Disable VBA Immediate Window

Question

Is there a way to disable the VBA "Immediate" window?  I can hide and/or protect almost everything in my spreadsheet, but anybody can still go into the VBA Editor, select the project and, even though it is locked for viewing (using a password), they can then find and display every hidden worksheet (even those set to xlVeryHidden), as well as all their formulas and values.

I can prevent users from seeing the formulas (by setting all cell attributes to "Hidden", then password-protecting the worksheet), but I can't prevent them from accessing the values, which allows them to re-create the layout of the hidden sheet, which in turn provides clues to the calculations.

Furthermore, as soon as I protect the worksheet, its name shows up in a list in the Permissions section of the Backstage view (under "File | Info"), making it even easier for the user to find and access information that I would prefer to hide.

This seems to be a gaping hole in Excel's security, and a puzzling one because it seems like it should be easy to fix.  For example, why can't the Immediate window refuse to return any information related to a "very hidden" worksheet?  It already returns an error if you try to access the formula in a cell whose attribute is "Hidden".  Better still, why can't it refuse to return any information at all if the project is locked for viewing?

If there is no way to disable the Immediate window, any other workaround suggestions would be much appreciated.

Thanks!

HJ

P.S.  Not sure if it makes a difference, but I'm using Excel 2010 under Windows 7.

Answer 1

I was hoping there would be a faster and easier solution, but I wasn't optimistic that would be the case.  In the long run, however, I think the COM add-in is probably the way to go.....

Thanks for your help, Bernie.

Regards,

Hugh John

Answer 2

If information security is that much of an issue, you can use a compiled COM add-in that does all your proprietary calculations:

http://www.cpearson.com/excel/creatingcomaddin.aspx

Or roll your own application that does what you need without allowing that kind of access.