VBA - Dynamic Listbox from Variable ComboBox

[MrMisster]

Hi,

I am trying to add a form, that allows the user to first create a team, then add members to that team. Once the team has been inserted it populates a ComboBox, I would like to be able to populate a list box with the Team Members based on the team selected in the ComboBox. I have tried a good amount of ways, getting very close, but cannot seem to set the Table Column variable correctly. Any help is appreciated.

Private Sub ComboBox1_Change()

Dim tbl As ListObject
Dim cell As Range
Set sh = ThisWorkbook.Sheets("Validation")
Dim n As Integer
Dim i As Integer

n = Application.WorksheetFunction.Match(Me.ComboBox1.Value, sh.Range("1:1"), 0)

'Store Table Object to a variable
Set tbl = Sheets("Validation").ListObjects("Teams")

'Clear Listbox each time a change is initialised
ListBox1.Clear

'Load Combobox with column 1 data teams
Me.ComboBox1.List = Sheets("Validation").ListObjects("Teams").ListColumns(1).DataBodyRange.Value

'Load List Box
  For Each cell In tbl.DataBodyRange.Columns(n).Cells
    ListBox1.AddItem cell.Value
  Next cell

End Sub

Screenshots of form and table;

 

[Fluff]

How about

Private Sub ComboBox1_Click()
   Dim tbl As ListObject

   Set tbl = ThisWorkbook.Sheets("Validation").ListObjects("Teams")
   Me.ListBox1.Clear
   Me.ListBox1.List = tbl.ListColumns(Me.ComboBox1.Value).DataBodyRange.Value
End Sub

 

[MrMisster]

How about

Private Sub ComboBox1_Click()
   Dim tbl As ListObject

   Set tbl = ThisWorkbook.Sheets("Validation").ListObjects("Teams")
   Me.ListBox1.Clear
   Me.ListBox1.List = tbl.ListColumns(Me.ComboBox1.Value).DataBodyRange.Value
End Sub

Wow, I want to punch myself in the face. I went with something very similar early on and could not get it to work so I have been sat for 2 days going around the houses with different methods. The .ListColumns() was where I fell down massively and I actually tried combox1.value without the reference to the form and it failed, obviously.

Thanks Fluff, appreciated!

 

[Fluff]

You're welcome & thanks for the feedback.

As you were using the Change event, if you manually type in a value (say Manowar), as soon as you type the "M" the code will be looking for a column called "M" & will therefore fail.

 

[MrMisster]

Not a problem,

Did not think of that, makes much more sense to use the click event instead.

Thanks again!

 

[Fluff]

My pleasure