Workbook Open Code Not Working

[Grant22]

Hello all, I am getting an error when I open a workbook. In a nutshell, for each sheet not named one of the sheets in the code I want to activate that sheet and add the months of the year in the combo box. Each sheet has a combobox1 on it, so I'm not sure why this isn't working. Any help is greatly appreciated. See below for the error message as well as the code:

Method or Data Member Not Found

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
If ws.Name <> "Home" And ws.Name <> "Emp's" And ws.Name <> "Metrics" _
        And ws.Name <> "Branch Performance" And ws.Name <> "EMP Performance" Then
        ws.Activate
    With ws.ComboBox1
        .Clear
        .AddItem ""
        .AddItem "January"
        .AddItem "February"
        .AddItem "March"
        .AddItem "April"
        .AddItem "May"
        .AddItem "June"
        .AddItem "July"
        .AddItem "August"
        .AddItem "September"
        .AddItem "October"
        .AddItem "November"
        .AddItem "December"
    End With
    ws.ComboBox1.Value = ""
    Next ws
End If
End Sub

 

[Joe4]

You have your "Next ws" and "End If" statements reversed. Basically, you close them in the opposite order they were opened (so the last one opened is the first one closed), i.e.

For Each ws In Worksheets
    If ws.Name <> "Home" And ws.Name <> "Emp's" And ws.Name <> "Metrics" _
        And ws.Name <> "Branch Performance" And ws.Name <> "EMP Performance" Then
        ws.Activate
        With ws.ComboBox1
            .Clear
            .AddItem ""
            .AddItem "January"
            .AddItem "February"
            .AddItem "March"
            .AddItem "April"
            .AddItem "May"
            .AddItem "June"
            .AddItem "July"
            .AddItem "August"
            .AddItem "September"
            .AddItem "October"
            .AddItem "November"
            .AddItem "December"
        End With
        ws.ComboBox1.Value = ""
[COLOR=#ff0000]    End If[/COLOR]
[COLOR=#ff0000]Next ws[/COLOR]

If you indent them properly, it should be more evident ("End If" should line up with "If", "Next" should line up with "For").

 

[hotabae]

I dunno if it will completely fix your problem, but your "Next ws" line is inside of your "IF" code.

I'd flip it to this:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Home" And ws.Name <> "Emp's" And ws.Name <> "Metrics" _
            And ws.Name <> "Branch Performance" And ws.Name <> "EMP Performance" Then
            ws.Activate
        With ws.ComboBox1
            .Clear
            .AddItem ""
            .AddItem "January"
            .AddItem "February"
            .AddItem "March"
            .AddItem "April"
            .AddItem "May"
            .AddItem "June"
            .AddItem "July"
            .AddItem "August"
            .AddItem "September"
            .AddItem "October"
            .AddItem "November"
            .AddItem "December"
        End With
        ws.ComboBox1.Value = ""
[COLOR=#ff0000]    End If
Next ws[/COLOR]
End Sub

 

[Grant22]

Thanks for catching that guys, I didn't even notice that issue yet. But I'm still getting the compile error.

 

[Joe4]

What kind of combo box do you have?
See: https://stackoverflow.com/questions/44083621/vba-how-to-reference-combobox-object

 

[Grant22]

Added an ActiveX combobox from the developer tab.

 

[Joe4]

I am not sure why, but it apparently does not like to reference the sheet that way with the ComboBox object.
But this seems to not have any compile errors:

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
    If ws.Name <> "Home" And ws.Name <> "Emp's" And ws.Name <> "Metrics" _
            And ws.Name <> "Branch Performance" And ws.Name <> "EMP Performance" Then
            ws.Activate
        With Sheets(ws.Name).ComboBox1
            .Clear
            .AddItem ""
            .AddItem "January"
            .AddItem "February"
            .AddItem "March"
            .AddItem "April"
            .AddItem "May"
            .AddItem "June"
            .AddItem "July"
            .AddItem "August"
            .AddItem "September"
            .AddItem "October"
            .AddItem "November"
            .AddItem "December"
        End With
        Sheets(ws.Name).ComboBox1.Value = ""
    End If
Next ws
End Sub

 

[Fluff]

Another way

Private Sub Workbook_Open()
Dim ws As Worksheet
For Each ws In Worksheets
   If ws.name <> "Home" And ws.name <> "Emp's" And ws.name <> "Metrics" _
         And ws.name <> "Branch Performance" And ws.name <> "EMP Performance" Then
      ws.Activate
      With ws.OLEObjects("ComboBox1").Object
         .Clear
         .AddItem ""
         .AddItem "January"
         .AddItem "February"
         .AddItem "March"
         .AddItem "April"
         .AddItem "May"
         .AddItem "June"
         .AddItem "July"
         .AddItem "August"
         .AddItem "September"
         .AddItem "October"
         .AddItem "November"
         .AddItem "December"
      End With
     ws.OLEObjects("ComboBox1").Object.Value = ""
   End If
Next ws
End Sub

 

[Grant22]

I love this forum, worked like a charm. Thanks Joe4!

 

[Joe4]

You are welcome!