Hide/Unhide Sheets Based On Cell Value

Re: Unhide Sheet Based On Cell Value Contents

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

Re: Unhide Sheet Based On Cell Value Contents

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

Re: Hide/Unhide Sheets Based On Cell Value

How would you convert the 'case' code if A1 had a formula (vlookup) to return "A", "B" or "C"?

Re: Unhide Sheet Based On Cell Value Contents

Hi! This is awesome, almost exactly what I'm was trying to do but I'm trying to be cable to make this work with a column of vaults like having column A in sheet D determine if the sheets are visible then have it go like if A1=yes then Sheet A is visible, If A2=yes then Sheet B is visible, so on and so forth.Or maybe even have the yes no part as a drop down menu. I'm still trying to learn this programming stuff so if you could help me and explain how the code works that would be much appreciated.

Re: Unhide Sheet Based On Cell Value Contents

Bryce,

Your hide code works perfectly for what I needed, I made a minor change so it is a YES/NO question basically and it worked well.

I want to add a second set of logic applying to anther cell how do I do put this into the VBA. I tried adding a second copy of the code with a different Range Cell but it didn't work after I did that.

Sorry for the rookie question I have zero VBA experience. likely I made a foolish error.

Cheers
Kent

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

End Select
End Sub

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

End Select
End Sub