Hide a sheet using code

[LJP]

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]

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]

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 re: hide/show sheet. I was assuming that this procedure would
'unhide' the sheet if cell A11 was populated. Wrong assumption?

 

[Dave Peterson]

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]

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?

 

[LJP]

Dave: May I ask you (in an effort to learn) what 'Sheet2' refers to in the
first line of the revised section of code that you provided? It occurred to
me that you may have written the code like you did based on my original post.
I was using 'Sheet2' at the time because I wasn't sure if I was required to
use 'Sheet2' or 'Sheet2(Item1)' in the line of code. I don't have a plain
ole 'Sheet2' in my VBA workbook sheet listings. I'm sorry if I confused you
but wanted to clarify in an effort to resolve. I feel so close to resolution
I don't want to give up now! Thanks again.

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]

Those strings inside the array() are the names of the worksheets that should
hidden or shown.

You'll want to match the exact spellings of what you see in the worksheet tab
when you're looking at excel.

 

[Dave Peterson]

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?

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?

 

[LJP]

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]

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

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

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.

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.

 

[LJP]

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]

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

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.

 

[LJP]

Thank you, thank you, thank you!

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