Asp Forum - VBA Code and Protection Sheet - microsoft.public.excel.programming

amirstal

12/14/2006 7:40:00 PM

Does Excel have a problem running a VBA code when the worksheet is
protected? If yes, is there a way to overcome it?

Thanks.

7 Answers

theSquirrel

12/14/2006 7:44:00 PM

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.

amirstal

12/14/2006 8:03:00 PM

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.

thesquirrel@gmail.com 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.

Kai Uwe Schmidt

12/14/2006 8:13:00 PM

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").Unprotect "password"
...
(your code)
...
ActiveWorkbook.("ProtectedSheet").Protect "password"
End Sub

Regards,

Kai
Cologne, Germany

"amirstal" <amirstal@yahoo.com> schrieb im Newsbeitrag
news:1166125184.883438.123430@j72g2000cwa.googlegroups.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.
>

amirstal

12/14/2006 9:38:00 PM

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

theSquirrel

12/14/2006 9:52:00 PM

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.
>
> thesquirrel@gmail.com 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.

amirstal

12/15/2006 3:27:00 AM

Thanks very much!

Amir

thesquirrel@gmail.com 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.
> >
> > thesquirrel@gmail.com 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.

amirstal

12/15/2006 3:27:00 AM

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

microsoft.public.excel.programming

VBA Code and Protection Sheet

amirstal

theSquirrel

amirstal

Kai Uwe Schmidt

amirstal

theSquirrel

amirstal

amirstal

x Login to ForumsZone