Script to unprotect a worksheet, edit, re-protect?

09-28-2004, 06:46 AM

GFritz34

Centurion

Join Date: Aug 2004

Posts: 173

Script to unprotect a worksheet, edit, re-protect?

I'm using Excel to store some historical user events. I don't want a user to be able to open these sheets offline w/out a password, therefore I need protection on.

How can I do this in VBA?

09-28-2004, 07:02 AM

Greychild

Centurion

Join Date: Jan 2004

Location: Hong Kong

Posts: 176

Code:

Activesheet.Unprotect("MyPassword")
'your code
Activesheet.Protect("MyPassword")

09-28-2004, 08:25 AM

Wheels1978 Wheels1978 is offline

Junior Contributor

Join Date: Apr 2004

Location: 's-Gravenpolder, NL

Posts: 389

I thought it was

Code:

ActiveSheet.Unprotect password:="MyPassword"
'your code
ActiveSheet.Protect password:="MyPassword"

Maybe they both work. Haven't tried Greychild's code yet.

__________________
"Accept what you can not change and change what you can not accept" - Me
"Normal is the average of all oddities" - Me
"Don't re-invent the wheel!" - My dad
Please enclose your code in [vb] and [/vb] tags

09-28-2004, 09:35 AM

GFritz34

Centurion

Join Date: Aug 2004

Posts: 173

Quote:

Originally Posted by Greychild

Code:

Activesheet.Unprotect("MyPassword")
'your code
Activesheet.Protect("MyPassword")

This is working, thanks alot. I was trying something similar but on the application not the sheet so that was why it wasn't working.

09-28-2004, 11:15 AM

Greychild

Centurion

Join Date: Jan 2004

Location: Hong Kong

Posts: 176

Happy to help - don't forget the macro recorder is often a great place to start with things like that.

Wheels, your code is equivalent. Your code uses a named argument, whereas mine takes advantage of the fact that the first argument for Protect is the password. If it were the 10th argument, I would either have to use your method, or use a lot of commas with nothing in between:

Code:

'This is an example, not real code
Activesheet.Protect(,,,,,,,,,"MyPassword")

The commas with nothing in between mean that I am accepting the defaults for the 9 preceding optional arguments. Obviously, this would make the code very hard to read (and write).

09-29-2004, 01:18 AM

Wheels1978 Wheels1978 is offline

Junior Contributor

Join Date: Apr 2004

Location: 's-Gravenpolder, NL

Posts: 389

Quote:

Originally Posted by Greychild

Wheels, your code is equivalent.

I thought it was like that. The help also mentions the stuff between commas. Personally I prefer the named arguments, just for readability. I have had some bad experience with an ex-colleague of mine, who didn't even put comments in his code.