Well, guess what, you can’t. Microsoft Office 2007. A brand new way to create frustration in your life.
I was trying to create a macro in Microsoft Excel 2007 and nearly tore out my hair. The thing with macros in Excel 2003 was that it was relatively easy to accomplish, but it’s a whole different ballgame in Excel 2007. Here’s an account of what I did, only to discover that there’s no sane way to have macros run in Excel 2007.
So firstly, my setup: I’m using Excel 2007, and I’m on Windows XP SP 2.
I created an Excel workbook, and thinking that newer is better (Oh so wrong), I chose to create it under the new Excel 2007 format. Then I wrote my basic macro in that workbook, tested it, and all was fine. However, the workbook contained sensitive information, so I wanted to password-protect it. So I encrypted the file, choosing to have it prompt for a password before it would open.
Once that was done, I saved and closed the file as a macro-encrypted workbook in the trusted folder location that I supplied. Like any other normal person, I re-opened the file immediately to see if everything worked. I was actually almost 100% certain that nothing would go wrong. Hoho if only I knew.
I was surprised with a message in the message bar saying that the macro has been disabled. There was the Options button on the message bar, so I clicked that, and was faced with a prompt that said:
“This file contains macros that have been disabled because there is no antivirus software installed that can scan them. To run these macros, remove the encryption or permission restrictions on the file”
At this point, you’re given a comprehensive list of choices that allow you to resolve this issue in the most efficient manner possible. Well, actually, I wish. You’re just given a radio button list of ONE choice that says, “Help protect me from unknown content (recommended)”. Well it looks like it’s not only recommended, but it’s the only option.
So I meddled around, and lost about one year of my life in frustration and annoyance. My meddling led me to the Trust Center, where apparently I thought I had to put my macro in a trusted location. “Okay, for the sake of better security,” I thought. So I made these changes:
- Message bar: “Show Message Bar in all applications when content has been blocked”
- Macro settings: “Disable all macros with notification”
Looking at that, I had the impression that I should get some notification if Excel blocks something (Oh so wrong again).
I also figured that I should also add a trusted folder location in the Excel trust center. So I go add my current working directory to it. A little bit annoying, but if it means better security, I guess it’s okay right? (Oh so wrong – it doesn’t do crap).
When I opened the Excel workbook, I immediately noticed that macros were silently disabled. Remember how I set the Trust Centre settings above to show some notification? Well yeah, no notifications, nothing on the message bar at all, and I couldn’t find any way to enable macros.
Weird, I thought. So I used the Developer tab (some newfangled contraption only found in the fabulous Excel 2007), and tried to run the macro that I created. But, whoops! You’ll see this message: “Because of your security settings, macros have been disabled. To run macros, you need to reopen the workbook, and then choose to enable macros.”
Well, that’s weird, because I just opened the workbook and I was NOT given any choice to enable macros. So why was the message telling me to enable macros??
So, let’s summarise:
- If you try to run a macro-enabled Excel workbook (an xlsm file) in a location that is not trusted, you are told that macros have been disabled, and you can’t enable them.
- If you put that workbook in a trusted location, your macros are silently disabled.
WOW.
The conclusion then? In Excel 2007, you can’t have a workbook with usable macros if it’s encrypted with a password. The ONLY way that I found was to save the workbook under Excel 2003.
I hope this has saved somebody some grief and frustration.
Similar Posts:
- How To Open Microsoft Office Documents in VMWare Fusion By Default
- How To Install and Use Custom MP3 Ringtones For Android Phones
- How To Fix/Solve PHPLD v_AGREERULES Smarty Template Problem
- How To Resolve 500 Internal Server Error
- How To Print The List of Files in a Folder on the Mac
August 5th, 2009 at 10:46 pm
Did you try to sign the macro? Look up digital signatures.
August 8th, 2009 at 11:01 pm
Alvin, this absolutely can be done. E-mail me and I can send you a sample I just created of this in action.
August 8th, 2009 at 11:40 pm
Thanks guys, but I think it’s fine.. I just don’t want to delve into this whole thing again since it’s working well with the old Excel 2003 format. Appreciate the gesture though
August 12th, 2009 at 6:02 am
Avoid the antivirus scan from registry:
http://support.microsoft.com/kb/927150
August 14th, 2009 at 3:43 pm
Well you might not want to “delve into this whole thing again,” but you did say , “In Excel 2007, you can’t have a workbook with usable macros if it’s encrypted with a password” and frankly that’s not true and M$ would appreciate you getting the facts straight.
August 14th, 2009 at 8:31 pm
Actually you can lay out the solution here so that people who need this functionality may benefit.
What I’ve did was to list down the steps that I took, and I’m fairly certain that a layman would go through those steps, if not less. If the desired end result still can’t be achieved after those steps, then it certainly may be possible, but surely not practical.
August 22nd, 2009 at 4:47 pm
Thanks for venting all my frustration with this article. I was having the EXACT same problem and I was ready to throw my laptop into the street (I’ve been designing and running macros just fine with the old 2003 version, but I upgraded to the best and the greatest). Devil, if M$ makes it this difficult to do a simple thing like macros in a password-protected file, then they don’t deserve us getting the facts straight. I’m going back to 2003…
September 30th, 2009 at 1:26 pm
I’ve got exactly the same problem. It’s just one of hundreds of similar problem I’ve experienced since I upgraded to the “latest and greatest.”
Did anyone notice that nobody came back with the simple answer. I’m certain those idiots in Redmond would love people to not belittle their products. Maybe they should try fixing the problems.
September 3rd, 2010 at 6:02 am
I just went through all of this myself, spanks to MicroHard.
October 6th, 2010 at 1:47 am
Man, I hear you. I read in silence, nodding and hoping that the article would finish with a “how to”.. alas, no. I have had exactly the same experience and am stuck, stuck with a Client looking over my shoulder and work backing up… what a nightmare! ;o) good work MicroSux.
January 10th, 2011 at 11:03 am
Just got Office 2007 on a laptop. It was bad enough trying to find anything on the new menu ribbon. Then I had a similar experience with Excel 2007 when I tried to use macros in a workbook from a previous version of Excel. I kept getting a message “”Because of your security settings, macros have been disabled…” So I created a digital signature and tried it again. Same problem. I then tried adding my digital signature to the trust center. Still got the macros are disabled message. The only way I could get the macros to run was to select “Enable all macros–not recommended and potentially dangerous.” Wasted a lot of time on this. I noticed that none of the other people defending M$ offered a solution.
I’m ready to wipe Office 2007 off of the hard drive, and I won’t be buying any future versions of M$ Office.
March 17th, 2011 at 7:52 am
You CAN. Here is how:
http://www.rondebruin.nl/password2007.htm