Navigating Worksheets Using Combobox Activex Control

How to Navigate worksheets in a workbook usingc Combobox Activex control with VBA. The first step is to get all the worksheet names into the combobox activex control using a looping process when the worksheet containing the combo-box is activated. Before we get all the worksheet names into the activex control, we first clear it to avoid duplicates. Next we select a worksheet name from the combo-box to activate the the relevant worksheet. This should happen on the ‘change’ event’. Since the combo-box also contains a blank value, we can make things easier by assigning the blank value a ‘default’ value like ‘choose worksheet’. This makes the interface more user-friendly.

Now what happens if we activate the combo-box containing worksheet, do nothing and close the workbook. When we open the workbook again and click on the drop-down arrow of the combo-box, there is a little surprise: the comb-box has not been populated with the worksheet names? why? Because our macro will get the worksheet names only on the combo-box containing worksheet activation! So we need to select another worksheet and then select the activex control containing worksheet so that this worksheet is activated when the workbook opens. Therefore, the code in the worbook_open procedure takes care of this situation. Watch the video below:

Here’s the complete VBA code for the process of using a combo-box activex control to navigate through worksheets of a workbook:

Private Sub cboStart_Change()
If cboStart <> “Choose Sheet” Then
Worksheets(cboStart.Value).Select
End If

cboStart.Value = “Choose Sheet”
End Sub

Private Sub Worksheet_Activate()
Dim sht As Worksheet
Me.cboStart.Clear
For Each sht In ThisWorkbook.Worksheets
Me.cboStart.AddItem sht.Name

Next sht

End Sub

Private Sub Workbook_Open()

If ActiveSheet.Name = “Start” Then
Worksheets(“Expenses”).Select
Worksheets(“Start”).Select
End If
End Sub

Watch this video on YouTube.

Further Reading: Add a list box or combo box to a worksheet in Excel