Macro security options

[kenf]

I am working in Excel 2007

When I open my spreadsheet, I see the macro security banner and have the option of clicking on it and then enabling macros. However, sometimes I forget and start working on the spreadsheet and notice that the macro security banner has disappeared. Is there a way to get it back, without closing and reopening the spreadsheet ? (large spreadsheet on slow network so it takes a couple of minutes to do this)

Also I notice that sometimes I get the security banner under the ribbon and other times I get the pop up window. I haven't figured out why I get one or the other. what triggers the pop up window instead of the security banner under the ribbon?

I am working on an administered system so I have no ability to change macro defaults. The spreadsheets are also used by numerous people so signing them is an impractical solution.

 

[kenf]

If you have the Visual Basic window open when you open the file you will get the pop-up window. Otherwise you will get the banner message.

Thanks Peter, that does explain the two different warning messages, which is great to know since I don't have to cover the pop up window in my documentation for users who will never open the VBA window. If they have the VBA window open they should know to click enable macros.

 

[kenf]

Go to the 'View' tab on the ribbon and check the 'Message Bar' checkbox in the 'Show/Hide' group.

Hi Peter, just to let you know, it does allow me to show hide the message bar but unfortunatly whenever, after I copy a new sheet, the message bar with the enable macro option disappears, I looked at the show hide group option button and it was greyed out after I copied a sheet.

 

[Peter_SSs]

Hi Peter, just to let you know, it does allow me to show hide the message bar but unfortunatly whenever, after I copy a new sheet, the message bar with the enable macro option disappears, I looked at the show hide group option button and it was greyed out after I copied a sheet.

You hadn't mentioned copying a sheet previously. Not sure if I will be able to help here but can you explain in more detail what is happening?

 

[kenf]

Hi Peter

I have a spreadsheet with a pivot table in it and my macro changes the pivot table into a regular table, so one of the first things I need to do is to copy the pivot table so I don't ruin my original copy. But when I copy the tab, the macro security options message bar disappears. I can perform other operations on the spreadsheet and the macro security options message bar will remain but when I move copy a tab the message bar disappears. When I try to run a macro it then says the spreadsheet has to be closed and reopened to enable macros. Just trying to figure out if there is a short cut around this?

 

[Peter_SSs]

I can now reproduce what you are describing but I can't see that there is an easy way around it. I think you will have to either ..

a) Close and re-open as you have been doing, or

b) Look at Jeff's suggestion (post #3) of forcing users to enable macros when they first open the file. I haven't looked at the link in that post but there have also been threads about this in this forum in the past if you want to go looking.