How to Hide and Unhide Sheets Based on Cell Value

[nhinx]

Dear All Excel Experts,

I would like to seek help from you to provide me a VBA that will hide and unhide the specific sheets i want. For example I have a sheets named "Data","Cat1", "Cat2", "Cat3","Dog1", "Dog2" and "Dog3" then whenever i input the word "Dog" in Cell "D4" it will hide (VeryHidden) the sheets named "Cat1", "Cat2", "Cat3". Then when I input the word "Cat" in Cell "D4" it will now unhide the sheets "Cat1", "Cat2", "Cat3" and then it will hide (VeryHidden) the sheets named "Dog1", "Dog2" and "Dog3". So Whenever I changed the name in Cell "D4" in sheet "Data" it will hide and unhide it vice versa.

Hope you could help me with this one.

Thanks

 

[Snakehips]

Try this.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Ws As Worksheet
    If Target.Count > 1 Then Exit Sub
    If Intersect(Target, Range("D4")) Is Nothing Then Exit Sub

For Each Ws In ThisWorkbook.Worksheets
    Select Case Target
        Case "Dog"
                If Ws.Name Like "Dog" & "*" Then Ws.Visible = xlSheetVisible
                If Ws.Name Like "Cat" & "*" Then Ws.Visible = xlSheetVeryHidden
         Case "Cat"
                If Ws.Name Like "Cat" & "*" Then Ws.Visible = xlSheetVisible
                If Ws.Name Like "Dog" & "*" Then Ws.Visible = xlSheetVeryHidden
           Case Else
     End Select
Next Ws

End Sub

 

[nhinx]

Same for both Cat & Dog??? I'm assuming that is a typo?

Hi Snakehips,

Yeah it's a typo. It Should be this way

Select Case Worksheets("Data").Range("D4").Value

Case "Dog"
Worksheets("Dog 1").Visible = xlSheetVisible
Worksheets("Dog 2").Visible = xlSheetVisible
Worksheets("Dog 3").Visible = xlSheetVisible
Worksheets("Cat 1").Visible = xlSheetVeryHidden
Worksheets("Cat 2").Visible = xlSheetVeryHidden
Worksheets("Cat 3").Visible = xlSheetVeryHidden

Case "Cat"
Worksheets("Cat 1").Visible = xlSheetVisible
Worksheets("Cat 2").Visible = xlSheetVisible
Worksheets("Cat 3").Visible = xlSheetVisible
Worksheets("Dog 1").Visible = xlSheetVeryHidden
Worksheets("Dog 2").Visible = xlSheetVeryHidden
Worksheets("Dog 3").Visible = xlSheetVeryHidden


End Select
End Sub