how to populate a combobox with tab names

[Pookiemeister]

In my workbook I have tabs named Hats, Shirts, Pants and Shoes. Within each tabs, there is a list of the various types of clothing. For example, if hat is selected there is a list of various types of hats located in the sheet with the tab labeled hats that will populate another combobox. I found some code that displays all the tabs in a messagebox.

[COLOR=#000000][FONT=Consolas]Sub WorksheetLoop()[/FONT][/COLOR]<code class="" style="box-sizing: inherit; font-family: Consolas, "Courier New", Courier, monospace; font-size: 1em;">
         Dim WS_Count As Integer
         Dim I As Integer

         ' Set WS_Count equal to the number of worksheets in the active
         ' workbook.
         WS_Count = ActiveWorkbook.Worksheets.Count

         ' Begin the loop.
         For I = 1 To WS_Count

            ' Insert your code here.
            ' The following line shows how to reference a sheet within
            ' the loop by displaying the worksheet name in a dialog box.
            MsgBox ActiveWorkbook.Worksheets(I).Name

         Next I

</code>[COLOR=#000000][FONT=Consolas]      End Sub[/FONT][/COLOR]

But I would like to display them in a combo box instead of a message box. So I tried this instead.

Private Sub cmbClothingLine_Enter()    Dim WS_Count As Integer
    Dim I As Integer
        
    cmbClothingLine.Clear
        
    WS_Count = ActiveWorkbook.Worksheets.Count
    
    
        For I = 5 To WS_Count
            With cmbClothingLine
                ActiveWorkbook.Worksheets(I).Name
                .AddItem
            End With
        Next I
   
End Sub

I then get a "Run-time error: '438': Object doesn't support this property or method" How can I fix this. Thank You.

 

[My Aswer Is This]

Put this script in a Command button:

Private Sub CommandButton1_Click()
'Modified 6/25/18 12:10 AM EDT
Dim i As Long
cmbClothingLine.Clear
    For i = 1 To Sheets.Count
        cmbClothingLine.AddItem Sheets(i).Name
    Next
End Sub

 

[My Aswer Is This]

If you wanted to see them in a Message Box. I would do it this way:

Private Sub CommandButton2_Click()
'Modified 6/25/18 12:25 AM EDT
Dim i As Long
Dim ans As String
Dim anss As String
    For i = Sheets.Count To 1 Step -1
        ans = Sheets(i).Name & vbNewLine & ans
    Next
anss = "Your sheet names are:" & vbNewLine
MsgBox anss & ans
End Sub

 

[Pookiemeister]

Thank You very much for your solution. It worked as I had liked.

 

[My Aswer Is This]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Thank You very much for your solution. It worked as I had liked.