Sheet protection - passwords

[ElvisSteel]

Hi
I have the following macro...
Sub ProtectSheets()
Sheets("Outlook").Protect EnableSelection = xlNoSelection
Sheets("FY Utilisation Report").Protect EnableSelection = xlNoSelection
Sheets("Resource Check").Protect EnableSelection = xlNoRestrictions
Sheets("Project").Protect
Sheets("Reports 1").Protect EnableSelection = xlNoSelection
Sheets("Reports 2").Protect EnableSelection = xlNoSelection
Sheets("Tables").Protect
Sheets("Input").Protect
End Sub

When I run this, all of these are protected, but when I try to unprotect them (using the ribbon button) all of the sheets where I have specified xlNoSelection now require a password!
I don't know what the password is, so I am basically stuck.
Any ideas as to....
1. Why this is happening?
2. What the password may be?

Thanks

 

[Andrew Poulsom]

You have missed a colon in:

Sheets("Outlook").Protect EnableSelection:= xlNoSelection

So instead of using the named argument EnableSelection the statement:

EnableSelection = xlNoSelection

is used as the first argument, which happens to be Password.

 

[ElvisSteel]

Andrew,

Thanks for that - beginners error, but I am unable to unlock the sheet using "EnableSelection = xlNoSelection" as the password
Also do you know why the other sheets are not password protected?
It is only those with xlNoSelection that have a password.

 

[Andrew Poulsom]

Did you surround EnableSelection = xlNoSelection with quotes when you tried to unprotect the sheet (in code)? If so try removing them. The other sheets don't have a password because you didn't set one when you protected them.

 

[ElvisSteel]

Andrew,

Thanks so much for your instant response

The code...

Sheets("Reports 2").Unprotect Password:=EnableSelection = xlNoSelection

...unprotected the sheet

I'm still a little uncertain as to why the "Resource Check" sheet did not have a password set (as I had specified xlNoRestrictions with the colon missing), but for now the problem is solved and a lesson learned!

Regards

Steve

 

[Andrew Poulsom]

What does this return?

MsgBox EnableSelection = xlNoRestrictions

By the way, I strongly advise you to check Require Variable Declaration under Tools|Options|Editor tab. That way Option Explicit will appear at the top of each new module and your project won't compile if there are any undeclared variables.

 

[ElvisSteel]

Thanks Andrew,

I am using Excel 2007. Any idea where I can find this setting?

 

[ElvisSteel]

MsgBox EnableSelection = xlNoRestrictions
returns TRUE

 

[Andrew Poulsom]

Thanks Andrew,

I am using Excel 2007. Any idea where I can find this setting?

Has the menu in the Visual Basic Editor changed in Excel 2007?

 

[Jon von der Heyden]

Has the menu in the Visual Basic Editor changed in Excel 2007?

Nope