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.
Screenshots of form and table;
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.
VBA Code:
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;