Hide a sheet with a variable name
hi, Scott !
let's suppose in "Front Page" sheet you put the names for the worksheets to hide/show in B3:B4 range
Private Sub Worksheet_Change(ByVal Target As Range)
Dim myCell As Range
Application.ScreenUpdating = False
If Target.Address < "$B$8" Then Exit Sub
For Each myCell In Range("b3:b4")
Worksheets(myCell.Text).Visible = Range("b8") < ""
Next
End Sub
hth,
hector.
__ OP __
I am trying to hide a sheet(s) based on a cell value. I have found an old post thats uses:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("B8"), Target) Is Nothing Then
For Each sh In Sheets(Array("Sheet2"))
If Target.Value = "" Then sh.Visible = xlSheetHidden
If Target.Value < "" Then sh.Visible = xlSheetVisible
Next sh
End If
End Sub
and this works perfectly. EXCEPT... my problem is that my sheet names change (in this case Sheet2)
depending on the value of other cells in the workbook.
The +ve side is that all the info for the sheet names and wether to hide other sheets
is contained on a single worksheet (called Front Page)
|