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.

 

[Joe4]

Welcome to the Board!

The spreadsheets are also used by numerous people so signing them is an impractical solution.

What do you mean by this? The only person who needs to sign it is the one who writes/maintains the macros. As long as no one else modifies the code, the signature won't be discarded. So each person would just have to accept the signature once, and they will not get the warnings anymore (macros will automatically be enabled).

Another option is to set-up a Trusted Location (the directory where the file resides). The any macros found on files in those location will automatically be accepted.

 

[jeffreybrown]

Here my two cents...

I don't know of a way to get the option back to enable macros, but you might want to look into setting up your spreadsheet so the user is forced to enable macros.

Here is a link http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

As far as the security banner popping up one way or the other not sure.

 

[kenf]

Welcome to the Board!


What do you mean by this? The only person who needs to sign it is the one who writes/maintains the macros. As long as no one else modifies the code, the signature won't be discarded. So each person would just have to accept the signature once, and they will not get the warnings anymore (macros will automatically be enabled).

Another option is to set-up a Trusted Location (the directory where the file resides). The any macros found on files in those location will automatically be accepted.

The Trusted locations are all greyed out on our system. Trusted publishers only has a view and remove button, no option to add. We can sign our own macros but we can not add in another users digital signature, we would have to ask each person to go in and sign the macro themselves, this causes problems when another person opens the spreadsheet it says the signature is invalid. (always fun working in locked down systems)

 

[Joe4]

Sounds like something to toss back at the system people and say "here, you figure it out!!!" Maybe then they will relent and make some concessions. Otherwise, you may be stuck with the structure they have set up.

Another possibility may be converting the macros to an Add-In and removing them from the files, and then have the Add-In distributed. I have never done this myself, and don't know how if it will all work out in your environment, but just thought I would toss that idea out there.

 

[kenf]

Sounds like something to toss back at the system people and say "here, you figure it out!!!" Maybe then they will relent and make some concessions. Otherwise, you may be stuck with the structure they have set up.

Another possibility may be converting the macros to an Add-In and removing them from the files, and then have the Add-In distributed. I have never done this myself, and don't know how if it will all work out in your environment, but just thought I would toss that idea out there.

I work in Government so no chance of concessions from our systems staff.
Thanks for your advice about the Add-In, I haven't worked with these before but will hit the books and give it a try.

 

[kenf]

Here my two cents...

I don't know of a way to get the option back to enable macros, but you might want to look into setting up your spreadsheet so the user is forced to enable macros.

Here is a link http://www.vbaexpress.com/kb/getarticle.php?kb_id=379

As far as the security banner popping up one way or the other not sure.

Thanks for the link, I looked at it but it adds a bit too much complexity to the spreadsheets. Will give it a try when I have more time to play around.

 

[Joe4]

Gotta love government/big business!!!

There was a discussion in the Lounge not too long ago that touched on Add-Ins, and some good links were provided. So you may find that link useful. Here it is: http://www.mrexcel.com/forum/showthread.php?p=2384661#post2384661

 

[Peter_SSs]

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?

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.

 

[Peter_SSs]

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 ?

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