Forum Discussion

jmpjmp's avatar
jmpjmp
Copper Contributor
Mar 07, 2023
Solved

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 depending on the slected criteria in adjaced field in Column B?

 

Many Thanks

James

excel
Macros and VBA
  • MindreVetande's avatar
    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

     

2 Replies

  • MindreVetande's avatar
    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

     

Resources