Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
Does Excel have a problem running a VBA code when the worksheet is
protected? If yes, is there a way to overcome it? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
unlock the worksheet before running the code and lock it back up when you are done... Activesheet.Unprotect "<password" 'code here Activesheet.Protect "<password" amirstal wrote: Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
Should I put this at the top of the code (just below its name: Sub
EoDProcess())? "<password" - is where I should write the password that protects the sheet, right? Thanks. wrote: unlock the worksheet before running the code and lock it back up when you are done... Activesheet.Unprotect "<password" 'code here Activesheet.Protect "<password" amirstal wrote: Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
Hi,
if you want to change cells´values, you have to unprotect them, yes. If you want to lock the sheet afterwards again, insert two rows in your code: Public Sub changingData ActiveWorkbook.Worksheets("ProtectedSheet").Unprot ect "password" ... (your code) ... ActiveWorkbook.("ProtectedSheet").Protect "password" End Sub Regards, Kai Cologne, Germany "amirstal" schrieb im Newsbeitrag oups.com... Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
I am a bit confused now with all the answers.
I protect the sheet thru Tools/Protection/Protect Sheet so other users wont erase formulas by mistake. Can the macro/VBA I run do the Unprotect Sheet function automatically for me and re-protect it once the it is done running? Amir Jim Thomlinson wrote: There are two possible solutions to your problem. If the sheet is protected via code then you can add a paramter which will allow code to make any changes it wants while still restricting the user. Something like this... Sheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="Tada" If that is not how the sheet is protected then you need to unprotect the sheet each time Code is going to make any changes to the sheet similar to what has already been posted... -- HTH... Jim Thomlinson "amirstal" wrote: Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
What you wrote is exactly what you should do...
Sub Test() ActiveSheet.Unprotect "password" 'your code here ActiveSheet.Protect "password" End Sub where 'password' is your sheet password when the sub is run, it will run through from beginning to end first unprotecting, running your code, then finally reprotecting the sheet. You can alternatively encompass a single line or any portion of code with the Unprotect/Protect code we have given you. Example: Sub Test() 'beginning of your code ActiveSheet.Unprotect "password" Msgbox "just a portion of your code" ActiveSheet.Protect "password" 'more of your code End Sub theSquirrel amirstal wrote: Should I put this at the top of the code (just below its name: Sub EoDProcess())? "<password" - is where I should write the password that protects the sheet, right? Thanks. wrote: unlock the worksheet before running the code and lock it back up when you are done... Activesheet.Unprotect "<password" 'code here Activesheet.Protect "<password" amirstal wrote: Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Code and Protection Sheet
Thanks very much!
Amir Jim Thomlinson wrote: Yes VBA can be used to protect and unprotect a sheet... Sub YourSub() ActiveSheet.Unprotect Password:="password" 'your code here ActiveSheet.Protect Password:="password" End Sub -- HTH... Jim Thomlinson "amirstal" wrote: I am a bit confused now with all the answers. I protect the sheet thru Tools/Protection/Protect Sheet so other users wont erase formulas by mistake. Can the macro/VBA I run do the Unprotect Sheet function automatically for me and re-protect it once the it is done running? Amir Jim Thomlinson wrote: There are two possible solutions to your problem. If the sheet is protected via code then you can add a paramter which will allow code to make any changes it wants while still restricting the user. Something like this... Sheets("Sheet1").Protect UserInterfaceOnly:=True, Password:="Tada" If that is not how the sheet is protected then you need to unprotect the sheet each time Code is going to make any changes to the sheet similar to what has already been posted... -- HTH... Jim Thomlinson "amirstal" wrote: Does Excel have a problem running a VBA code when the worksheet is protected? If yes, is there a way to overcome it? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Sheet protection code conflicts with Pivot Table "auto refresh" | Excel Discussion (Misc queries) | |||
Sheet Protection and XLA code | Excel Programming | |||
Sheet Protection and VBA Code | Excel Discussion (Misc queries) | |||
disabling the "view code" tab - sheet protection? | Excel Programming | |||
protection sheet and code ??? | Excel Programming |