Excel VBA根据Listbox2选择填充Listbox1 [英] Excel VBA Populate Listbox1 based on Listbox2 Selection

问题描述

我试图根据第二个列表框中的选择来找出用于填充列表框的正确代码.我将尽我所能解释我的问题.我有一个工作表，其中有两列是这样填充的.

I am trying to figure out the proper code for populating a listbox based on a selection from a second listbox. I will explain my question the best I can. I have one worksheet with two columns populated like this.

(COLUMN A)    (COLUMN B)
PART NUMBER:  LOCATION:
PART A        LOC1,LOC7,LOC12,LOC21
PART B        LOC2,LOC8,LOC13,LOC22
PART C        LOC6,LOC9,LOC18,LOC20

我希望能够用"PART NUMBER"列填充ListBox1，当我单击"PART A"时，我仅从ListBox2的(列B)中获得位置列表.我希望这是有道理的，有人可以帮助我.预先谢谢你.

I want to be able to populate ListBox1 with the "PART NUMBER" column and when I click on "PART A" I get just a list of items for location from (Column B) in ListBox2. I hope this makes sense and someone can help me out. Thank you in advance.

要填充我的列表框:

Dim rngName As Range
Dim ws As Worksheet
Dim i As Integer

Set ws = Worksheets("Sheet2")
For i = 1 To ws.Cells(ws.Rows.Count, 1).End(xlUp).Row Step 1
If ws.Cells(i, 1).Value <> vbNullString Then Me.LstPartNum.AddItem    ws.Cells(i, 1).Value
Next i

要用逗号填充和拆分，请执行以下操作:

To Test populate and split by commas:

UserForm1.LstPartNum.List = Split("LOC1,LOC7,LOC12,LOC21", ",")

推荐答案

在部件号组合框的change事件中，应执行以下操作.

In the change event of the part number combobox do something like this.

Dim ws As Excel.Worksheet
Dim lRow As Long

Set ws = Worksheets("Sheet2")
lRow = 1

'Loop through the rows
Do While lRow <= ws.UsedRange.Rows.count
    'Check if Column A has the value of the selected part number.
    If ws.Range("A" & lRow).Value = LstPartNum.Text Then
        UserForm1.LstLocation.Clear
        'Load the locations
        UserForm1.LstLocation.List = Split(ws.Range("B" & lRow).Value, ",")
        Exit Do
    End If
lRow = lRow + 1
Loop

如果您的UserForm1.LstPartNum.List = Split()无法加载列表，则此处是循环拆分数组的代码.

If your UserForm1.LstPartNum.List = Split() does not work to load the list, here is code to loop the split array.

Dim szLocs() As String
Dim i as integer

szLocs= Split(ws.Range("B" & lRow).Value, ",")

i = 0
'Loop though each token
Do While i <= UBound(szPieces)
    UserForm1.LstPartNum.Additem szPieces(i)
i = i + 1
Loop

这篇关于Excel VBA根据Listbox2选择填充Listbox1的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！