Allow Trusted Locations on my network - Group Policy (GP)

[AKA Bones]

Hi,
I have an Excel SpreadSheet that we use in our company which includes
macros. Currently when users open it they are prompted to allow macros,
which some forget to do, meaning the functionality doesn't work as expected
and the support department get a call to assist.

To work around this I tried digitally self-signing the project which
worked for me but not anyone else unless they explicity accepted the
self-created certificate, which leaves me back in the position of requiring
user-interaction which I'd like to avoid.
I then placed the document in a network share and modified the Trust
Centre settings so that the share was specified in the Trusted Locations,
whilst also enabling the "Allow Trusted Locations on my network (not
recommended)". This works a treat, but still required a manual configuration
step by the users to set this up.

I then read through the documentation supplied by MS regarding how to
configure settings in the Trust Centre using Group Policy. This initially
worked a treat, I was able to set the network path into the "Policy
Locations" within Trusted Locations using the GP entry: User Configuration -
Administrative Templates - Microsoft Office 2007 systems - Security Settings
- Trust Centre - Trusted Location #1.

This however still required users to enable "Allow Trusted Locations on
my network (not recommended)". I found this setting against Excel in GP
under: User Configuration - Administrative Templates - Microsoft Office Excel
2007 - Security - Trust Centre - Trusted Locations - Allow Trusted Locations
not on the computer. I enabled this in the GP and ran gpupdate / force.
When I re-opened Excel the setting is still disabled, meaning users are
still prompted to enable macros. I've run rsop.msc on the client machine and
it shows that the above setting is enabled, even though I can still manually
enable and disable the value in Excel without the GP overriding anything.


Can someone please suggest what I might be doing wrong as I've already
spent a fair amount of time working this out and can't see what else needs to
be done to enable this.

Thanks in advance,
Andy

 

[Luke M]

Alternate idea, not solution...

If you don't get an easy solution, perhaps this alternate idea will work.
Setup your workbook so that all sheets are hidden (or even veryhidden, via
VB) with only 1 sheet visible. On this sheet, put a nice disclaimer along the
lines of "You must enable macros for this workbook to operate correctly!".

Now, setup Workbook_Open event that hides your disclaimer sheet and unhides
your other sheets. Similarly, set a Workbook_BeforeClose event to rehide your
working sheets and show disclaimer.

Now, those who don't enable macros will get a nice reminder, and can close
and re-open workbook. Those who do enable them will be able use the workbook
normally.

Again, not a solution to automatically allowing your macros to run, but I
think it would at least cut down on tech support calls.

 

[AKA Bones]

Luke,
Thanks for the alternate way of looking at things. I like the idea and
may well implement it if I can't find another solution.

Interestingly I've left it a bit whilst I had lunch and the GP setting
appears to have worked now - possibly because of time, or because I closed
Excel and my other Office App's in frustration(!).

The only problem I've got now is that the user is still prompted with a
message to accept the self-created certificate - but at least it works once
they've done that.

Andy