Expert Zone
XLSX + XLS repair tool

Removing the Macro Security Warning

By

Nick Hodge

Many times after macros have been recorded or written users want to remove them as they are no longer required, but they cannot get rid of the 'macro warning' pop-up that appears each time the workbook is open, (see below). (You should only follow these instructions if you have no further need for the macros or UserForms!) .

Excel has the ability to put code in three places, (see here). If the code is in a standard module the key to stopping the dialog is to remove the entire module(s), not simply delete the code in it. To do this:

• Open the VBE (Alt+F11)

• In the project explorer look for the name of the workbook. Below this you should see a folder called 'Modules'. Right-click on any modules below this folder and select 'Remove [Name of module]...'

• Unless you specifically want to save the code contained within the module, answer 'no' to the dialog offering export of the module

• Once this is done with all modules, you can close the VBE and save the workbook.

• You may also have a folder called 'Forms'. If this is the case follow the same instructions as above on any forms contained in this folder.

• Close and re-open and all should be well

Unless...

You may also have code written behind 'objects' in the workbook providing 'event' code. (see here), or possibly UserForm(s) with controls (buttons, combo boxes, etc), that can also contain code. If this is the case, you simply need to delete the code as you cannot delete the module(s) that contains them. To do this:

• Open the VBE (Alt+F11)

• In the project explorer, locate the name of the workbook and below this you will find the objects in the workbook, (e.g. Sheet1, Sheet2, ThisWorkbook, etc).

• Right-click on each object in turn and select 'View code'

• A window will open. Any code lines in here should be highlighted and deleted.

This should be a route to eliminating 99% of areas where code may hide and trigger the macro warning.