Test for if sheet is HIDDEN

[CLR]

Hi All........

Am trying to step through sheets, and if one is hidden, so indicate....

This line don't seem to work

If ActiveSheet.Visible = False Then
"DoThis"
Else
"DoThat"
Endif

Any suggestions please?

Vaya con Dios,
Chuck, CABGx3

 

[Rick Rothstein \(MVP - VB\)]

See if this code snippet helps you...

Dim WS As Worksheet
For Each WS In Worksheets
If Not WS.Visible Then
Debug.Print WS.Name & " is hidden."
End If
Next

Rick

 

[JE McGimpsey]

One way:

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Visible
Case xlSheetVisible
'Do this
Case xlSheetHidden
'Do that
Case xlSheetVeryHidden
'Do nothing?
End Select
Next ws

 

[CLR]

Thanks Rick, I'm able to step through the sheets ok, just having trouble with
the "check for visibility test"

I replaced my
If ActiveSheet.Visible = False Then
with your
If Not WS.Visible Then

but that seems to act the same


I'm using XL97 if that makes a diff.......

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Thanks JE, but this one don't seem to work for me either. Maybe because I'm
still using XL97.

Vaya con Dios,
Chuck, CABGx3

 

[Rick Rothstein \(MVP - VB\)]

I don't have XL97, but I am willing to bet its macro language is
significantly different from the XL2003 that I am using.... obviously since
the code I posted works fine on my system. Hopefully someone knowledgeable
about XL97 will come by to answer your question. For future reference,
always mention the version of XL you are using when asking questions in the
Excel newsgroups.

Rick

 

[JE McGimpsey]

Chuck, you know better than to just say "don't seem to work". What does
that mean?

The code I provided works for XL97. What are you seeing - errors?
crashes?

What are you expecting to happen that doesn't?

 

[Jim Cone]

Hi Chuck,
I guess I'll stick my nose in here and see what happens...
I believe both sets of code you were given will work, however...
with Rick's code
you have to look in the intermediate window in the VBE (Ctrl + G)
to see the results. It does not actually do anything to the sheets.

with JE's code
replace "Select Case ws.Visible" with "Select Case True"
(air code is the most difficult type to write)

Regards,
Jim Cone


"CLR"
wrote in message
Hi All........

Am trying to step through sheets, and if one is hidden, so indicate....

This line don't seem to work

If ActiveSheet.Visible = False Then
"DoThis"
Else
"DoThat"
Endif

Any suggestions please?

Vaya con Dios,
Chuck, CABGx3

 

[Chip Pearson]

I don't have XL97, but I am willing to bet its macro language is

significantly different from the XL2003

You'd lose that bet. XL97 used VBA5 and XL2000+ uses VBA6. The only
differences I can thing of are that VBA6 supports modeless forms and has the
Split and Join functions. Beyond that, they are more or less identical.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

 

[JE McGimpsey]

Oops- perhaps I posted in the wrong code. I meant to post (and did test):

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Visible
Case xlSheetVisible
'Do this
MsgBox ws.Name & " Visible"
Case xlSheetHidden
'Do that
MsgBox ws.Name & " Hidden"
Case xlSheetVeryHidden
'Do nothing?
MsgBox ws.Name & " Very Hidden"
End Select
Next ws

 

[Rick Rothstein \(MVP - VB\)]

LOL... OK, bad guess on my part. I figured 6 years was a long time between
versions.

Rick

 

[Jim Cone]

It is my "Oops" not yours.
As I said, air code is the most difficult to write.
My modification just screws it up.
Your original code works in xl97, if you add a line to make the sheet visible.
(as you have done below)
Jim Cone



"JE McGimpsey"
wrote in message
Oops- perhaps I posted in the wrong code. I meant to post (and did test):
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Select Case ws.Visible
Case xlSheetVisible
'Do this
MsgBox ws.Name & " Visible"
Case xlSheetHidden
'Do that
MsgBox ws.Name & " Hidden"
Case xlSheetVeryHidden
'Do nothing?
MsgBox ws.Name & " Very Hidden"
End Select
Next ws

 

[CLR]

Yeah, I know, <headhanginglow>, sorry
And, I admit, I didnt try either of the suggestions as posted, I just tried
to take out what I thought I needed.
What I'm trying to do is use one macro to step through all the sheets in a
workbook, about 8 of them. And while in each sheet, to call in other macros
to do specific tasks. This is all working fine. Now I'm trying to add a new
outside macro that will identify if each sheet is Hidden or not, and if so,
do one thing and if not, do another. This is where I'm running into trouble.
For instance if I use this in an outside macro, it works fine to tell me if
the sheet is Protected.......

If ActiveSheet.ProtectContents = True Then

But, when I try to use this one to find out if each sheet is Hidden or not,
I get only that they are all hidden, or none are.....the code does not crash,
nor return an error. It simply refuses to differentiate between hidden and
non-hidden sheets.

If ActiveSheet.Visible = True Then

I get the same results with Ricks code of
If Not WS.Visible Then

And the same with variations of your code such as
ws.xlSheetHidden

I'm sure that both of the suggestions work, in the contexts that you all
presented them, but I'm trying to incorporate their principals into what I've
already got, and THAT is what is not working. I did not mean to imply that
either of you offered bad code, and I apologize if that impression was taken.

Incidently, although I write mostly in XL97, I did try these suggestions in
XL2k and XLXP and got the same results.

So, hopefully someone can see my situation here and assist.

Thanks again to all who have responded so far.

Vaya con Dios,
Chuck, CABGx3

 

[Héctor Miguel]

hi, Chuck/Guys ! (just wild ideas)...

activesheet implies/requires that the activeworkbook' window is/stays visible
(and therefore the activesheet is also visible)
for what doesn't require to be proven the visibility of activesheet -?-

if you are not looping through sheets & activating each of them...
is "ActiveSheet" required ?

(as I said, just wild ideas)

regards,
hector.

 

[JE McGimpsey]

The code I gave you (at least the second post) can do exactly what
you're looking for.

You can't activate a hidden sheet, so

If ActiveSheet.Visible = xlSheetHidden

is doomed to return False in every case.

If you don't want to use Select Case, you can instead use If...Then...
to check the .Visible property of an object variable (e.g., ws) in

Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
If ws.Visible = xlSheetVisible Then
'Do whatever for a visible sheet
ElseIf ws.Visible = xlSheetHidden Then
'Do whatever for a hidden sheet
Else
'Do whatever for a Very Hidden Sheet
End If
Next ws.

 

[CLR]

Hi JE.........
Thanks for your efforts, but I'm still getting the same.....all sheets
giving the same answer......must be something I'm overlooking but just too
tired to continue tonight, it's all starting to run together......will look
at it more over the weekend........thanks again for hanging with me.....

Vaya con Dios,
Chuck, CABGx3

 

[CLR]

Hi JE..........

Just to close the loop, you pointed the direction for me, and I thank you.
I never really could make it work using the method I was wanting to, but was
finally able to take it out of that method and used this to get what I
wanted.........

ii = 0
For Each sh In Sheets
ii = ii + 1
Cells(ii + 11, 10).Value = sh.Visible
Next

Thanks again for your help and patience,
Vaya con Dios,
Chuck, CABGx3