how to prevent excel workbook from opening if macros are disabled

[zorgnum]

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 to macros being disabled?

[#2]

@zorgnum 

To prevent an Excel workbook from opening or functioning properly when macros are disabled, you can implement the following security measures:

1. Password Protection: You can protect your VBA code with a password. This way, even if someone opens the workbook with macros disabled, they will not be able to view or modify the code without entering the correct password.
• In the Visual Basic for Applications (VBA) editor, go to "Tools" > "VBAProject Properties."
• In the "Protection" tab, check the "Lock project for viewing" box and enter a password.
• Save your workbook.
2. Check Macro Status: Within your VBA code, you can add checks to ensure that macros are enabled. If macros are disabled, you can display a message and exit the application.

Vba code:

Sub Workbook_Open()
    If Application.AutomationSecurity = msoAutomationSecurityLow Then
        MsgBox "Macros are disabled. Enable macros to use this workbook.", vbExclamation
        ThisWorkbook.Close SaveChanges:=False
    End If
End Sub

This code checks the Application.AutomationSecurity property to see if macros are enabled. If not, it displays a message and closes the workbook without saving any changes.

1. Digital Signatures: You can digitally sign your Excel workbook using a code-signing certificate. When someone opens the workbook, they will be able to see that it has been signed, which can provide reassurance that the macros are safe to enable.
2. Trust Center Settings: Educate users on how to adjust their Excel Trust Center settings. They can choose to "Enable all macros" or "Enable macros with notification" depending on their level of trust in the workbook's source.
3. Use Protected View: Users can open potentially unsafe documents in "Protected View," which is a read-only mode. They can then choose to enable editing or content if they trust the source.
4. Password-Protect Sheets: You can also password-protect specific worksheets or workbook structure to prevent users from accessing certain content without enabling macros. This will not prevent the entire workbook from opening, but it adds another layer of security.

Remember that while these measures can help protect your workbook's content, determined users may still find ways to access the data. Therefore, it is essential to strike a balance between security and usability based on your specific needs and the level of protection required for your Excel workbook. The text and steps were edited with the help of AI.

My answers are voluntary and without guarantee!

Hope this will help you.

Was the answer useful? Mark them as helpful and like it!

This will help all forum participants.

[#3]

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.

[#4]

@zorgnum 

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.

[#5]

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.