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.
Did you try to sign the macro? Look up digital signatures.
Alvin, this absolutely can be done. E-mail me and I can send you a sample I just created of this in action.
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 😉
Avoid the antivirus scan from registry:
http://support.microsoft.com/kb/927150
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.
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.
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…
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.
I just went through all of this myself, spanks to MicroHard.
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.
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.
You CAN. Here is how:
http://www.rondebruin.nl/password2007.htm
I know this has been here for a bit and suprised nobody said anything…
I was looking for something else and came upon this…
Open VBA and double clock on “ThisWorkbook” and add…
Private Sub Workbook_Open()
Dim wSheet As Worksheet
For Each wSheet In Worksheets
wSheet.protect Password:=”password”, UserInterFaceOnly:=True
Next wSheet
End Sub
This is just a easy and simple way of adding a password protected workbook. There are plenty of sites out there with more complex ways of adding password protection to your workbooks. The key here is the addition of UserInterFaceOnly:=True which allows macros to change protected/hidden cells on workbooks with protection enabled. Of course your system has to be able to run macros for this to work. If you forget password you can also look it up again in the same place.
If you are afraid of someone unlocking your form by looking at your password, don’t worry about it. Just google “can’t remember my Excel password” and it will show you how to unlock workbooks you don’t know passwords to. Password protect is only to stop people from accidently messing up the form there is nothing to stop anyone from doing it on purpose.
I faced similar problem. For once, I thought, I lost my work but than as a quick workaround, I could simply open the workbook and do another simple save as “another name.xlsm” . The new workbook worked the macros, so atleast I didnt have to copy paste all work from original. But yes, yet to see a simple answer on this one. The link Matt gave above, doesnt works. Hope it helps!
And well, after posting my initial response, I did found this on Microsoft’s website.
https://support.microsoft.com/en-us/kb/928613
Basically, what article says is, “you cannot have this feature working unless exposing your system to big virus threats and risks”. And then it gives a workaround that will make the solution work but better not done.
Hope it helps others looking for a solution on this!