How do I remove all the macros from my workbook? I don’t want the “this workbook contains macros prompt” to appear anymore?

There are three things you can do to get rid of the macros message when you open the workbook.

  1. Set the macro security setting to low.
  2. Move all of your worksheets to another workbook.
  3. Manually delete the VBA modules.

The first option will not work in many corporate settings where the IT department forces the security settings to Medium. If your IT department does not set this for you, you can change the setting to low by going to tools on the menu, and then macro=>security.

The easiest option for those unfamiliar with VBA, is option two. To do this, unhide all sheets. Select the first sheet and while holding down shift select the last sheet.  Right click on one of the sheet tabs and choose move or copy. In the “to book” drop-down, select new book. This will put all of your worksheets in a new workbook that does not contain the macros.

The last option is to delete the macros from the workbook manually. Press alt+f11 to go the VB Editor. Press ctrl-r to show the project explorer on the left side. Find VBAProject(your workbook name) and select the first item called “module” that you see. Select file from the menu and then remove module. Select no, do not export module. Repeat this for every module listed. There could be VBA contained on the individual worksheets as well, to remove it, select sheet 1 in project explorer and delete any VBA you see. Continue this through all your sheets.

Joseph Harris
Analyst Pulpit

Related posts:

  1. Duplicates – Finding and Removing in Excel There are many occasions when you may need to remove...
  2. Removing Characters from a String in Excel It is often necessary to replace characters within a string...
  3. User Passwords in Excel – Multiple Passwords Simple multiple user password protection in Excel....
  4. Playing with VBA in Excel, Without Knowing VBA! Introduction to user defined functions and VBA in Excel....
  5. Referencing Data in Another Excel Workbook Q: Joe, I have data in another workbook that I...

This entry was posted on Wednesday, November 12th, 2008 at 11:31 am and is filed under Excel Hints, Mail Bag. You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.

One Response to “Removing Macros from a Workbook”

  1. Jon Peltier on November 12th, 2008 at 4:32 pm

    Note that if there is code in the modules behind the workbook or worksheets, moving the sheets will retain the code behind the sheets. This code will have to be manually deleted.

Leave a Reply

You must be logged in to post a comment.