How to make a drop-down list for worksheets

Question

I have a total of five sheets in a workbook. My task is to create a combo list button in the first sheet that will be able to point to the other four. If a user chooses one of the sheet names then the button will automatically activate the chosen sheet. It is unlikely that sheets will be deleted, though likely that sheets will be added.

I'm not even sure how to get the sheet names to show up on the combo list.

Answer 1

In order to make the combobox change the active sheet, I believe you'll need to use VBA (as I don't know how to do it using validation lists).

To do it, you'll have to:

1st - Add a combobox into your first sheet and properly name it (I called it cmbSheet). I suggest to use an ActiveX Combobox (in Excel 2007, under Developer tab).

2nd - Open VBA and add the below code into your workbook code. This code will populate the combobox with the sheet names every time the workbook is opened.

Private Sub Workbook_Open()

    Dim oSheet As Excel.Worksheet
    Dim oCmbBox As MSForms.ComboBox

    Set oCmbBox = ActiveWorkbook.Sheets(1).cmbSheet

    oCmbBox.Clear

    For Each oSheet In ActiveWorkbook.Sheets

        oCmbBox.AddItem oSheet.Name

    Next oSheet

End Sub

3rd - Now, go to the code of your first sheet (where the combobox has been added) and add the code that will activate the sheet chosen in the combobox. The code is

Private Sub cmbSheet_Change()

    ActiveWorkbook.Sheets(cmbSheet.Value).Activate

End Sub

Now, when the combobox value changes, the respective sheet is activated.

Let us know if something ins't clear and we'll help you out.