hide multiple worksheets based on a formula value in one cell

J

Jamonf

Joined
Jan 4, 2014
Messages
2
Reaction score
0
I have a project I'm working on where I need to hide up to 8 worksheets based on the value in a master sheet. essentially I have 8 different data sheets with 50 sections of data on each sheet and I want to hide any sheets that do not have data on them, once I hit 51 pieces of data the second sheet needs to become visible so with 0 data sets I wantonly the master sheet to show, 1-50 Data sheet 1, 51-100 Data sheets 1 & 2, 101-150 1 2 & 3 and so on up to 8 sheets. The data source is a count formula derived from the true false answers from check boxes. i figured out how to do it partially but it will not automatically update using the method I found.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("DC417").Address Then
Sheets("Data Sheet 1").Visible = Target.Value > "0"
Sheets("Data Sheet 2").Visible = Target.Value > "50"
Sheets("Data Sheet 3").Visible = Target.Value > "100"
Sheets("Data Sheet 4").Visible = Target.Value > "150"
Sheets("Data Sheet 5").Visible = Target.Value > "200"
Sheets("Data Sheet 6").Visible = Target.Value > "250"
Sheets("Data Sheet 7").Visible = Target.Value > "300"
Sheets("Data Sheet 8").Visible = Target.Value > "350"
End If
End Sub

how can I make it so the sheets will automatically unhide as the data count hits 51, or 101 and so on?
 
After many hours surfing forums I pieced together something that works for me, wish it were something short and simple but it's just a lot of copy pasting for the most part, here's what I did, if you know of a better way please let me know I'm fairly new to VB and I love to learn new ways of doing things.

Code: 
Private Sub Worksheet_Calculate()
Dim sh As Worksheet
Set sh = Worksheets("Master List")
With Sheets("Data Sheet 1")
If sh.Range("DC417").Value > 0 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value = 0 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 2")
If sh.Range("DC417").Value > 50 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 50 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 3")
If sh.Range("DC417").Value > 100 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 100 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 4")
If sh.Range("DC417").Value > 150 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 150 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 5")
If sh.Range("DC417").Value > 200 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 200 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 6")
If sh.Range("DC417").Value > 250 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 250 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 7")
If sh.Range("DC417").Value > 300 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 300 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 8")
If sh.Range("DC417").Value > 350 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 350 Then
.Visible = xlSheetHidden
End If
End With
With Sheets("Data Sheet 9")
If sh.Range("DC417").Value > 400 Then
.Visible = xlSheetVisible
ElseIf sh.Range("DC417").Value <= 400 Then
.Visible = xlSheetHidden
End If
End With
End Sub
 
Back
Top