PDA

View Full Version : Solved: Populate listbox based on sheet selected from a ComboBox



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?

Bob Phillips
09-13-2012, 05:55 AM
Private Sub SheetsList_Change()
Dim NameArray As Variant

With Sheets(Me.SheetsList.Value)

NameArray = .Range(.Range("B6"), .Range("B6").End(xlDown))
End With

TeamList.List = NameArray
End Sub

SBrooky
09-13-2012, 05:56 AM
Perfect thanks alot =)