CODEWITHSUNDEEP
vbaexcelexcel-2010
OwlPenn
OwlPenn

Reputation: 81

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

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

Upvotes: 2

Views: 1207

Answers (1)

David Zemens
David Zemens

Reputation: 53623

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.

Upvotes: 2

Related Questions