So, you upgrade to Excel 2007 and your macros stop working. If you are incredibly lucky, it might be a situation where the macro would work, but you need to enable macros. Episode 828 shows where Microsoft hid Macro Security options and what the new words actually mean.
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
This blog is the video podcast companion to the book, Learn Excel 97-2007 from MrExcel. Download a new two minute video every workday to learn one of the 377 tips from the book!
Transcript of the video:
Hey. Welcome back to the MrExcel netcast.
I want to thank George Wood for that great Where Is It Wednesday theme.
I got an email just this morning from Shannon.
Shannon said, hey, someone in our office got a new computer and it had Excel 2007, and he said, the thing that's driving us crazy is the macros don't work anymore, and it's my problem because I had written the macro for Shannon, and so they used to press CONTROL+R here, the R, they press CONTROL+R, and I just cringe.
I cringe when I hear that someone has Excel 2007 and my macros don't work anymore, and so, you know, he said, what can we do?
I said, well, we’re going to have to tweak that macro.
We’re going to have to figure out what to do.
Well, in this case, it turns out that it was really simple.
When we go to Excel 2007, macros are turned off.
So, what we had to do was go to macro security to turn things on, but it's completely hidden.
There is no macro security that anyone can find.
If you come here to the VIEW tab and go to MACROS, they give us 3 options and none of them are security.
So, here's what we do.
We have to go to the Office button, top left-hand corner, choose the Office button, go into EXCEL OPTIONS.
Now, on the left-hand side, you're going to have to choose TRUST CENTER, and then, on the right-hand side, TRUST CENTER SETTINGS, and, now, back to the left-hand side and choose MACRO SETTINGS, and we have 4 options here.
They changed all the words.
If you used to use low, and you shouldn't, then you want to ENABLE ALL MACROS.
If you used to use MEDIUM, if you used to use MEDIUM, that's now called DISABLE ALL MACROS WITH NOTIFICATION.
That’s the one I use, so we turn that on.
Click OK.
Click OK.
Now, the macro is open here but the macros still are not going to run.
We have to close this file and reopen the file.
So, we use FILE, CLOSE, open it again, and now you'll see that, instead of popping up a message saying, hey, there's macros here, do you want to enable or not, we get a security warning MACROS HAVE BEEN DISABLED.
Click OPTIONS and say ENABLE THIS CONTENT.
Now, finally, we can run the macro, CONTROL+R, and everything works great.
Now, another way to go here is, especially if you're going to be recording macros and you want more than just those 3 buttons on the VIEW menu, then, go ahead into the OFFICE button, EXCEL OPTIONS, and choose SHOW DEVELOPER TAB IN THE RIBBON.
It's right here in the very first POPULAR category, and that now gets us the tab that actually has a button for MACRO SECURITY.
Same way to get to macro security without going through the 17 steps.
So, plenty of different ways to go.
Try this first to try and get your macros to work.
Now, I'm interested.
For those of you who have switched to Excel 2007 and you had a macro that worked in 2003, but it still didn't work in 2007 after enabling macros, I'd love to see that macro.
I'd love to figure out what all the problems are between 2003 and 2007.
Obviously, they added some new features, but I want to find the old features that the macro will just stop at and it won't run anymore.
I’m always interested in finding those out.
So, if you have one of those, drop me a note -- bill@mrexcel.com.
You can either send me the macro, or if you figured out what the bad piece was, just let me know.
Drop me a note.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
I want to thank George Wood for that great Where Is It Wednesday theme.
I got an email just this morning from Shannon.
Shannon said, hey, someone in our office got a new computer and it had Excel 2007, and he said, the thing that's driving us crazy is the macros don't work anymore, and it's my problem because I had written the macro for Shannon, and so they used to press CONTROL+R here, the R, they press CONTROL+R, and I just cringe.
I cringe when I hear that someone has Excel 2007 and my macros don't work anymore, and so, you know, he said, what can we do?
I said, well, we’re going to have to tweak that macro.
We’re going to have to figure out what to do.
Well, in this case, it turns out that it was really simple.
When we go to Excel 2007, macros are turned off.
So, what we had to do was go to macro security to turn things on, but it's completely hidden.
There is no macro security that anyone can find.
If you come here to the VIEW tab and go to MACROS, they give us 3 options and none of them are security.
So, here's what we do.
We have to go to the Office button, top left-hand corner, choose the Office button, go into EXCEL OPTIONS.
Now, on the left-hand side, you're going to have to choose TRUST CENTER, and then, on the right-hand side, TRUST CENTER SETTINGS, and, now, back to the left-hand side and choose MACRO SETTINGS, and we have 4 options here.
They changed all the words.
If you used to use low, and you shouldn't, then you want to ENABLE ALL MACROS.
If you used to use MEDIUM, if you used to use MEDIUM, that's now called DISABLE ALL MACROS WITH NOTIFICATION.
That’s the one I use, so we turn that on.
Click OK.
Click OK.
Now, the macro is open here but the macros still are not going to run.
We have to close this file and reopen the file.
So, we use FILE, CLOSE, open it again, and now you'll see that, instead of popping up a message saying, hey, there's macros here, do you want to enable or not, we get a security warning MACROS HAVE BEEN DISABLED.
Click OPTIONS and say ENABLE THIS CONTENT.
Now, finally, we can run the macro, CONTROL+R, and everything works great.
Now, another way to go here is, especially if you're going to be recording macros and you want more than just those 3 buttons on the VIEW menu, then, go ahead into the OFFICE button, EXCEL OPTIONS, and choose SHOW DEVELOPER TAB IN THE RIBBON.
It's right here in the very first POPULAR category, and that now gets us the tab that actually has a button for MACRO SECURITY.
Same way to get to macro security without going through the 17 steps.
So, plenty of different ways to go.
Try this first to try and get your macros to work.
Now, I'm interested.
For those of you who have switched to Excel 2007 and you had a macro that worked in 2003, but it still didn't work in 2007 after enabling macros, I'd love to see that macro.
I'd love to figure out what all the problems are between 2003 and 2007.
Obviously, they added some new features, but I want to find the old features that the macro will just stop at and it won't run anymore.
I’m always interested in finding those out.
So, if you have one of those, drop me a note -- bill@mrexcel.com.
You can either send me the macro, or if you figured out what the bad piece was, just let me know.
Drop me a note.
Well, I want to thank you for stopping by.
We'll see you next time for another netcast from MrExcel.
