VBA Code for Hiding multiple sheets given a cell value

[sophijo97]

Hi,
I'm trying to make a spreasheet where if there is a "0" on the first sheet, it will hide a corresponding sheet. For example, if B2 has a "0", then Sheet2 will be hidden and same thing until B18 and Sheet18. I have this code right now that works for one sheet:

Private Sub Worksheet_Change(ByVal Target As Range)
If [B2] = "0" Then
Sheets ("Sheet2").Visible = False
Else
Sheets("Sheet2").Visible = True
End If
End Sub

Is there a way to modify this to apply to multiple sheets and cells rather than just one of each?

Thanks

 

[nemmi69]

try

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NumSheets As Integer


For NumSheets = 1 To 10
    If ActiveSheet.Range("B" & NumSheets).Value = "0" Then
    Sheets("Sheet" & NumSheets).Visible = False
    Else
    Sheets("Sheet" & NumSheets).Visible = True
    End If
Next NumSheets




End Sub

 

[sophijo97]

I get a Microsoft Visual Basic "Run-time error '9' Subscript out of range"

 

[nemmi69]

How many sheets do you actually have?

 

[sophijo97]

Ah I see so this works perfectly if the sheets are titled Sheet1, Sheet 2, etc. Is there a way to modify this code so the sheets can be named based on the cell next to the reference cell
Example
A B
1 Dogs
2

 

[sophijo97]

sorry
A B
1 Dogs 1 So the tab would not be hidden and would be named Dogs?

 

[nemmi69]

like this?

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim NumSheets As Integer
Dim CountSheets As Integer
Dim SheetID As String

CountSheets = Worksheets.Count
'Name in "B" followed by number
For NumSheets = 1 To CountSheets
    SheetID = ActiveSheet.Range("B" & NumSheets).Value
    If ActiveSheet.Range("A" & NumSheets).Value = "0" Then
       Sheets(SheetID & NumSheets).Visible = False
    Else
       Sheets(SheetID & NumSheets).Visible = True
    End If
Next NumSheets

End Sub

 

[Fluff]

Another option

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.CountLarge > 1 Then Exit Sub
   If Target.Column = 1 Then
      Sheets(Target.Offset(, 1).Value).Visible = Target.Value = 0
   End If
End Sub

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...iding-multiple-sheets-given-a-cell-value.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.