Improved Macro Security Warning in Excel 2010
by Jon Peltier
Friday, August 14th, 2009
Peltier Technical Services, Inc., Copyright © 2010.
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
- VB Editor Settings
- Installing an Add-In in Excel 2007
- Adding Excel Chart Data
- Blue Ribbon Interface
- Using RefEdit Controls in Excel Dialogs
- Installing an Excel Add-In
- Build an Excel Add-In 4 – Create the Dialog
Posted: Friday, August 14th, 2009 under Excel 2010.
Comments: 12
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
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.