Macro to Unhide sheets from a Dropdown List

[JO505]

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]

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.

Private Sub Oval2_Click()
Dim ws As Worksheet:    Set ws = Sheets("Index")
Dim wksht As Worksheet

Application.ScreenUpdating = False
For Each wksht In Worksheets
    If wksht.Name = ws.Name Or wksht.Name = ws.Range("E5").Value Then
        wksht.Visible = xlSheetVisible
    Else
        wksht.Visible = xlSheetHidden
    End If
Next wksht
Application.ScreenUpdating = True

End Sub

[tigeravatar]

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:

Sub Oval2_Click()
    
    On Error Resume Next
    With Sheets(Sheets("Index").Range("E5").Text)
        .Visible = xlSheetVisible
        .Select
    End With
    
End Sub

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

[JO505]

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