The use of macros in Excel is made possible by the VBA programming language and we tend to avail ourselves of it often in our workbooks. We do this because we want to do things within our worksheets quickly and efficiently. Every time we create one of these little gems, Excel adds what we call a module to your workbook and this is what is used to store the macros that you create and record.
Unfortunately, macros can also be a big and I bring this up because every time you open a workbook that has macros, Excel will dutifully ask you if you want to enable macros (even if you have previously deleted all the macros in your workbook).
Seems kind of silly doesn’t it? But the reason this occurs is that even though you have told Excel to delete your macros, the module that housed your macros, is not automatically deleted when you delete your last macro. And, as long as the module exists, the security feature in Excel is going to continue to ask you if you want to enable macros each and every time you open your workbook.
Follow the steps below to overcome this issue and rid yourself of that security prompt for your workbook:
Click Alt + F11 to display your Visual Basic Editor.
In the upper left-hand side of the Editor, you will see the Project Explorer. This will contain a hierarchical tree that displays the different modules in your workbook.
If you do not see the Project Explorer, click CTRL + R to make it visible.
In the Project Explorer you should see a folder called Modules. If it is not already open, double-click it to display its contents.
Right-click a module folder and a Context menu will be displayed.
Select the Remove option from the Context menu.
You will be asked if you want to export the module before removing it.
Select No and the module will be removed.
Repeat the above steps for each module in the Modules folder.
Close the Visual Basic Editor.
Save your workbook.
Your workbook has now been rid of any modules and you will hence no longer receive the annoying little security message when you open it!