If you want Excel to stop asking you if you want to enable macros every time you open a macro enabled workbook, you should think about using a trusted location.
A trusted location is a folder either on your computer or the network that you have told Excel you trust. Basically you are saying, any file in this folder is safe, just open it and don’t ask me if I want to enable macros.
So when you designate a folder as trusted, make sure that you really do trust that location. It should be somewhere that either you or your network administrator have control over.
Default Trusted Locations
A number of folders are trusted by Excel by default, these are shown below. You could put your files in one of these folders if you wish but I prefer to keep my files separate to anything that Excel/MS Office uses just in case that folder gets removed during a repair or reinstall of Office.
Network Trusted locations
It is possible to have a folder on the network as a trusted location, and if you work in an organization with servers, this might be the case. Check with your I.T. admin if there is a folder or folders that your organization uses for this.
A Word of Warning
It’s not a great idea to set your Documents/My Documents or your Downloads folders as trusted locations. Doing this creates an easy target for malware to exploit. You are much better off having a totally separate folder as a trusted location.
Create a Trusted Location
The steps in Excel 2007, 2010 and 2013 are virtually the same, except that in 2007 you click the Office button to start the process, in 2010 and 2013 you click on the File tab. I'm going to use 2013 in these images to illustrate the steps.
- Click on the File tab (or the Office button in 2007)
- Click Excel Options
- Click on Trust Center, Trust Center Settings, then Trusted Locations.
- To create a trusted location on the network, check the Allow trusted locations on my network (not recommended) box.
- Click on Add new location. You can type the folder path\name in the Path box, or click on Browse to choose the folder.
- To include subfolders in your chosen folder as trusted locations, check the Subfolders of this location are also trusted check box
- Enter a description in the Description box if you want
- Click OK to add the new trusted location
- Click OK, OK to finish and get back to Excel
Modify a Trusted Location
To modify a trusted location that you created, go back into the Trusted Locations (steps 1 to 3 above), select the location and click on the Modify button.
Remove a Trusted Location
To remove a trusted location, go into the Trusted Locations, select the location and click the Remove button.
My macros do not work in onedrive even after adding the folder to trusted location
I’m not sure what you mean by ‘work on OneDrive’. You have to open the file in Excel for the Desktop to execute a macro. It will not run when the file is closed and it won’t run on Excel Online.
Useful information. Thanks for posting where these settings come from.