I have vba code which loads the names of the sheets in a workbook into a Combobox.
This ComboBox helps to populate a ListBox based on the ComboBox selection.
Right now, I can use Select / Case or a bunch of If Then statements to set the Rowsource for each ListBox based on which sheet is selected in the ComboBox, like this:
[VBA]With Sheet1
ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)
End With[/VBA]
What I'll like to know is how do I set the ListBox1.RowSource automatically if another sheet is added by the user, in the same way that I have the ComboBox automatically populated in the Private Sub UserForm_Initialize module, like this:
For Each ws In ThisWorkbook.Worksheets
ComboBox1.AddItem ws.Name
Next ws
The Rowsource for each sheet in the listbox is the same range on each sheet.
ListBox1.RowSource = Range(.Range("A4"), .Cells(Rows.Count, "A").End(xlUp)).Address(, , , True)
Any code or suggestions welcome
Thanks