Populating a dependent list box

[charly1]

Hi All

I am trying to write some vba code for my user form, yet I keep on getting stuck. Any help would be very useful and greatly appreciated.

In my worksheet [worksheet1] I have a table [table1] with three columns. col1 = unique reference number. col2 = book name. col3 = book author.

In a user form I have two list boxes. list box 1 already contains two columns: LB1 col1 is a range of reference numbers retrieved from my table col1, and in LB1 col2, I have the matching book names.

I need a code that would loop through all rows in LB1 and populate LB2 with two separate columns. LB2 col1 should contain all the the reference numbers contained in LB1 col1 - in the same order [resulting in both list boxes containing an identical col1]. The second column of LB2, should then be populated with all of the matching book artist, as retrieved from the Table in column 3.

I hope I've explained myself clearly enough.

Thank you so much in advance for any help offered.

 

[charly1]

the code below works well enough when the data source for the two list boxes are both coming from a single excel table.

Private Sub CommandButton2_Click()
Dim searchrange As Range
Set searchrange = Worksheets("מפתח").ListObjects("Table1").ListColumns(1).DataBodyRange
Dim tableData As Range
Set tableData = Worksheets("מפתח").ListObjects("Table1").ListColumns(14).DataBodyRange
Dim Rr As Long
Dim Bb As Long
Bb = (ListBox1.ListCount - 1)
For Rr = 0 To Bb
With ListBox7
.ColumnCount = 2
.AddItem
.List(Rr, 0) = ListBox1.List(Rr, 0)
Dim c As String
c = WorksheetFunction.XLookup(ListBox1.List(Rr, 0), searchrange, tableData)
.List(Rr, 1) = c
End With
Next





End Sub