Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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) |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 (...) |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#17
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need my Hidden Rows to stay hidden when I print the sheet. | Excel Discussion (Misc queries) | |||
How to test to see if a given sheet from is in a workbook from ano | Excel Programming | |||
test if a sheet exist (with the name) ? | Excel Programming | |||
Saving hidden data with a worksheet (preferably without using a hidden sheet) | Excel Programming | |||
Problem pasting a row from a hidden sheet to the first free row on another visible sheet | Excel Programming |