Many of us have workbooks that we have to work on that we did not originally create. Sometimes we come across one where the original creator used macros that either no longer work as they should or thatno longer exist. But you are still plagued with the error that says: Enable/Disable Macros every time you open the workbook.
The reason this happens is that although the macro may have been deleted, the modules in which the macros were originally stored have not. So every time you open that workbook, MS Excel will check to see if it contains modules as it does with every workbook you open. If it checks and they there are empty modules, MS Excel will assume that the workbook contains macros and will prompt you to either enable or disable them.
To prevent this error from popping up in workbooks that have no macros, follow the steps below:
- Click Alt + F11 to open the Visual Basic Editor (VBE).
- On the left-hand side you should see the Project Explorer pane, with a tree structure similar to that of Windows Explorer, that contains your open workbooks. These are shown as VBEProjects.
- If the Project Explorer is not visible, click on CTRL + R to display it.
- If the Modules folder for your workbook has not be expanded, click the plus sign (+) to expand it.
- Right-click each module object in the Modules folder and select Remove Module.
- When you are asked if you want to Export the Module, select No.
- Close the VBE window and return to Excel.
- Save and close your workbook.
When next you open that workbook, the Enable macro message should not appear.