Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
bug in protect-unprotect macro
Excel 2002 SP3
Win XP Pro *Follow-up to: microsoft.public.excel.programming* Hi, I have a bug with the line ".EnableSelection = xlUnlockedCells" in this macro: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub It's always worked in the past and I can't understand what is happening now. Be easy on me as I am a newbie at XL macros/VBA.... |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in protect-unprotect macro
The "bug" shows itself in what manner?
Gord Dibben MS Excel MVP On Tue, 09 Oct 2007 14:43:39 -0400, stef wrote: Excel 2002 SP3 Win XP Pro *Follow-up to: microsoft.public.excel.programming* Hi, I have a bug with the line ".EnableSelection = xlUnlockedCells" in this macro: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub It's always worked in the past and I can't understand what is happening now. Be easy on me as I am a newbie at XL macros/VBA.... |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in protect-unprotect macro
Error "438':
Object doesn't support this property or method I should have posted that, sorry. Gord Dibben wrote: The "bug" shows itself in what manner? Gord Dibben MS Excel MVP On Tue, 09 Oct 2007 14:43:39 -0400, stef wrote: Excel 2002 SP3 Win XP Pro *Follow-up to: microsoft.public.excel.programming* Hi, I have a bug with the line ".EnableSelection = xlUnlockedCells" in this macro: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub It's always worked in the past and I can't understand what is happening now. Be easy on me as I am a newbie at XL macros/VBA.... |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in protect-unprotect macro
Do you have any chart sheets or such? If so then you will get the error you
discussed. Try this to see if it goes any better... Sub ProtectAllSheets() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets With wks .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next wks Application.ScreenUpdating = True End Sub -- HTH... Jim Thomlinson "stef" wrote: Error "438': Object doesn't support this property or method I should have posted that, sorry. Gord Dibben wrote: The "bug" shows itself in what manner? Gord Dibben MS Excel MVP On Tue, 09 Oct 2007 14:43:39 -0400, stef wrote: Excel 2002 SP3 Win XP Pro *Follow-up to: microsoft.public.excel.programming* Hi, I have a bug with the line ".EnableSelection = xlUnlockedCells" in this macro: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub It's always worked in the past and I can't understand what is happening now. Be easy on me as I am a newbie at XL macros/VBA.... |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in protect-unprotect macro
Jim,
You are totally correct. That must be the problem; I do have charts.... Let me try your version and post back here. Thanks. Jim Thomlinson wrote: Do you have any chart sheets or such? If so then you will get the error you discussed. Try this to see if it goes any better... Sub ProtectAllSheets() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets With wks .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next wks Application.ScreenUpdating = True End Sub |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
bug in protect-unprotect macro
It does work perfectly.
What about undoing it--the un-protect part: Sub UnprotectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count Sheets(n).Unprotect Password:="nosecret" Next n Application.ScreenUpdating = True End Sub Now becomes: Sub UnprotectAllSheets() Application.ScreenUpdating = False For Each wks In Worksheets With wks Sheets(n).Unprotect Password:="nosecret" End With Next wks Application.ScreenUpdating = True End Sub *Is that correct?* Jim Thomlinson wrote: Do you have any chart sheets or such? If so then you will get the error you discussed. Try this to see if it goes any better... Sub ProtectAllSheets() Dim wks As Worksheet Application.ScreenUpdating = False For Each wks In Worksheets With wks .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next wks Application.ScreenUpdating = True End Sub |
#7
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
bug in protect-unprotect macro
I did tweak it a bit, but didn't have problems where you did.
Sub ProtectAllSheets() Dim WS As Worksheet Application.ScreenUpdating = False For Each WS In ThisWorkbook.Worksheets With WS .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next WS Application.ScreenUpdating = True End Sub -- HTH, Barb Reinhardt "stef" wrote: Excel 2002 SP3 Win XP Pro *Follow-up to: microsoft.public.excel.programming* Hi, I have a bug with the line ".EnableSelection = xlUnlockedCells" in this macro: Sub ProtectAllSheets() Application.ScreenUpdating = False Dim n As Single For n = 1 To Sheets.Count With Sheets(n) .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next n Application.ScreenUpdating = True End Sub It's always worked in the past and I can't understand what is happening now. Be easy on me as I am a newbie at XL macros/VBA.... |
#8
Posted to microsoft.public.excel.programming,microsoft.public.excel
|
|||
|
|||
bug in protect-unprotect macro
Bard, same as the one Jim posted and I thank both very much for it--it
does work and makes my life easier. Barb Reinhardt wrote: I did tweak it a bit, but didn't have problems where you did. Sub ProtectAllSheets() Dim WS As Worksheet Application.ScreenUpdating = False For Each WS In ThisWorkbook.Worksheets With WS .Protect Password:="nosecret" .EnableSelection = xlUnlockedCells End With Next WS Application.ScreenUpdating = True End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Protect and Unprotect all worksheets with macro | Excel Discussion (Misc queries) | |||
400 Error in Unprotect/Protect Macro | Excel Discussion (Misc queries) | |||
Unprotect and protect sheet in a macro | Excel Programming | |||
Protect/Unprotect Sheet from Macro | Excel Programming | |||
Macro to protect and unprotect | Excel Programming |