Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
I attempted to edit a macro from you folks to protect all sheets, because I
noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
try:
Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets On Error resume next wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True, AllowFormattingCells:=True, AllowFormattingColumns:=True,AllowFormattingRows:= True Next wks End Sub Corey.... "Dean" wrote in message ... I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
Sub ProtectAllWorksheets()
Dim wks As Worksheet For Each wks In Worksheets If wks.Visible = xlSheetvisible Then wks.Activate wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True end if Next wks End Sub -- Regards, Tom Ogilvy "Dean" wrote in message ... I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
Corey's solution will work. Perhaps this will also.
First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
Thanks to all, especially you for explaining all this JL - someday it will
sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
Nick, or whoever, was correct - it's more efficient and much quicker if you
can do things without either activating or selecting them. And yes, the code I put up avoids both. Look in the code that's causing the flashing and see if there isn't a ..Activate or .Select somewhere in it. There may be. Screen updating often causes a slow down also. And that can occur even when you don't select or activate anything - Try the following code in a test workbook Sub FillUpCellsTest() Dim LoopCounter as Integer Cells.Clear ' remove any old results Range("A1").Select For LoopCounter = 1 to 1000 Activecell = "At Row " & LoopCounter ActiveCell.Offset(1,0).Activate Next MsgBox "All Done Now" End Sub All that activating and even just placing the value into the cells will cause screen scrolling or flashing and a slowdown. Next lets try it without the flashy stuff. By the way, you might even make the For Next loop go further say to 5000 just so you can start telling difference in how long it takes to execute with each of these methods. We make a simple change to the code, just adding 2 lines Sub FillUpCellsTest() Dim LoopCounter as Integer Cells.Clear ' remove any old results Range("A1").Select Application.ScreenUpdating = False For LoopCounter = 1 to 1000 Activecell = "At Row " & LoopCounter ActiveCell.Offset(1,0).Activate Next Application.ScreenUpdating = True MsgBox "All Done Now" End Sub Technically the Application.ScreenUpdating = True reset at the end isn't necessary, but I'm kind of retentive about resetting things I've changed to their original state. Run it and see how much faster it is. Next lets see what happens when we do it without activating the cells. Sub FillUpCellsTest() Dim LoopCounter as Integer Cells.Clear ' remove any old results Range("A1").Select ' becomes the active cell For LoopCounter = 0 to 999 ActiveCell.Offset(LoopCounter,0) = "Count Is " & LoopCounter Next MsgBox "All Done Now" End Sub as a final trial, put the Application.ScreenUpdating code into that last version and (not) watch it just fly! On my AMD X2 4800+ system the approximate times I got in each of the trials (best time - it varies depending on what else the system is doing) with a 1 to 10000 or 0 to 9999 loop we 4.16 Seconds 0.6 (to 1.2) just by using .ScreenUpdating=False 0.4 by going to .Offset() 0.1 by going to .Offset() and turning ScreenUpdating off The general theory also holds that things already in memory that don't have to be reinterpreted are worked with faster. This can also make a difference within loops. Take the literal string in it "At Row " or "Count Is". If we'd have set those up outside of the loop and written the whole thing like this (this will give you time to execute also: Sub FillUpCellsTestReallyFast() Dim LoopCounter As Integer Dim myPhrase As String Dim StartTime As Double Dim EndTime As Double Cells.Clear ' remove any old results Range("A1").Select ' becomes the active cell myPhrase = "Count Is " Application.ScreenUpdating = False StartTime = Timer For LoopCounter = 0 To 9999 ActiveCell.Offset(LoopCounter, 0) = myPhrase & LoopCounter Next EndTime = Timer Application.ScreenUpdating = True MsgBox "All Done Now: " & EndTime - StartTime & " Seconds to execute." End Sub I was able to get consistent times of approximately 0.4 seconds with that last routine. But when doing it the 'original' way the time was a consistent 4+ seconds. So there is at least a 10-fold improvement in performance time using these methods (for this test case). It can go to even larger numbers if there is a lot of work to be done with cells, such as moving and copying or filling with custom formulas within VBA code. I've seen cases where the difference was as much as 30 or more. I have seen 30 second processes reduced to completion in under a second in the past. "Dean" wrote: Thanks to all, especially you for explaining all this JL - someday it will sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
P.S. - you said "...the macro I sent you didn't..." - did you send email to
me? If so, I haven't received it. My email address is HelpFrom @ jlathamsite.com (remove spaces) for assistance requests related to these forums. Or maybe you meant "...the macro I showed you didn't..." referring to the code in the original post? "Dean" wrote: Thanks to all, especially you for explaining all this JL - someday it will sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
I meant the macro I posted in my query to the forum.
"JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... P.S. - you said "...the macro I sent you didn't..." - did you send email to me? If so, I haven't received it. My email address is HelpFrom @ jlathamsite.com (remove spaces) for assistance requests related to these forums. Or maybe you meant "...the macro I showed you didn't..." referring to the code in the original post? "Dean" wrote: Thanks to all, especially you for explaining all this JL - someday it will sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
Now that J Latham has given you a tutorial on all the foibles of selecting,
I will mention that I have seen problems in terms of very strange behavior manually selecting on a sheet after that sheet was protected when it is not the active sheet. Thus I added the code to replace your Activesheet to include wks.Activate before unprotecting. This speed penalty is readily and largely mitigated by supressing screenupdating. So, perhaps this isn't a prevelant problem, but it is no problem at all when the sheet is activated before protecting (or unprotecting). Just some added info. -- Regards, Tom Ogilvy "Dean" wrote in message ... I meant the macro I posted in my query to the forum. "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... P.S. - you said "...the macro I sent you didn't..." - did you send email to me? If so, I haven't received it. My email address is HelpFrom @ jlathamsite.com (remove spaces) for assistance requests related to these forums. Or maybe you meant "...the macro I showed you didn't..." referring to the code in the original post? "Dean" wrote: Thanks to all, especially you for explaining all this JL - someday it will sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
protect all sheets macro crashes when sheet is hidden
OK, good info, Thanks
"Tom Ogilvy" wrote in message ... Now that J Latham has given you a tutorial on all the foibles of selecting, I will mention that I have seen problems in terms of very strange behavior manually selecting on a sheet after that sheet was protected when it is not the active sheet. Thus I added the code to replace your Activesheet to include wks.Activate before unprotecting. This speed penalty is readily and largely mitigated by supressing screenupdating. So, perhaps this isn't a prevelant problem, but it is no problem at all when the sheet is activated before protecting (or unprotecting). Just some added info. -- Regards, Tom Ogilvy "Dean" wrote in message ... I meant the macro I posted in my query to the forum. "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... P.S. - you said "...the macro I sent you didn't..." - did you send email to me? If so, I haven't received it. My email address is HelpFrom @ jlathamsite.com (remove spaces) for assistance requests related to these forums. Or maybe you meant "...the macro I showed you didn't..." referring to the code in the original post? "Dean" wrote: Thanks to all, especially you for explaining all this JL - someday it will sink in. My understanding, from some other poster (was it Nick) is that it's more efficient not to activate the worksheets and I think your approach avoids such, right? In fact, you can probably tell that the macro I sent you didn't even work with sheets unhidden. I hybridized my existing working macro which did activate sheets; and one from, I think Nick, which didn't activate sheets and sent that to you! The only thing I still find confusing is that I copied the same routine into my unprotect all sheets macro, when I unprotect, I see the screen flash like crazy whereas, when I protect, it doesn't move at all. I'm pretty sure the only difference is one has one row that says unprotect, the other one protects. No biggie, but I'm curious why. Do you know? Dean "JLatham" <HelpFrom @ Jlathamsite.com.(removethis) wrote in message ... Corey's solution will work. Perhaps this will also. First, there's no .Hidden property for the worksheet object (there is for rows and columns), so that was a problem. The second problem was that within your For Each loop your IF test was always ONLY looking at the current ActiveSheet - so the result of that test was always the same (and usually the active sheet is visible <g). When you work through a collection as you were doing with the For Each loop, the individual members of the collection aren't actually activated, per se, so that's why ActiveSheet. was always the same sheet. Sub TestForHiddenSheets() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets If wks.Visible = xlSheetVisible Then '...your code to protect/unprotect here End If Next End Sub So you test the .Visible property. And since there are 3 possible conditions or values; xlSheetVisible (just what it says - the sheet is visible in the workbook) xlSheetHidden (hidden, but visible in the Format | Sheet [Unhide] list) xlSheetVeryHidden (hidden from view completely - not even in the list of hidden sheets) the best thing to test for is the one condition: Is it Visible and if it is, then act on it. Hope this helps you to some small degree. "Dean" wrote: I attempted to edit a macro from you folks to protect all sheets, because I noticed that, if I hid some sheets, then the macro crashed. I added two rows, the if statement and the rem statement. This is still crashing with a worksheet hidden. I guess it may be that it can't select a sheet if it is hidden, so that my if statement is too late, or else my syntax is wrong! Kindly help. Thanks! Dean Sub ProtectAllWorksheets() Dim wks As Worksheet For Each wks In Worksheets If ActiveSheet.Hidden = True Then GoTo L1 wks.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True _ , AllowFormattingCells:=True, AllowFormattingColumns:=True, _ AllowFormattingRows:=True L1: Rem Next wks End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Hide or protect hidden rows/columns - macro help | Excel Programming | |||
Protect hidden rows/columns -macro help | Excel Discussion (Misc queries) | |||
How do I password protect hidden sheet? | Excel Discussion (Misc queries) | |||
"With Sheets" Issue - macro on one sheet to affect hidden rows on other sheets | Excel Programming | |||
Protect Sheet > All sheets | Excel Discussion (Misc queries) |