Using a combo box to link to other sheets

Baz_Man

New Member
Joined
Dec 5, 2008
Messages
11
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
 

Some videos you may like

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.

Tuelor

Board Regular
Joined
Mar 19, 2011
Messages
101
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

Code:
Sheets(ComboBox1.Value).Select
 

Jing

Active Member
Joined
Feb 11, 2011
Messages
288
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

Board Regular
Joined
Mar 19, 2011
Messages
101
Sure.

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

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.

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,108,970
Messages
5,525,957
Members
409,673
Latest member
Riseee

This Week's Hot Topics

Top