Hide/Unhide Sheets Based On Cell Value

[gmccreedy]

I have a series of sheets hidden in a workbook. (example sheets named a, b and c).

I want to be able to unhide a sheet based on a Cell "A1" entry on sheet D

So if A is entered, sheet A would unhide, and B and C would remain hidden.

[bryce]

You could use this, it will unhide the shet specified In A1 and hide the others. Place the code in worksheet D.

Private Sub worksheet_change(ByVal target As Excel.Range) 
    Select Case Worksheets("D").Range("A1").Value 
    Case "A" 
        Worksheets("A").Visible = True 
        Worksheets("B").Visible = False 
        Worksheets("C").Visible = False 
    Case "B" 
        Worksheets("B").Visible = True 
        Worksheets("A").Visible = False 
        Worksheets("C").Visible = False 
    Case "C" 
        Worksheets("C").Visible = True 
        Worksheets("A").Visible = False 
        Worksheets("B").Visible = False 
    End Select 
End Sub 

[gmccreedy]

ok...my example was a little generic, I apologize. Is there a way to make this a loop as opposed to a series of "Case" statements?

I actually have about 30 sheets.

[bryce]

Try this one then.

Private Sub worksheet_change(ByVal target As Excel.Range) 
    Dim ws As Worksheet 
     
    For Each ws In Worksheets 
        If ws.Name <> "D" And ws.Name <> Worksheets("D").Range("A1").Value Then 
            ws.Visible = False 
        End If 
        If ws.Name = Worksheets("D").Range("A1").Value Then 
            ws.Visible = True 
        End If 
    Next ws 
     
End Sub 

[gmccreedy]

Thank you so much!!! I was so close...had small typo in the If statement...thanks again!!!