Password protecting a sheet, while allowing vba to freely unprotect

[Yeoman.jeremy]

Hi there.

I have recently encountered a problem with my excel program, in which the manager of a particular shop is being very sour about change, and it is my impression that he is purposefully unprotecting and protecting the sheet again after mucking around with little bits of formulae.

I know that I can use a password to prevent people other than myself to unprotect the sheet, but this isn't ideal, as my vba coding requires the unprotecting and protecting of the sheet. is there a way that i can get around having to type in the password everytime i use a piece of code?

Many thanks

 

[Ron Coderre]

If you protect the worksheet with VBA, one of the arguments of Protect is UserInterfaceOnly. When set to TRUE, VBA code can access all aspects of the sheet without unprotecting it, while users will be restricted.

Example:

ActiveSheet.Protect Password:="guesswhat", UserInterfaceOnly:=True

Does that help?

 

[Yeoman.jeremy]

Wait, so let me get this straight
If i used the formula you just gave me to protect my sheets instead of through the options, vba ignores the protection when it does it's workings?

 

[Ron Coderre]

Wait, so let me get this straight
If i used the formula you just gave me to protect my sheets instead of through the options, vba ignores the protection when it does it's workings?

That is correct. Your program can run roughshod all over the worksheet as if it's not protected. The users, on the other hand will be hobbled by whatever restrictions you included in the protection settings.

 

[Yeoman.jeremy]

Oh my goodness!
This is such a huge help!

Up until now, I've been including the ridiculous amounts of unprotecting and protecting within each module.


ahhhh thank you very kindly

 

[Ron Coderre]

You're very welcome
....BTW...we've all been there with the protection thing.

 

[Yeoman.jeremy]

I just had a thought..

If the vba will ignore the protection when coded, can i still use (as an example)

ActiveSheet.UsedRange = ""

does the "UsedRange" relate to only the unlocked cells, or the cells that can be changed?

 

[Ron Coderre]

I just had a thought..

If the vba will ignore the protection when coded, can i still use (as an example)

ActiveSheet.UsedRange = ""

does the "UsedRange" relate to only the unlocked cells, or the cells that can be changed?

Just try it and see...(save your work first, of course)

 

[Yeoman.jeremy]

there is a problem with

UserInterfaceOnly:=True

"Compile Error, Named Argument not found"

 

[Ron Coderre]

When in the VBA editor...
You *did* reference a worksheet...right?
Like in my example:
ActiveSheet.Protect Password:="guesswhat", UserInterfaceOnly:=True