How do you run macros in Excel 2007?

[Oppressed1]

Yes, I'm serious, the thread title is a verbatim expression of my query. It is a testament to the severe mental retardation - did I stutter? - of the 2007 and later Office Team at MS, that someone who is probably a 95th percentile performance user must ask this. This and the numerous and serious 2007+ interface failures are why I will likely change from a 50-hour-per-week Excel god and guru to 0.0 when 2003 is forcibly taken from me at the workplace. But for this week, this project, I wish I could get to use some code that I spent months developing.

The message:
"Because of your security settings, macros have been disabled. To run macros, you need to reopen this workbook, and then choose to enable macros. For more information about enabling macros, click Help." *(footnote)

In 2003 you simply go tools/macro/security and pick one of 4 fairly straightforward choices. It's very difficult to get it wrong.

Now in 2007 I cannot get a macro to run. From opening a .xls made in 2003, I go alt-F8 and try to run one. It says, "No, you can't; hit help for help." Naturally help is broken as it has been since - what - 1997? (Highlight the word SUM and hit alt-H or F1. Or SUMIF. I'm not kidding. You must go to Google to get help on Excel - at least in my standard configuration. Must. Anyway...)

So off to Google, and off to visit other users, where there are a dozen theories on how to do it. The leading answer appears to be "Go to the Trust Center and set options there." As astonishing as it would be if this simple option was that obfuscated, I tried anyway. And the answer there is so shocking that I just can't believe it:

Once I navigated the counterintuitive path to macro settings, it appears to give you 4 choices:
- Run everything, period. (The suicide option)
- Run nothing. Ever.
- Run nothing. Ever. Oh, but you get a cool useless message box! (Why? What does that accomplish?)
- Set up digital signatures (Oh no you di'n't. Quit f***ing around, MS. I just want to run my macro.)

So How do you run macros in Excel 2007?
Just for myself. I just want to run my own macros. What - minimally - do I click?
[short of the suicide option]


* By the way, why in the ganja-glazed haze of Redmond offices would I reopen a workbook that I just, just opened?

[MarvinP]

Hi Opressed1,

In the early days of Windows, Office and VBA you could AutoRun a macro when a file opened. If you've heard of the Minerva Virus, it was one of those.

In early versions of Office, Microsoft trusted all good programmers to be good and not screw things up. That was found to be a false assumption.

In more current versions of Office, VBA macros don't run unless you are sure they aren't going to screw you. This is why VBA and Excel got better.

If you don't like the current scheme for protecting yourself against viruses then get ready to get infected.

I'm sorry you don't like the extra questions when you open an .xls or .xlsm but believe me they are important.

[Oppressed1]

So each and every time I want to run a macro, I have to navigate to "run all macros with no protection," run the macro, and then when I've run it, navigate to "don't run any macros ever?" That sounds simply absurd.

Thanks for the attempted defense of MS's action, but frankly each and every one of your concerns was addressed well before even the straightforward 4 choices in 2003, and in 2003 itself which wasn't broken. Just "prompt user whether to run." What's the issue with that (Other than MS knows what the user wants better than the user does)? You don't have to try to defend that. Just tell me, short of digsigs how to prompt-to-allow.

And frankly if admins still have security concerns even then, presuming the users are too incompetent to practice safe hex, cripple it at the worksite. But I should be able to run macros at my own home without either enabling all macros always (suicide) or developing digital signatures. (What percentage of users use digsigs?).

[MarvinP]

You only need to accept the "watch out this file has macros" when you open it.

I think there are "safe site" settings in some browsers that will allow them to get into your home network and or you can run macros automatically..

In later versions of Excel you needed to get a Security Tested tag for your work. See if the attached gives you a prompt to allow macros.

[benishiryo]

hi there. i'm not using my Excel 2007 right now, but it's similar to Excel 2010. so if what i mention is different, there should be something close. explore around. you should be using:
Disable all macros with notification
so that always notifies you if you want to enable macros. if you want to run it at home using the same file every time and without needing to enable them, go to the Windows icon -> Options -> Trust Center -> Trust Center Settings -> Trusted Locations -> Add New Location. so you can add that particular folder you trust. avoid putting a general C drive though, unless you are sure everything inside is something you can trust.

[RozieTK]

have you tried saving the file as an xlsm (macro enabled) ?

[Oppressed1]

you should be using:
Disable all macros with notification so that always notifies you if you want to enable macros.

I am. When I open the file I'm asked nothing, the file just opens. When I attempt to run a macro it says no.

if you want to run it at home using the same file every time and without needing to enable them, go to the Windows icon -> Options -> Trust Center -> Trust Center Settings -> Trusted Locations -> Add New Location. so you can add that particular folder you trust. avoid putting a general C drive though, unless you are sure everything inside is something you can trust.

Thank you for that practical answer, but I mean, really, whether a macro will run will depend on which folder I place it, unless I use the suicide option? Good God. I ought to be able to answer a prompt upon opening: yes, I want to run macros for this file. If moving to the "happy folder" works, at least that's something so thanks for that. (By folder, that's plus subtree, I gather? The "general C" warning sounds like it means root and below for that warned example.)

[Oppressed1]

You only need to accept the "watch out this file has macros" when you open it.

Marvin I get nothing when I open your text.xls or my .xls with macros.

I think there are "safe site" settings in some browsers that will allow them to get into your home network and or you can run macros automatically.

I'm open to recommended practice with Firefox

In later versions of Excel you needed to get a Security Tested tag for your work. See if the attached gives you a prompt to allow macros.

The messagebox in your test file did not fire, even when activating a different workbook and reactivating it. There was no notification either, at any time - as far as I could see some workbook just opened up with a blank sheet or two. However I appreciate your consideration in sticking with this and providing that.

