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
 

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,015
Messages
5,834,938
Members
430,327
Latest member
Mr_Himalayan778

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top