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.

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.

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.

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.

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.

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.

VBA Code and Protection Sheet
 

Thanks very much!

Amir

wrote:
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.

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.