Macro won't protect sheet

[shiguera]

Hi every one,

I have a protected sheet and I use a macro to do some calculations. At the beginning of the macro I unprotect the sheet:

ActiveSheet.Unprotect

and it prompts me for the password. Fine so far. At the end of the code I protect the sheet:

ActiveSheet.Protect

The sheet apparently gets protected (I can't edit the cells, which is correct); however, I'm not prompted for the password to protect and if I go to the menu Tools-Protection-Unprotect Sheet... the sheet gets unprotected without asking me for the password. Could any one point out what I'm missing to get the sheet really protected and ask for the password every time I try to unprotect or protec it?

Note: the Workbook is also protected and I do not unprotect it at any time.

 

[yoel]

You need to tell it to protect using the password ("thePassword" represents the password you want to use in the code below)

ActiveSheet.Protect "thePassword"

 

[mikerickson]

You can set a hard coded password with

ActiveSheet.Protect "password"

This will invoke the Un/ProtectSheet dialog box

Application.Dialogs(xlDialogProtectDocument).Show

 

[Phixtit]

ActiveSheet.Unprotect ("Your Password here")
Your Code Here
ActiveSheet.Protect ("Your Password here")

 

[shiguera]

Hi guys,

thanks for the replies. I personally like more the "hidden" option so that the password is not written in the code. Tried it and now everything works great!

Thanks again

 

[Phixtit]

Hi guys,

thanks for the replies. I personally like more the "hidden" option so that the password is not written in the code. Tried it and now everything works great!

Thanks again

Which option are you refering to?

In the VBE you can goto Tools-->VBA Project Properties-->Protection-->Lock project for viewing-->Set a password. Than your code will not be seen unless you enter the password.

If you don't want your macros to be available in the Tools-->Macro-->Macros dialog, you can preface them with the word Private. I.E. Private Sub Macro1()

 

[shiguera]

Oh! thanks, didn't know that other way around it either.

I was referring to Mikerickson's option:
Application.Dialogs(xlDialogProtectDocument).Show

but I see your way is another way to do it without having the password "exposed".

All great answers guys.

 

[Chris Bode]

Ok, follow the steps below carefully,
1.When you write the code to protect then you must also supply the password as parameter to the protect function as
ActiveSheet.Protect (“a”)
Now the sheet is protected with password ‘a’
Then if you execute your ActiveSheet.UnProtect code, it will definitely ask you for password
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o> </o>
Hope you get it
<o> </o>
Have a nice time….
<o> </o>
<o> </o>
Chris
------
Convert your Excel spreadsheet into an online calculator.
http://www.spreadsheetconverter.com