excel drop down grabbing info from another sheet

Question

I'm trying to create a drop down list that has the names of the other sheets in my workbook. When I select one of those names from the drop down I would like it to grab all the people's names from that sheet and display onto the sheet with the drop down. But I can't figure it out.

Answer 1

Using this code:

Dim SheetSel As Boolean

Sub InitCMB()
    ComboBox1.Clear
    For Each xx In Sheets
        ComboBox1.AddItem xx.Name
    Next
    SheetSel = True
End Sub

Private Sub ComboBox1_Change()
    If SheetSel = True Then
        e = ComboBox1.ListIndex + 1
        SheetSel = False
        ComboBox1.Clear
        ComboBox1.AddItem ".."
        For i = 1 To 9999
            If Sheets(e).Range("A" & i).Value = "" Then Exit For
            ComboBox1.AddItem Sheets(e).Range("A" & i).Value
        Next
    Else
        If ComboBox1.Value = ".." Then InitCMB
    End If

End Sub

you insert in a combo the names of the sheets (InitCMB).
If you select one sheet, the macro load the names from A1 to the end of the sheet selected.
In the list add at the top ".." like dir. When you select ".." you return to the sheets list.