Thank you for the quick response...the good news is I'm no longer getting any
error messages! The bad news....nothing happens when data is entered or
removed in cell A11.

Any other suggestions?

"Dave Peterson" wrote:

I screwed up the closing )'s when I added the other sheet names:

For Each sh In Sheets(Array("sheet2", _
"sheet2(Item1)", _
"sheet3(Item2)"))

Sorry.

But do watch out for the spelling. I'm not sure if there are spaces anywhere in
those sheetnames.



LJP wrote:

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


--

Dave Peterson