In the old days, say, from Excel versions 97 through 2003, when you opened a workbook containing VBA code, you received a big ugly modal dialog box with the macro warning. You had to click a button on the dialog in order to continue. The dialog was annoying, but it was a reminder to allow the code to run.
In Excel 2007, if the Visual Basic window is open, you still get this dialog. In fact, the dialog shown above is the Excel 2007 version. However, if the VB window is not open, you don’t get the dialog. Instead you get a notice embedded in the UI. It’s a non-modal message, with no beep, and it doesn’t make you respond to it.
See the notice, tucked away between the ribbon and the formula bar? I didn’t think so. Half the time I don’t notice it, and I run around wondering what’s wrong.
I can’t count how often I’ve loaded a workbook with macros, and not known why they don’t work. Or at least I’m annoyed by the next message.
Excel 2010 works like 2007. If the VB editor is open, you get the blatant dialog that forces you to respond. If the VB editor is closed, you still get the sedate little notice in the UI, but it’s no longer camouflaged to blend in with the ribbon.
See it there, between the ribbon and the formula bar? Think you could possibly miss it? Good.
This is one bit of gaudy formatting that I welcome. I always see the notice immediately every time it appears.
Mathias says
Nice! Or should I rather say, not that nice, but does the job! A good UI should first and foremost tell you unambiguously what you are expected to do, this definitely will do the trick. I have been stuck the same way you describe, which is rather amazing because the size of that message was already huge…
Mike Alexander says
Jon: I’ve got big hope for macro security in 2010. Apparently it will be much better.
In 2007, the macro security has been such a drag that I’ve basically made my entire computer a trusted site. I’ll take my chances.
I just don’t open any Excel games from Russia.
Jayson says
I would have toned down the color a bit and maybe not chosen red. Thanks Microsoft for making my files look like nuclear bombs.
Jon Peltier says
Jayson – I’m sure there’s a happy medium, but I’d rather see an important notice it because it’s gaudy than miss it because it blends in too well.
Bob says
Hi Jon,
Agree with you there. I’ve built some reporting tools for the company that rely on macros and sharepoint. If the user doesn’t see the warning, I get a phone call… Your tool doesn’t work…
People notice red. Stop signs, traffic lights… Sooner or later, when we upgrade, they will see the alert.
Do you know if the red warning is also activated for external data sources, like sharepoint lists?
Cheers,
Bob
Jon Peltier says
Bob –
I haven’t explored the extent of the new warning. Every time I am tripped up by the gray hidden warning in 2007 (a couple times per session, it seems), I appreciate 2010’s red message.
Robert Martim says
Jon
Yep, missing the “Message Bar” was an easy job and happened more often than otherwise. But I actually prefer that more intrusive message box. I never missed that one. :o)
Thanks for sharing your thoughts on the new format of the Message Bar.
masqu3rade says
I agree with the ‘missing it’ sentiment and it drives me crazy. so my question is: Is there anyway to bring back the old box or make the ribbon in 2007 bright red?
I could do it with VBA… oh wait…
Jon Peltier says
Masqu3rade –
I know of no feasible way to color the security warning in 2007 so it is more noticeable. I wish it would always pop up a dialog, but in 2007 the dialog only appears when the VB editor is open. That’s most of the time for me, but of the 20% of the time when the VBE is closed, I miss 50% of the warnings.
The other shortcoming of the dialog warnings in 2007 is that, when you do get the dialog, clicking the red X does not cancel opening of the file as it used to do. The file still opens, but with macros disabled.
Duke says
How do I ensure that the VBE is open?
Jon Peltier says
Duke –
The following procedure will open the VB editor, then minimize it. There will be a very slight flicker as the window opens and minimizes itself.
Mark says
Old Post but relevant. The message bar isn’t red on my home PC but if it’s accepted once or maybe ignored once the next time you open the file it doesn’t prompt you but then you can’t enable the macros. Can you force the dialogue ribbon to appear after you start working on the file?
Jon Peltier says
Mark –
If the macro bar isn’t red, you must be using 2007. 2010 leaves the message for a while, so you can enable macros even after working with the file. 2007 removes the message as soon as you do anything else. Grrr.
Mark says
Thanks
Have you any idea how you can re-show the macro warning “ribbon” again once it’s hidden, haven’t dug too deeply as there are so many other changes I’m looking to catch up with!
It’s definitely 2010 & you must have a different colour scheme as mine is certainly a pail yellow or is it a bright buff?
More annoying in compatibility mode… Pivot tables just lock as Data & Complex cond formatting forces file to open as non formatted!
Think some are deliberate bugs to force us to go fully 2010 & thence our coleagues & partners to do the same!
Jon Peltier says
Mark –
You’re right, the macro security warning is an amber bar. This article was written during the Office 2010 Beta, and apparently some User Interface Aesthetics Engineer decided red was too harsh.
It still is better than in Excel 2007.
Another nice thing is that even after dismissing the bar (by clicking on the X at the right of the bar) you can go to the File tab and enable the file.
Guess I’ll be rewriting this post soon.
Mark says
Many Thanks Jon
just what I wanted, couldn’t see it for looking, you don’t even have to minimise it as it dissapears of its volition sometimes! At work I am running all 3 platforms depending on which provider I’m working for at the time 98/2003, 2007 & 2010 so my head is all over the place. Some files have to cross work & compatibility is an issue with Pivots, cond formatting & command buttons but I guess that another set of blogs.
thanks again
Mark
Jon Peltier says
Mark –
I discovered that link while I was responding to your comment. Good to know it’s there.
Rodh says
I have a macro procedure too long, how can I break in two parts or two modules using the same macro.
Jon Peltier says
How do you know the macro is too long? (There are lots of ways to tell, I’m wondering what told you.)
In any case, you can often break up a long procedure into sections. For example, if your chart carries out calculations, builds a chart, and formats the chart, you could break the macro into three smaller macros, named for example CalculateChartData, BuildTheChart, and FormatTheChart. You then make a master program that calls all three sections:
If you do it cleverly, you can call the individual smaller macros, for example, to build a chart from existing data, or to format a chart that’s already been made.