Test for if sheet is HIDDEN
 

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

Test for if sheet is HIDDEN
 

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


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

Test for if sheet is HIDDEN
 

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


In article ,
CLR wrote:

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

Test for if sheet is HIDDEN
 

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


"Rick Rothstein (MVP - VB)" wrote:

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


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

Test for if sheet is HIDDEN
 

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



"JE McGimpsey" wrote:

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


In article ,
CLR wrote:

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

Test for if sheet is HIDDEN
 

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


"CLR" wrote in message
...
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


"Rick Rothstein (MVP - VB)" wrote:

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


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

Test for if sheet is HIDDEN
 

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?

In article ,
CLR wrote:

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



"JE McGimpsey" wrote:

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

Test for if sheet is HIDDEN
 

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

Test for if sheet is HIDDEN
 

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)

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"CLR" wrote in message
...
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


"Rick Rothstein (MVP - VB)" wrote:

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


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

Test for if sheet is HIDDEN
 

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


In article ,
"Jim Cone" wrote:

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

Test for if sheet is HIDDEN
 

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

Rick


"Chip Pearson" wrote in message
...
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)

"Rick Rothstein (MVP - VB)" wrote in
message ...
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


"CLR" wrote in message
...
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


"Rick Rothstein (MVP - VB)" wrote:

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


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

Test for if sheet is HIDDEN
 

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


"Jim Cone"

wrote:

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

Test for if sheet is HIDDEN
 

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




"JE McGimpsey" wrote:

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?

In article ,
CLR wrote:

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



"JE McGimpsey" wrote:

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

Test for if sheet is HIDDEN
 

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.

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
(...)

Test for if sheet is HIDDEN
 

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.

In article ,
CLR wrote:

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




"JE McGimpsey" wrote:

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?

In article ,
CLR wrote:

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



"JE McGimpsey" wrote:

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

Test for if sheet is HIDDEN
 

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


"JE McGimpsey" wrote in message
...
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.

In article ,
CLR wrote:

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




"JE McGimpsey" wrote:

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?

In article ,
CLR wrote:

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



"JE McGimpsey" wrote:

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

Test for if sheet is HIDDEN
 

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

"JE McGimpsey" wrote in message
...
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.

In article ,
CLR wrote:

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




"JE McGimpsey" wrote:

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?

In article ,
CLR wrote:

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



"JE McGimpsey" wrote:

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