Search code examples
excelvbacomboboxworksheet

Switching sheets via a dropdown list

I'm very new to VBA, and am trying to create a way to navigate sheets in a workbook via a dropdown list on a main 'index' page. As there are multiple sheets in the workbook, and multiple people using, I would like the dropdown list to autofill, to save from needing to search through the list. I would then like the selected sheet to open (possibly with a macro button, or even by hitting enter if that is an option).

So far, I have the following macro that populates the dropdown list (taken from another site):

Private Sub ComboBox1_DropButtonClick()
Dim xSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
    ComboBox1.Clear
    For Each xSheet In ThisWorkbook.Sheets
        ComboBox1.AddItem xSheet.Name
    Next xSheet
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
    End Sub

This is perfect for the content of my dropdown list, and for its autofill feature; but I'm struggling to include that final action of actually going to the selected sheet. For info, I've used a ComboBox (ActivX Control). I was thinking it could be a case of creating a macro button and referencing the text in the dropdown selection?

Any help or suggestions would be much appreciated.

Solution

  • You can use the value in the combobox to switch tabs like this. Once I hit the button, then Sheet1 becomes active instead of Sheet2

    enter image description here

    Here's the code for you to copy/paste & test for yourself:

    Private Sub CommandButton1_Click()
Dim mySheet As Worksheet
For Each mySheet In ActiveWorkbook.Worksheets
    If mySheet.Name = ComboBox1 Then
        mySheet.Select
        Exit For
    End If
Next mySheet
End Sub

    Essentially, this will cycle through all your worksheets to find whatever is in the combobox. If there's a match, then it is selected.