Enabling Macros In Excel Spreadsheets

Since I get a fair amount of readers downloading my spreadsheets and asking, "How do I get the macros to work?" I decided to write a quick tutorial on just that topic.

"Macros," where Excel is concerned, are simply sets of instructions that tell Excel to automate some process or processes. In my case, I love to use macros to sort and/or clear out spreadsheet data (with a single click!) and to perform a variety of similar mundane duties. Macros can be huge time- and effort-savers, but because they could conceivably be programmed to do pretty much anything to the computer they're running on — say, wipe out the hard drive, or even something worse — we have to be very careful with the Excel macros we run. You always want to verify a spreadsheet macro's source, and make sure you trust the soul who created the thing.

In any case, when you're ready to enable macros in your Excel program, you'll find that it's a snap.

Enabling Macros in Excel 2003 and Older

1. In your Excel menubar, click TOOLS —> MACRO —> SECURITY, as shown below.

This will open up a message box like the one shown below.

2. Select your macro Security Level: either "Medium" (recommended) or "Low" (not recommended).

Checking the radio button beside "Medium" (which is what I'd recommend) means Excel will prompt you for permission each time a macro tries to run. Checking the radio button beside "Low" (not something I'd encourage!) means Excel will have free reign to run any and all macros in the spreadsheets you open.

You might notice the words "Virus scanner(s) installed" in the bottom left corner of that screenshot. Having a good antivirus program running at all times is a great idea; having a good antivirus program running when you're using Excel macros is a must.

Still Not Working? There May Be an Excel Quirk at Work.

Complete the procedure above, then save and close your spreadsheet.
Close Excel completely.
Reopen Excel and your spreadsheet
Complete the procedure above once more.
Save and close Excel again.
When you open Excel the next time, your macros should be operating normally.

Yeah, it's odd, but I find that in Excel 2003, I usually have to change the security settings, save, and close Excel a couple of times before the changes take effect. It's annoying ... but that's life, right?

Enabling Macros in Excel 2007

1. In Excel, click the Office button in the upper left corner of the screen.

The "Office Button" is the fancy button with the colorful Office logo. Click it to open the menu shown in the next step.

2. Click the "Excel Options" button in the lower right.

3. Click the "Trust Center" button on the left. Then, at the bottom right, select "Trust Center Settings" as shown below.

The image above can be enlarged by clicking.

4. In the next window, select "Macro Settings," then select the radio button for "Disable all macros with notification."

5. To close the Trust Center window, click the lower right "OK" button.

6. Save and close Excel completely.

7. Reopen Excel and your spreadsheet (one with macros, anyway).

Now there should be a SECURITY WARNING notification beneath the Office ribbon, as shown in the next step.

8. In the SECURITY WARNING banner, click the "Options" button.

9. Select the radio box beside "Enable this content," then click "OK."

After you click "OK" and the window closes, your macros should function normally. Because you selected "Disable all macros with notification" in Step 4 above, you will need to follow Steps 8 and 9 each time you open a spreadsheet that contains macros (if you want the macros to run, that is).

Alternately, in Step 4 above, you could select "Enable all macros." This would allow any and all macros to run freely at all times. But I would not suggest it! Don't do this unless you are ABSOLUTELY SURE you won't be downloading or using unsafe spreadsheets (and by extension, macros) from ANY source! Malicious macros and Active-X controls can do serious damage to your computer and/or network!

If you need more help with your security settings and macro operation, just drop me a line.