VBA - Loop Through Worksheets to Add Months of Year to Combo Boxes

Question

I have Private Sub combo boxes on 20 worksheets. I am trying to preload the months of the year in the combo boxes but I cant seem to make it work. What's wrong with my present code? It only adds the 12 months like, 120 times into the first combo box:

Sub WorkBook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
        ActiveSheet.ComboBox1.AddItem "January"
        ActiveSheet.ComboBox1.AddItem "February"
        ActiveSheet.ComboBox1.AddItem "March"
        ActiveSheet.ComboBox1.AddItem "April"
        ActiveSheet.ComboBox1.AddItem "May"
        ActiveSheet.ComboBox1.AddItem "June"
        ActiveSheet.ComboBox1.AddItem "July"
        ActiveSheet.ComboBox1.AddItem "August"
        ActiveSheet.ComboBox1.AddItem "September"
        ActiveSheet.ComboBox1.AddItem "October"
        ActiveSheet.ComboBox1.AddItem "November"
        ActiveSheet.ComboBox1.AddItem "December"
Next ws
End Sub

You don't have a ComboBox1 on each worksheet, at least not under that name. — Rik Sportel
They are all ComboBox1 I am copy/pasting the name ComboBox1 right from the properties dialogue — Sean Kelly
you can also do something like this for i=1 to 12:ws.combobox1.additem format(dateserial(2017,i,1),"mmmm"): next i — Nathan_Sav

Rik Sportel Rik Sportel · Accepted Answer · 2017-07-03T19:15:43

This happens because you're looping through your sheets using the ws variable, but for each worksheet you're adding the months to your currently ActiveSheet. You could do a ws.Activate before the first ActiveSheet.Combobox... line, however - that's just plain bad practice (see how to avoid Select / Activate

Bad example:

Sub WorkBook_Open()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
   ws.Activate
   ActiveSheet.ComboBox1.AddItem "January"
   ActiveSheet.ComboBox1.AddItem "February"
   '....
Next ws
End Sub

It's better to use the ws variable and then access the controls through the OLEObjects collection.

Good example:

Option Explicit
Private Sub Workbook_Open()
Dim ws As Worksheet
Dim i As Integer

For Each ws In ThisWorkbook.Worksheets
    With ws.OLEObjects("Combobox1").Object
        For i = 1 To 12
            .AddItem Format(DateSerial(2017, i, 1), "mmmm") 'as per comment!
        Next i
    End With
Next ws
End Sub

VBA - Loop Through Worksheets to Add Months of Year to Combo Boxes

1 Answers