Unable to lock project in Excel VBA

[DeFacto]

I'm having a curious problem I'm hoping someone can help me with...

When I was using Excel 2003, I would often 'lock' my VBA projects when sharing workbooks with sensitive information... it was especially helpful when I had information on a Very Hidden tab that I didn't want anyone to know about or try to 'Unhide'. However, ever since I've been using Excel 2007 and 2010 I have been unable to lock any projects prior to sharing or distributing. Here are the steps I've been using to try to lock my projects in Excel 2007 & 2010: In the VBA window I highlight the workbook/project I'm interested in locking. I click on 'Tools > VBAProject Properties...' and click on the 'Protection' tab. I mark the box called 'Lock project for viewing' and then provide a password and confirm password. Once done, I either save the workbook from the VBA window or I navigate back to Excel and save the workbook from there. Either way, when I re-open the workbook it's as if I never protected (or tried to protect) the workbook/project.

I'm totally baffled and I've messed around with several workbooks over the past year-and-a-half trying to get this to work. It's not possible this is still a bug Microsoft hasn't fixed in Excel 2007 or 2010 yet, is it? Any help or experience anyone has to offer would be greatly appreciated... I've been pulling my hair out for a long time.

 

[shg]

I believe in Excel 2007+, you need to have some code -- any code -- in the workbook in order to protect the project. E.g., in ThisWorkbook,

Sub MySub()
End Sub

 

[DeFacto]

Thanks shg4421,

It worked... many thanks!

However, do you if there is a way to accomplish this without having to save the file as a Macro-enabled workbook (.xlsm)?

Thanks again!

 

[shg]

You can't save a macro in anything other than an macro-enabled workbook, so I think not.

 

[DeFacto]

Thanks again, shg4421... your responses are most appreciated!

 

[alexbw]

I believe in Excel 2007+, you need to have some code -- any code -- in the workbook in order to protect the project. E.g., in ThisWorkbook,

Sub MySub()
End Sub

after 11 years your answer save me
Thanks