macro for Protecting a Sheet

[Guest]

Hello everyone...

Just wondering what the line of code would look like that protects a sheet
via the press of a (macro) button. For reason undisclosed, I dont want users
to go the Tools / Protection routine, but I can only get the macro to work
without password protection. Here is what I am using:

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

but again this only works without a password.

Any ideas?

 

[Alan]

Try
ActiveSheet.Protect ("Your_Password")
Regards,
Alan.

 

[Guest]

Hi Alan & thanks...

This works great, although it wasn't exactly what I was looking to
accomplish since this approach predefines the password (assuming I'm applying
your suggestion correctly). Consequently however, it does solves another
issue with this spreadsheet - namely admin security and control.

These users arent sophistated enough to know how to compromise an xls
password, but I've incorporated adminstrator control regardless to address
multi users (families members) who have editing privlidges via 'Allow Users
to Edit Ranges'. I'm giving this spreadsheet to acquentences so its not a
Corp thing, but this approach will require I give those less knowledgable
with VB instructions on how to modify the password within the macro. Not a
problem though.

Thanks again for the tip. Much appreciated.

 

[Paul B]

cincode5, if you want the user to pick a password you could use something
like this

Sub protect()
Dim pw As String
pw = InputBox("Enter your password to protect the sheet ?")
ActiveSheet.protect Password:=pw, DrawingObjects:=True, _
Contents:=True, Scenarios:=True
End Sub


--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003

 

[Guest]

Paul...
Even better... Thanks for the tip.

I needed to have the individulal admin set the password without accessing VB
code. This is what I was looking for.

Thanks again Paul!

 

[Paul B]

Your Welcome

--
Paul B
Always backup your data before trying something new
Please post any response to the newsgroups so others can benefit from it
Feedback on answers is always appreciated!
Using Excel 2002 & 2003