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, Jul 03 '17 at 18:44
They are all ComboBox1 I am copy/pasting the name ComboBox1 right from the properties dialogue — Sean Kelly, Jul 03 '17 at 18:47
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, Jul 03 '17 at 18:48

score 3 · Accepted Answer · answered Jul 03 '17 at 19:15

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

You're welcome. B.t.w. keep in mind that this will error for Worksheets that do not have the "ComboBox1" object, so you might want to add some checks and/or error handling. — Rik Sportel, Jul 03 '17 at 19:23

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

1 Answers1