Hide a sheet using code
Dave...I tried the revision that your provided but no luck. I plead absolute
ignorance when it comes to code so my ability to help myself is minimal.
Following is the code as it now appears:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("A11"), Target) Is Nothing Then
For Each sh In Sheets(Array("Sheet2)"), _
"Sheet2(Item1)", _
"Sheet3(Item2)")
If Target.Value = "" Then sh.Visible = xlSheetHidden
If Target.Value < "" Then sh.Visible = xlSheetVisible
Next sh
End If
End Sub
My current error message is: Wrong number of arguments or invalid property
assignment. Additionally, I'm not sure I understand your question in your
last post hide/show sheet. I was assuming that this procedure would
'unhide' the sheet if cell A11 was populated. Wrong assumption?
"Dave Peterson" wrote:
Did you try this:
For Each sh In Sheets(Array("Sheet2"), _
"Sheet2(Item1)", _
"Sheet3(Item2)")
Be careful with your typing. They have to match--exactly!
Don't you want this same procedure to hide/show the sheets?
LJP wrote:
I have a spreadsheet with approximately 35 sheets all set up the same. I
want to hide a sheet if cell A11 is empty. I'm using the code from another
post but my results are unfavorable.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim sh As Worksheet
If Not Application.Intersect(Range("A11"), 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
The sheets in my workbook are listed in VBA as 'Sheet 2(Item1)',
'Sheet3(Item2)', etc. Item1 being the name displayed on my sheet tab. I
wasn't sure if I needed to alter the Array statement above but I've tried it
every way I can and nothing works. I placed the code on Sheet2(Item1). I
get a 'Subscript out of range' error message whenever data is entered or
removed from cell A11 on sheet Item 1. What am I doing wrong!?
Thanks.
--
Dave Peterson
|