Set VBA Project Protection code in Excel 2007

[eti3nne]

I have just had Office 2007 installed and am wondering if protection arrangements have changed in Excel2007 (vs 2003).

I set the 'protect project from viewing' to yes and add a password and save. Then when I reopen the spreadsheet and select view code I expect to be asked for a password, yet I am not!

On further investigation, when I check the set project password - it is not set and there is indeed no password!?

This seems to be true for 2010 also, (as seen on another PC).

Can some of you please check that your installation is either as mine , working correctly, or advise on the correct method to protect code in 2007/2010.

Thanks for looking.

 

[eti3nne]

Hah!!

In 2003 one can set the project protection no problem and this then denies access to the code and also denies access to the sheets properties.

Marvellous.

In 2007 one can also set the project protection no problem, however I have discovered that it [silently] only 'takes' the setting if there is indeed any non commented-out code in the sheets.

OK so that shouldn't be a problem should it? No code to protect after all?

Well yes and no! No code to protect, but I still want to deny others from viewing the formulae etc in cells.

I have usually done this by setting the sheet password and not allowing viewing of either protected or unprotected cells.

On this occasion, I have some selection dropdowns for which I cannot protect the cell contents, as obviously these may need to be changed by the user. Hence I have set the sheet protection as allow viewing of unprotected cells - not a problem as it's some of the other cells I do NOT want to be viewed.

OK Still with me?...

In my 2007/2010 spreadsheet, NOT having code I can though, apparently, set (though turns out it hasn't been set) the project protection as I've said, but this has other implications... for one can still adjust some settings on the sheets properties in 2007/2010, even though ,in 2003, these are locked inside the password.

For example I can change the Enable Selection property from '1 - xlUnlockedCells' to '0 - xlNoRestrictions' or even '-4142 xlNoSelection' thus bypassing the protection which I thought I had applied. No warning is given that the project protection has NOT been applied.

This will have implications for spreadsheet code locked in Excel2003 where no code was actually written, as when the spreadsheet is opened in 2007 ( or 2010?) there will be no code protection present and access to the sheet properties will become unintentionally available.

OMG My head - just as well it's lunchtime.

 

[eti3nne]

... Futhermore if you save a 2007 spreadsheet with all your code temporarily commented out say , when you return you will find it gone.

Not only does the project protection fail silently but all your code is wiped too.

Done and done for the day. TGIF.

 

[SkipVought]

In my test, it wipes out comments with no code IN THE WORKSHEET OBJECT ONLY.

However, if I have a MODULE, with comments/no code, it preserves ALL COMMENTS in MODULES and OBJECTS.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[eti3nne]

Skip,

Yes thanks for your usual precision in pointing that out. I was less than explicit in my haste to post before leaving the office.

Is this a well-known 'feature'? As I said in my original post we've only just today had Office 2007 installed (as a replacemet for 2003). I could't find any references to this when searching this am.

If this sort of 'feature' silently lurks within Excel 2007, I'm not looking forward to Access 2007!