Categories
Excel

No Macro but Enable Macros Prompt Shows Everytime

Excel remembers that you recorded a macro in the workbook. Unfortunately, Excel keeps asking you to enable macros when you reopen the workbook even though you have deleted all the macros. There are a few things you can do to fix this issue.

When you record a macro, Excel inserts a Visual Basic module to hold your commands and home-brewed functions. Upon opening a workbook, Excel checks for the presence of modules, whether empty or macro-filled.

Deleting a workbook’s macros deletes any code within the module, not the module itself. To avoid the unnecessary macro prompt, you need to remove the module. Here’s how to do that.

Project Explorer modules with Modules folder expanded
  1. Open the VBE by pressing Alt+F11 (or Option+F11)
  2. Select View » Project Explorer (if the Projects window is not opened).
  3. Find your workbook in the Project Explorer
  4. Click the plus sign (+) to its left to expose the workbook’s component parts
  5. Click the plus sign to the left of the Modules folder to list any modules on board.
  6. Right-click each module in turn and choose Remove Module from the context menu. Decline the offer to export the modules.
  7. Press Alt+Q to get back to Excel’s spreadsheet view.
  8. Save your workbook.

Now, close and reopen the workbook, you’ll not see any prompt asking you to enable macros.

How to Disable “Enable Macro” Prompt for all Workbooks

You’ll be prompted to enable macros only if your macros setting is set to “Disable VBA macro with notification”.

If it’s set to “Enable VBA macro…”, macros are enabled without a peep.

if it’s set to “Disable VBA macro without notification”, macros are disabled automatically for your protection.

You can disable the macro security settings in Excel, but this is not recommended as it can expose your computer to potential risks from malicious macros.

  1. Click Home > Options.
  2. Click Trust Center > Trust Center Settings.
  3. Click Macro Settings and choose “Enable VBA Macros”

Now all workbooks containing macros will open without showing any prompt.

Understanding Workbooks and Worksheets: