Except that you'll have trouble if the cell is actually empty--or someone puts 2
(or more spaces) in that cell.

These lines:

If Target.Value = "" Then sh.Visible = xlSheetHidden
If Target.Value < "" Then sh.Visible = xlSheetVisible

could be rewritten to:

If Target.Value = "" Then
sh.Visible = xlSheetHidden
else
sh.Visible = xlSheetVisible
end if

and to avoid the space problem:

If trim(Target.Value) = "" Then
sh.Visible = xlSheetHidden
else
sh.Visible = xlSheetVisible
end if

LJP wrote:

FINALLY got it to work! I decided to try and change the target value to
"space" and that did it! Sometimes you just need to walk away for a while.

Thanks your patience Dave.

"Dave Peterson" wrote:

Any you changed that single cell named "A11" on that sheet.

I'd add a line like this near the top of the procedu

Msgbox "Event is firing"

If you don't see that message when you change All, then something is wrong.

I'd check those things you've checked before.

LJP wrote:

Yes, I placed the code behind the single worksheet that contains named range
A11. And yes, I did allow macros to run when I opened the file.

"Dave Peterson" wrote:

Did you put the code behind the single worksheet that owns the range named All?

Did you allow macros to run when you opened the file?

LJP wrote:

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


--

Dave Peterson


--

Dave Peterson


--

Dave Peterson