Macro to Unhide sheets from a Dropdown List

**JO505** · 06-19-2013, 04:48 PM

I have a file with numerous sheets using a 4 digit year as the tab name. On the "Index" sheet I have a dropdown list of years, 1950 to present.
Rather than create a Macro for each sheet I want to Use a Macro that will open the sheet that is selected by the List.

The List is in Cell E5 on the sheet "Index" and will be controlled by a button "Oval 2".

Thanks

Jim O

**stnkynts** · 06-19-2013, 04:59 PM

Assumed Oval2 is the name of an activex button. Assumed you wanted to hide all other sheets but "Index" and the year selected in E5.

Please Login or Register  to view this content.

**tigeravatar** · 06-19-2013, 05:02 PM

Jim O,

Attached is an example workbook based on the criteria you described.
In cell E5 is a drop down list using data validation that contains 4-digit years
The sheets names '2010', '2011', '2012', and '2013' are hidden
When you select a 4-digit year and click the Go button (which is actually a shape named "Oval 2"), it executes this macro:

Please Login or Register  to view this content.

You can assign a macro to a shape by right-clicking the shape and selecting "Assign Macro"

**JO505** · 06-19-2013, 05:32 PM

Thank you both for the input.

tigeravatar, that works perfectly. I wasn't sure how to apply the range. I have done them using just the sheet name but I hit a wall when applying it to a single cell.

Much to learn.

Again thanks to both for your time.

Jim O