Forum Discussion
how to prevent excel workbook from opening if macros are disabled
hi all, I have a workbook loaded with formulas that I don't want to have available if the vba code behind them isn't operational does anyone know a way to secure excel when vba is not running due...
how is your code sample expected to run if macros are disabled? Also, if you read my question, I'm trying to remove access to the formulas in the workbook when the macros aren't running. All of your recommendations are on how to get macros running which is the opposite of what I'm asking.
You can't prevent the workbook from being opened if macros are disabled, but you can do the following:
- Add a worksheet that only contains a text such as "This workbook cannot be used if macros are disabled".
- Hide all worksheets except that new one when the workbook is saved, using the Workbook_BeforeSave event procedure. Set their Visible property to xlSheetVeryHidden. this means that they cannot be unhidden from the Excel interface.
- Unhide the hidden sheets when the workbook is opened, in the Workbook_Open event procedure. This will only run if macros are enabled.
- In the Visual Basic Editor, protect the VBA project with a password.
As a result, only the new sheet will be visible if macros are disabled.
Warning: knowledgeable users will be able to circumvent this, but it should be sufficient to protect against accidental misuse.
- Hello Hans,
I have one issue I am getting as when I copy this file from one server location to my local location the macro linked with the button gets disconnected. can you have some information about this?
Thanks in advance - Thanks Hans, all good stuff, but I wanted to prevent people from using individual sheet formulas when code (macros) are not running. I have worked out a solution, just wanted to say thanks for contributing.
