Setting password via VBA named cell

[MCubitt]

Hi

I am getting a run-time error '5' (Invalid procedure call or arguement) when trying to process the following:

ActiveWorkbook.Protect password:=[WorksheetPass]

WorkSheetPass is a named reference to a cell on sheet 2 (active is 1). It contains a value, such as "fred".

The statement it fails on is inside a subroutine.

When I use
ActiveWorkbook.Unprotect password:=[WorksheetPass]
from within the main macro it is fine.

Do I need to pass the value via teh subroutine call?





Applications Support
UK

 

[SkipVought]

Hi,

ActiveWorkbook.Unprotect password:=sheets("Sheet 2").[WorksheetPass]

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MCubitt]

Sorry, both statements are in the same sub.



This works (and happens before):
' Unprotect worksheet to allow update to cells
ActiveWorkbook.Unprotect password:=[WorksheetPass]
Sheets(1).Unprotect password:=[WorksheetPass]

But this, a little later, doesn't:
' Protect workbook and sheet
ActiveWorkbook.Protect password:=[WorksheetPass]
Sheets(1).Protect password:=[WorksheetPass]

I tried referncing the sheet, no joy.



Applications Support
UK

 

[MCubitt]

I tried:
MsgBox ([WorksheetPass])

Just before, and the value is correct.

I tried this: Sheets(1).Protect password:=[WorksheetPass]

and it works.

Its the work book protection I am having a problem with.


very odd...



Applications Support
UK

 

[SkipVought]

Please post all the code in this procedure.

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}

 

[MCubitt]

Well, I got around it using this:
pass = [Worksheetpass]
ActiveWorkbook.Protect password:=pass

It seems to be a problem with using named cell values with ActiveWorkbook.Protect.

Very odd!




Applications Support
UK

 

[SkipVought]

Nope! I use your code and it works!

Skip,

_{[red]Be advised:[/red] The dyslexic, agnostic, insomniac, lays awake all night wondering...
"Is there really a DOG?"}