Workbook Protection - password stored in named range

[TC1980]

Hi,

Anyone have any ideas why the following code does not work (Invalid procedure call or argument):

Please Login or Register  to view this content.

*nrProtectionPW" is a named range storing the password of XXXX

Yet SHEET protection works:

Please Login or Register  to view this content.

And a hard coded password works:

Please Login or Register  to view this content.

I'm sure I'm missing something here but I can't figure out what. Any help appreciated.

TC

[TMS]

This worked for me:

nrProtectionPW:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

Regards, TMS

[TMS]

Do you have any code using any of the reserved words? Like "structure"? Or any modules or worksheets with the same name?

A sample workbook might aid resolution.


Regards, TMS

[TC1980]

Hi TMS,

Thanks for coming back to me on this. I've searched the project and related projects for the word Structure and can confirm it's not used as any variable, object or sub name.

I've recreated a really basic workbook with just the following three lines of code:

Please Login or Register  to view this content.

A password of "xxxx" stored in a cell on Sheet1 which I have set as the named range: nrProtectionPW
The Test sheet just contains some dummy text (literally: sdfkho;dsf sdmfho;asduif...)


And it's still behaving in exactly the same way - the first two lines execute perfectly and the third line throws out a runtime 5 error. I would upload it - but I'm at work and local security settings prevent me. Grrr.

I'm not sure exactly what you mean by this bit though...

nrProtectionPW:
Formula:
=Workings!$A$1

I'm using Excel 2003 on Windows 2008 R2 server - if that makes any difference... Also got a colleague to test in Excel 2003 in Windows XP and same runtime error...

Sometimes I really do not understand VBA - but then it would appear the feeling is mutual!

Thanks, TC

[JosephP]

perhaps

Please Login or Register  to view this content.

[TC1980]

Hi Josie,

Perhaps indeed! It works perfectly! Sometimes I could tear my hair out with VBA!
Now going back an encasing all passwords in CStr for consistency...

Thanks again for being my saviour!

TC

[TC1980]

Josie,

I can't 'Add Reputation' as you've helped me out so many times before it's saying I must spread some reputation around before I add to yours again. But know that your help is very much appreciated.

Thanks again, TC.

[JosephP]

you're welcome

and a simple thank you is always enough for me :-)

[TMS]

You're welcome. Thanks for the rep.


Not sure why a string would need to be converted to a string ... I just used your code (having set up a workbook, named a worksheet and created a Named Range).

Maybe I should have said: nrProtectionPW: Refers to: =Workings!$A$1 (which has, as its contents, "XXXX" without the quotes)


Regards, TMS

[JosephP]

.Value was the key part-I included CStr in case the cell is empty

[TMS]

@JP: can you explain the logic/rationale? .Value is the default property for a range so, while it is good practice, it shouldn't really make a difference. Confused. Good to have a workaround if I ever encounter this problem but I'd really like to understand what' showing on.

Regards, TMS

[JosephP]

it can make a difference in some situations. for instance where you are effectively late binding-such as using worksheets() which returns an object not a worksheet-and you try to rely on a default property (in my opinion you should never do that-not optional!) I was discussing this a couple weeks ago with a friend and near as we can figure it has something to do with the type of variant returned but we couldn't decide why. if it only happened with late binding it would probably relate to something happening with COM-probably related to QueryInterface-but in this instance it doesn't matter if you use a typed Worksheet variable either. bottom line-again-use explicit properties always

by the way Value is not the default property _Default is :-)

[TMS]

by the way Value is not the default property _Default is :-)

I stand corrected


There is a dialogue here which seems to confirm my, obviously mistaken, impression that .Value was the default property for a range.

But Googling it gets me references that imply that .Item is the default property: http://msdn.microsoft.com/en-us/libr.../ff196273.aspx

Not sure I understand this:

Value is not the default property _Default is

Regards, TMS

[JosephP]

if you inspect the Range class in the object browser with hidden members shown you will see that _Default is the default property although I suspect it is simply an accessor for the Item property-which may be part of the issue at work here

[TMS]

It says:

Property _Default([RowIndex], [ColumnIndex])
Default member of Excel.Range

Is "default member" the same as default property? As opposed to just "member".

What does this tell me:

Please Login or Register  to view this content.

Apart from the fact that there are several ways to get the same data returned. Needless to say, cell B1 has 01/10/2013 in it

No idea what this means:

simply an accessor for the Item property

Regards, TMS

[JosephP]

Is "default member" the same as default property? As opposed to just "member".

yes-all variables/properties/methods are members of the class

What does this tell me...Apart from the fact that there are several ways to get the same data returned. Needless to say, cell B1 has 01/10/2013 in it

nothing really :-)

No idea what this means:

just that the _Default property probably does nothing more than point to the Item property function

[TMS]

Thanks JP; guess we should draw a line. Education session over

Cheers, TMS