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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Navigating Worksheets Using Combobox Activex Control”

  1. I am a big fan of your tutorials. Many thanks for creating them. I have been able to follow this tutorial, “Navigate worksheets using combobox activex control”. I am left with one question though. Could you please tell me how to deal with the list when I have one or more hidden worksheets so they do not appear in the list or are ignored. I think this could involve a form of “if error” script but an very unsure. My sincere thanks in anticipation of your assistance with my problem

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.