Perhaps there's a configuration setting or issue in play. I'm told by I.T. that I'm vanilla as can be though. In that light, I note that I'm also not being asked whether to update links the way 2003 did when the linked files were not already open. I've heard that for 2007 a marvelously arcane trust center setting approach is recommended for that, but Google again shows numerous theories on how to get this pathetically simple task done. Jeeeeeeezz....

[Izandol]

- Run nothing. Ever. Oh, but you get a cool useless message box! (Why? What does that accomplish?)

This setting should show a message bar (not the old-fashioned pop up message box, unless you have the VB Editor open) below the formula bar, with an option to enable macros.

[Andy Pope]

There is also a setting within Trust Center where the message bar can be disabled, so you better check that as well.

File links are in the Options > Advanced > General section.


Did you I.T. also apply all the service packs and updates to your install?
Personally I would have upgraded to xl2010

[Oppressed1]

This setting should show a message bar (not the old-fashioned pop up message box, unless you have the VB Editor open) below the formula bar, with an option to enable macros.

I'll have to go down Andy's path, because I just get OK and [useless]Help buttons. It does sound like configuration. I'll have to get into that tonight.

[Oppressed1]

Message bar setting in trust center says "Show the message bar in all applications where content has been blocked." What does this message bar look like? Top or bottom of screen? Here's what I getxl07-bs.JPG

[Oppressed1]

Hmmm, now I do see something new, above the formula bar, nicely blending into the screen-hogging silly big icon section atop the window. I hadn't seen it before because it disappears when you go alt-F8. Is that the macro bar?

So it appears that I do get to answer a prompt, and I owe the Office developers a little prayer of gratitude for making the prompt experience both easier and definitely more confusing (even after I buy into the post-2003 paradigm). Personally I WANT a message box for me and for anyone who uses my workbooks, but they didn't ask me in between bong hits. Okay, then I'll consciously look for the "macro bar" every time I open a workbook for the rest of my life.

At least you all gave me polite and constructive advice. I am able to now run macros, as long as I look for and click on the "macro status bar" first.

Oh, one more thing I've discovered while writing this, not just for me, but for anyone to run a macro in 2007 - and maybe 2010, and maybe forever, thank you Jensen you fool - that status bar (if I'm calling it correctly) disappears after, I'm not sure, but maybe, a minute or two. That's right. It just disappears. Thank you, Microsoft, you clueless imbeciles; message boxes don't do that. If you go get a coffee after starting to load a slow loading workbook, you miss your window to act. (Should I bother to ask if you can go view/toolbars/macro status bar to recover it? Or is that too logical and productive? Sorry, can't help myself in the fact of such utter massive incompetence.)

I just tried that again. Maybe 2 minutes after opening, POP - it disappears. I know it's insane that I would have to make it reappear, but is there a way, short of reloading the workbook? I can (but Jensen couldn't) easily see ignoring the bar at first, using the workbook, then deciding, oh, I want to run a macro 5 minutes later. Can you?

[Oppressed1]

I don't think anyone mentioned it, but a seemingly more intuitive route to the "Disable all macros with notification" (which I've had set all along BTW) looks like
Developer tab
Code pane
Macro Security
Macro settings
Click the choice, or alt-d
All this being the 2007 counterpart to alt-T-M-S-M for medium security in 2003.
BTW alt-T-M-S is one of the shortcuts that still does what it did before.

I still have questions if anyone can survive past my ranting in the prior post. Please understand me: I love spreadsheets, and I may be one of if not the fastest 2003 user on the planet, at least for numerous actions. It's just very upsetting that my speed (and all of the FAST users that I know - I'm talking the uberfast users) - is crushed in '07+. It was all so, so, unnecessary. I rant in hoping to humiliate the transgressors and in some small hope effect change. Anyway, I understand that this may offend some and they may not read any further. But what I'm wondering is, can you get the "macro security bar" to reappear after it disappears (whether by going alt-F8, or just disappearing on its own), short of closing and reopening the workbook? Thanks.

[Izandol]

I do not think so. This behaviour is fixed from 2010. 2007 is not good version, I am afraid.

[Oppressed1]

I do not think so. This behaviour is fixed from 2010. 2007 is not good version, I am afraid.

Sounds like many of you are on 2010 and I can do that. I try to spend some time on each version so I don't blindly overengineer by taking advantage of newer version features but shutting out those who are still on e.g. 2007 or 2003. (That's not to say I reject using mucho sort columns and conditional formats and such; I just don't want to do so "blindly" )

[Richard Buttrey]

I do not think so. This behaviour is fixed from 2010. 2007 is not good version, I am afraid.

Not sure I'd entirely agree with you there

[Oppressed1]

Interesting diplomatic retort. Just expressing my perspective, it's all about the interface. A million lines is swell; expanded conditional formatting features are swell; but I'd give up all of that a thousand times over for a productive interface. I haven't heard anyone say that the 2010 *interface* was worse, while some say that it erased a tiny bit of the 2007 interface screwups. At least that's my essentially anecdotal impression from lurking around.

BTW now I understand why MS says to reopen the workbook in order to run macros, even if I opened it 15 seconds earlier - because in order for me to see the "macros are disabled" message, that means I didn't enable it, and if I'm seeing this messagebox, the macro enable bar is gone and can't be restored. Still wholly lame, but I understand their message text choice now.

[swoop99]

Best. Thread. Ever.

[Izandol]

Not sure I'd entirely agree with you there

You do not have to. Everything is opinion.

I do not know anyone who will recommend 2007 and have seen many times it has been described as Office Vista - I do not think this is complimentary. For sure, without at least Service Pack 2 in place, 2007 is terrible. In my opinion.