VBA Hide Worksheet Using Cell Values

Column A matches the worksheet names - Doc1, Doc2, Doc3... Column B represents worksheet visibility with validation criteria - Show, Hide Is there a way to show/hide worksheets named in Column A...

excel

Macros and VBA

MindreVetande

Mar 07, 2023

jmpjmp

RightClick on your "mainSheet" and select something like "show code". then you can catch any change in B1:B3 and hide/show the matching sheet (in A1:A3).

Try this small example and see if it does what you want and then adapt to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B1:B3"), Target) Is Nothing Then Exit Sub

Dim ChangedSheet As Worksheet

Set ChangedSheet = Sheets(Target.Offset(0, -1).Value)

If LCase(Target.Value) = "show" Then
    ChangedSheet.Visible = True
ElseIf LCase(Target.Value) = "hide" Then
    ChangedSheet.Visible = False
End If

End Sub

MindreVetande

Iron Contributor

Mar 07, 2023

jmpjmp

RightClick on your "mainSheet" and select something like "show code". then you can catch any change in B1:B3 and hide/show the matching sheet (in A1:A3).

Try this small example and see if it does what you want and then adapt to your needs.

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Range("B1:B3"), Target) Is Nothing Then Exit Sub

Dim ChangedSheet As Worksheet

Set ChangedSheet = Sheets(Target.Offset(0, -1).Value)

If LCase(Target.Value) = "show" Then
    ChangedSheet.Visible = True
ElseIf LCase(Target.Value) = "hide" Then
    ChangedSheet.Visible = False
End If

End Sub

jmpjmp

Copper Contributor

Mar 14, 2023

This worked very well! Thanks MindreVetande

Forum Discussion

VBA Hide Worksheet Using Cell Values

Resources