In Excel, how can I set up a VBA ComboBox so that it still works if the worksheet is copied?

Question

In Excel 2010, I can create an ActiveX ComboBox in a worksheet and configure it to give me a list of all worksheets, and it will activate whichever worksheet I select.

However, if I copy the worksheet containing the ComboBox, the new ComboBox is dead. I have to duplicate all the VBA code that makes it work, changing the labels accordingly.

Is there any way to set it up so that it works automatically if I copy the worksheet?

This is how I'm currently doing it:

Microsoft Excel Objects \ ThisWorkbook:

Private Sub Workbook_Open()

    ' Rebuild the list of sheets for the worksheet ComboBox.
    Dim i As Long
    For i = 1 To ThisWorkbook.Sheets.Count
        Sheet1.ComboBox1.AddItem Sheets(i).Name
        Next

End Sub

Microsoft Excel Objects \ Sheet1(Sheet1):

Private Sub ComboBox1_Change()

    With Sheet1.ComboBox1
        Sheets(.List(.ListIndex)).Activate
    End With

End Sub

since each ComboBox has a separate event, you will always need to assign the event code for each sheet you copy with the ComboBox in it. One thing you could do is write a separate Sub to Activate the Sheet and just pass the ComboBox ListItem to that sub for each ComboBox ... actually, not sure that is really even worth it! — Scott Holtzman
Using VBE Extensibility it would be possible but definitely overkill. What I would do is change the event procedure instead of Sheet1 make it say Me, and I think then the code should be portable between sheets. If the copied sheets do not preserve the ComboBox name, then just rename the ComboBox instead of revising each CB's event procedure. — David Zemens
I was just playing around with what @DavidZemens suggested as he was writing his comment. I will tell you what I did that worked. 1) Created a ComboBox 2) named it something specific (cbSheet in my case). 3) tested your code, but replaced Sheet1 with Me and 4) Copy the sheet I had the combobox on and it tested the code again, which worked perfectly without having to make any adjustments. — Scott Holtzman

David Zemens David Zemens · Accepted Answer · 2016-01-07T18:52:43

Do this in your Workbook Module:

Private Sub Workbook_Open()
    Call PopulateBoxes(Sheet1)
End Sub

In a standard module, do this:

Sub PopulateBoxes(ws As Worksheet)

    Dim sht As Worksheet

    'Populate the combobox on sheet 1
    Dim obj
    Set obj = ws.OLEObjects.Item("ComboBox1").Object
    obj.Clear
    For Each sht In ThisWorkbook.Worksheets
        obj.AddItem sht.Name
    Next

End Sub

Then, in your Sheet1 module, make this:

Private Sub ComboBox1_Change()

    With Me.ComboBox1
        Sheets(.List(.ListIndex)).Activate
    End With

End Sub

Private Sub WOrksheet_Activate()
    Call PopulateBoxes(Me)
End Sub

Now, the code for each ComboBox should be functional even after copying sheets.

In Excel, how can I set up a VBA ComboBox so that it still works if the worksheet is copied?

1 Answers