How to allow trusted location no lan via group policy for sharing Excel macro

**The-Farmer** · 21-01-2012

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 explicit 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 Center 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.

**BillieJ** · 21-01-2012

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 very hidden, 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.

**The-Farmer** · 21-01-2012

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.