Using a combo box to link to other sheets

[Baz_Man]

Hello all,

After an hour of searching and trying I'm having trouble finding a solution to this one.

My workbook has 20 different worksheets. On a summary page I want a drop down box which can be used as a link through to the correct sheet. So, you click the drop down box, select the option (sheet) you want and are taken to cell A1 in that sheet.

I'd have thought this was fairly simple but I can't figure it out.

Any suggestions would be much appreciated.

Cheers

 

[Tuelor]

You can use the Active ComboBox control, and run a macro on the combobox_change event to pick up the selected value and take you to the worksheets.

D

Sheets(ComboBox1.Value).Select

 

[Jing]

Would i be able to get a more broken down explanation on how to use this??? seems very useful instead of using hyperlinks that break if anything on the sheets changes...

 

[Tuelor]

Sure.

1) Add ActiveX ComboBox on to target worksheet. In design mode, double click on this control and add the following code.

Private Sub ComboBox1_Change()
    Sheets(ComboBox1.Value).Select
End Sub

2) To populate the control, add the following macro on to the Workbook_Open event.

Private Sub Workbook_Open()
    Dim iCount As Integer
    
    Sheet1.ComboBox1.Clear
    
    For iCount = 1 To Sheets.Count
        Sheet1.ComboBox1.AddItem Sheets(iCount).Name
    Next iCount
End Sub