Excel – Enable Excel Macro’s for one spreadsheet all users

microsoft excel

We have a workstation where a large number of users need to work on an Excel 2007 spreadsheet that is stored on the network and contains Macros. We have added the sheets location in the Trust Center, which causes it to work for the current user without issue, but this does not carry over to all users.

We do not want to make a general exception to allow all macro's everywhere. How can we get all users to have an exception for this one macro sheet without manually setting it for each user?

We would prefer to avoid require our users to take any explicit steps to enable macros on this sheet as it complicates the process and their level of technical expertise varies.

Best Answer

You can do this either by editing the Office installer, or by importing the Office Administrative Template and then editing your office setting through group policy.

As editing the office installer worked for me to add the path, but not to enable "Allow Trusted Locations not on this computer, I will explain how to do this both ways.

To configure Office settings using the Office Customization Tool.

  1. Browse to your Office setup file location and run setup.exe /admin (The OCT is available only with volume licensed versions of the 2007 Office system. Office Standard 2007, Office Small Business 2007, Office Professional Plus 2007, and Office Enterprise 2007)
  2. Go to Office security settings and add the path to the Trusted Locations List
  3. Under Default security settings Set Microsoft Office Excel - Allow trusted locations Options to Allow trusted locations that are not on users computer
  4. Save the .msp file, close Excel and run it.

enter image description here

If you are like me and step 3 doesn't work to allow non-local locations, you can also set the setting through local (or global) Group policy

  1. Download and extract the Office 2007 administrative templates
  2. Open the mmc and import the group policy object editor for the local computer
  3. Right click on Administrative Templates Under User Policy and choose Add/Remove Templates
  4. Browse to User Configuration/Administrative Templates/Microsoft Office Excel 2007/Excel Options/Security/Trust Center/Trusted Locations
  5. Set Allow Trusted Locations not on this computer to Enabled
  6. Add as many trusted locations as needed.

enter image description here

This allowed us to skip explaining how to enable macro's dozens of times. With only slight modifications, it will also work for Word, Access and the rest of the programs in the office suite.

Related Question