Macros Won't Run in Excel 2007

[sunday_storyteller]

All,

I have a Macro that I wrote that I need to be able to run. I am using Excel 2007. This workbook and macro were written in Excel 2007, and I saved the file as a "Macro Enabled Worksheet". The Macro was working fine before and after saving it.

When I opened the worksheet later today and tried to run that Macro, it says,
"Because of your Security settings, Macros have been disabled. To run macros, you need to re-open this workbook, and then choose to enable macros. For information about enabling macros, click Help."

I did click Help, and there was nothing useful there. (If their solution had worked, it would have been useful, but it did not.)

My security settings for Macros are currently "Disable all macros with notification."

It doesn't, however, give me an notification or choice to run the macros or not.

I need this macro to work, and one solution that I read (save the macro to your personal workbook and access that when you need it), I didn't even try because it is not an option. I need to be able to pass the Macro with the workbook to some other users.

Thanks in advance for any help. This is the first major problem I've encountered with Excel 2007, but it is an extremely annoying one.

 

[dafan]

If you go to the Office button in toplefthand corner, choose Excel Options -> Trust Center, choose the Trust Center Settings button on the right, then go to Macro Settings. I can set this to Enable all or Disable with notification without any problems.

 

[Peter_SSs]

My security settings for Macros are currently "Disable all macros with notification."

It doesn't, however, give me an notification or choice to run the macros or not.

Are you sure? I thought this too to start with because the notification is not an pop-up message box like in previous versions.

When you open the workbook containing macros a new "bar" appears above the formula bar and below the Ribbon/Quick Access Toolbar and provides options.

 

[Capt_Ron]

Is your workbook password protected. This proved to be a problem for me. Check this posting. http://vbaexpress.com/forum/showthread.php?t=18978

I built a workbook with macros in Excel 2007, saved it to the .xlsm format, and found that as long as the workbook was password protected on open, macros were not enabled and no provision was offered to enable them.

The workbook can be protected on open, just not password protected. I hide all but the "Start" sheet on close and require the user to click a button to make the other sheets visible. Part of the code connected to the button enables events, unhides the other sheets, and sets password protection on the workbook. With the workbook already open, a password can be applied to protect the workbook and discourage a user from adding, deleting, or re-arranging the sheets.

Ron

 

[sunday_storyteller]

Thank you so much for your help.

The issue was that the notification was not a pop-up. I did eventually find the ribbon-notification. And now it works fine. Thanks!

Do you have to click the ribbon to turn macros on every time you open the workbook? It's fine if you do, but if there's a way around it (other than a digital signature that would auto-activate it), then I'd be interested in knowing how to do that.

 

[Capt_Ron]

One way to avoid having to activate macros each time on open is to designate one or more folders as trusted locations and keep your "trusted" Excel files in those folders.

Ron

 

[spicetrader]

One way to avoid having to activate macros each time on open is to designate one or more folders as trusted locations and keep your "trusted" Excel files in those folders.

How?

 

[Capt_Ron]

Spicetrader,

For some good instructions on designating folders as trusted locations, take a look here:

http://faculty.fuqua.duke.edu/~pecklund/ExcelReview/Excel%202007%20and%20macro%20security.pdf

Ron