SBrooky
09-13-2012, 05:48 AM
I have a ComboBox (SheetsList) which lists all the Sheet names (and removes one and adds one):
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Me.TeamLeader.Caption = ActiveWorkbook.Worksheets("Main").Range("D3")
Me.SheetsList.Clear
For Each ws In ActiveWorkbook.Worksheets
Me.SheetsList.AddItem ws.Name
Next ws
Me.SheetsList.RemoveItem (Main)
Me.SheetsList.AddItem ("All")
End Sub
When they select a worksheet I want it to populate the Listbox (TeamList) with the data from the selected sheet B5 onwards.
I tried:
Private Sub SheetsList_Change()
Dim NameArray() As String
NameArray = Sheets(Me.SheetsList.Value).Range("B6:B10")
TeamList.List = NameArray
End Sub
I failed. Any help?
Private Sub UserForm_Initialize()
Dim ws As Worksheet
Me.TeamLeader.Caption = ActiveWorkbook.Worksheets("Main").Range("D3")
Me.SheetsList.Clear
For Each ws In ActiveWorkbook.Worksheets
Me.SheetsList.AddItem ws.Name
Next ws
Me.SheetsList.RemoveItem (Main)
Me.SheetsList.AddItem ("All")
End Sub
When they select a worksheet I want it to populate the Listbox (TeamList) with the data from the selected sheet B5 onwards.
I tried:
Private Sub SheetsList_Change()
Dim NameArray() As String
NameArray = Sheets(Me.SheetsList.Value).Range("B6:B10")
TeamList.List = NameArray
End Sub
I failed. Any help?