Improved Macro Security Warning in Excel 2010
by Jon Peltier
Friday, August 14th, 2009
Peltier Technical Services, Inc., Copyright © 2012.
Licensed under a Creative Commons Attribution-Noncommercial-Share Alike 3.0 Unported License.
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.
Related Posts:
- How To: Use Someone Else’s Macro
- How To: Assign a Macro to a Toolbar or Menu
- How To: Record Your Own Macro
- Installing an Add-In in Excel 2007
- VB Editor Settings
- Adding Excel Chart Data
Posted: Friday, August 14th, 2009 under Excel 2010.
Comments: 18
Comments
Comment from Mathias
Time: Friday, August 14, 2009, 1:06 am
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…
Pingback from Makrowarnung in Excel 2010 « Excel-Inside News-Ticker
Time: Friday, August 14, 2009, 1:37 am
[...] Hier gehts direkt zum Beitrag. [...]
Comment from Mike Alexander
Time: Friday, August 14, 2009, 2:18 pm
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.
Comment from Jayson
Time: Monday, August 17, 2009, 1:23 pm
I would have toned down the color a bit and maybe not chosen red. Thanks Microsoft for making my files look like nuclear bombs.
Comment from Jon Peltier
Time: Monday, August 17, 2009, 3:51 pm
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.
Comment from Bob
Time: Friday, August 28, 2009, 5:10 pm
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
Comment from Jon Peltier
Time: Friday, August 28, 2009, 10:40 pm
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.
Comment from Robert Martim
Time: Sunday, September 6, 2009, 11:18 am
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.
Comment from masqu3rade
Time: Monday, May 24, 2010, 8:20 pm
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…
Comment from Jon Peltier
Time: Tuesday, May 25, 2010, 7:25 am
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.
Comment from Duke
Time: Wednesday, June 2, 2010, 11:21 am
How do I ensure that the VBE is open?
Comment from Jon Peltier
Time: Wednesday, June 2, 2010, 9:04 pm
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.
Sub Open_VBE()
With Application.VBE.MainWindow
.Visible = True
.WindowState = 1 ' vbext_ws_Minimize
End With
End Sub
Comment from Mark
Time: Wednesday, March 28, 2012, 2:11 pm
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?
Comment from Jon Peltier
Time: Wednesday, March 28, 2012, 6:46 pm
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.
Comment from Mark
Time: Thursday, March 29, 2012, 1:03 am
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!
Comment from Jon Peltier
Time: Thursday, March 29, 2012, 8:20 am
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.
Comment from Mark
Time: Saturday, March 31, 2012, 12:39 am
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
Comment from Jon Peltier
Time: Sunday, April 1, 2012, 9:20 pm
Mark -
I discovered that link while I was responding to your comment. Good to know it’s there.
Write a comment
I welcome comments from my readers. If you have an opinion on this post, if you have a question or if there is anything to add, I want to hear from you. Whether you agree or disagree, please join the discussion.
If you want to include an image in your comment, post it on your own site or on one of the many free image sharing sites, and include a link in your comment. I'll download your image and insert the necessary html to display the image inline.
Read the PTS Blog Comment Policy.