Excel VBA populates Listbox1 based on selection Listbox2
I am trying to figure out the correct code to populate a list based on a selection from a second list. I will explain my question in the best possible way. I have one sheet with two columns filled in this way.
(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
I want to populate ListBox1 with the "PARTS NUMBER" column and when I click "PART A" I only get the list of items for the location from (column B) in ListBox2. Hope this makes sense and someone can help me. Thank you in advance.
To populate the ListBox:
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 check padding and comma separation:
UserForm1.LstPartNum.List = Split("LOC1,LOC7,LOC12,LOC21", ",")
+3
source to share
1 answer
In a change event with a list of room numbers, 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
If your UserForm1.LstPartNum.List = Split () doesn't work for loading the list, here is the code for circularly splitting the 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
+2
source to share