Drop down list that changes selected sheet

**Mike_StGeorge** · 01-18-2010, 08:21 AM

Hello,

I'm trying to create a drop down list that changes the selected sheet. Basically, I want a main sheet that has a single drop down list with four selections, and when something in that list is selected, I want the workbook to switch to a specific sheet in the workbook. Can anyone here help?

Thanks

**DonkeyOte** · 01-18-2010, 08:30 AM

You will need to use VBA to change sheet though you can simply use Data Validation List to create the options...

might be an idea to outline how the options dictate which sheet is to be selected... ie are the options the sheet names themselves or something else entirely ?

**Mike_StGeorge** · 01-18-2010, 08:39 AM

The options are the sheet names themselves, does this make it easier?

**DonkeyOte** · 01-18-2010, 08:43 AM

Yes though it would not be too complex either way in truth - just needed to know for sake of demo.

If we assume A1 holds a Data Validation List containing the 4 sheet names ... right click on the Tab name -> View Code -> paste below into resulting window:

Please Login or Register  to view this content.

obviously alter A1 per your own requirements (ie to reflect the actual location of your validation cell)

thereafter in a macro enabled state altering selection in the validation list will navigate to the selected sheet.

**Mike_StGeorge** · 01-18-2010, 01:33 PM

I have tried entering that code into the window and changing the A1 to the cell I have my list in and when I try to select something from the list, I'm prompted with a VBA compile error that says

"Procedure declaration does not match description of event or procedure having same name"

I'm using excel 2003, does this have anything to do with my problem?

**DonkeyOte** · 01-18-2010, 01:36 PM

Originally Posted by Mike_StGeorge

I'm using excel 2003, does this have anything to do with my problem?

No, not at all, the problem is to do with me being :

a) a dunce

b) lazy and coding ad hoc

the ByRef should read ByVal... I've modified my earlier code.

**apatheticrory** · 06-22-2017, 11:56 AM

Hey! This has worked a treat for me, i was wondering whether there was a way for it to still work with hidden sheets?

Many thanks,

Rory

**FDibbins** · 06-22-2017, 01:39 PM

Rory, welcome to the forum

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.