Creating a Drop-down list for different tabs

[whinks]

Hello all,

I am attempting to figure out a problem and I seem to be stuck. What I am trying to do is create a drop-down list for different tabs in my worksheet. When a tab name is selected I want it to automatically direct me to that worksheet. When I am directed to a worksheet I want another drop-down list to either direct me back to the first sheet or a different sheet from there (I want the list of tab names to be different from sheet to sheet). What I have so far is below:

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

Private Sub cmbSheet_Change()

    ActiveWorkbook.Sheets(cmbSheet.Value).Activate

End Sub

This works great for my first sheet and it directs me to every sheet I want to go, but when I try to duplicate it for other sheets, it will not work. Any suggestions? I am not great at writing code so anyone who could write the code needed for this problem would be most appreciated! Thanks!

 

[hiker95]

whinks,

Welcome to the MrExcel forum.

1. What version of Excel, and, Windows are you using?

2. Are you using a PC or a Mac?


See: Tom’s Tutorials for Excel: Sheet Selector Drop-Down List.

Tom’s Tutorials for Excel: Sheet Selector Drop-Down List | Atlas

There is a sample, downloadable file to play with.

And, the macro code(s) are available.

 

[whinks]

I am using version 2010 and I am on a PC. Thanks for the link!

 

[hiker95]

Thanks for the link!

whinks,

You are very welcome.

Have you tried to use the information?

 

[whinks]

Got what I need, thanks!

 

[hiker95]

whinks,

Thanks for the feedback.

You are very welcome. Glad I could help.

And, come back anytime.