Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a protected worksheet
One of our users has recorded two simple macros, one to apply a custom filter
and one to remove the filter. It is required that the worksheet cells with formulas be protected, and they are scattered over a broad range. When protected, the macros will not run. Security is set to medium, and both of the Trusted Sources check boxes are check. Is there a workaround for this behaviour? Thanks for any suggestions Paul |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a protected worksheet
Hi Paul,
If the macros run but then produce an error then you should be able to get them to work by including a line of code that unprotects the sheet. This line will have to come before any other code lines that attempt to change the sheet. If the protection was applied without a password then just use... ActiveSheet.Unprotect If password eg 123 was used then use.. ActiveSheet.Unprotect(123) After the macro code has done all its changes to the sheet the code can reinstate the protection with the final line.. ActiveSheet.Protect 'if no password OR... ActiveSheet.Protect(123) 'if password is 123 Ken Johnson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a protected worksheet
You can unprotected sheet and protect sheet in the macro ActiveSheet.Unprotect 'your code here ActiveSheet.Protect -- davesexcel ------------------------------------------------------------------------ davesexcel's Profile: http://www.excelforum.com/member.php...o&userid=31708 View this thread: http://www.excelforum.com/showthread...hreadid=535754 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a protected worksheet
Perfect, thank you.
"Ken Johnson" wrote: Hi Paul, If the macros run but then produce an error then you should be able to get them to work by including a line of code that unprotects the sheet. This line will have to come before any other code lines that attempt to change the sheet. If the protection was applied without a password then just use... ActiveSheet.Unprotect If password eg 123 was used then use.. ActiveSheet.Unprotect(123) After the macro code has done all its changes to the sheet the code can reinstate the protection with the final line.. ActiveSheet.Protect 'if no password OR... ActiveSheet.Protect(123) 'if password is 123 Ken Johnson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro in a protected worksheet
You're welcome Paul.
Thanks for the feedback. Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro error when worksheet is protected | Excel Discussion (Misc queries) | |||
How to run Macro functionality for a protected worksheet | Excel Programming | |||
Macro fails when worksheet protected | Excel Discussion (Misc queries) | |||
How can I run a macro in a protected worksheet? | Excel Worksheet Functions | |||
Protected worksheet and macro | Excel Programming |