Copied a Macro-Enabled Workbook into OneDrive, now macros cannot be run, edited, or deleted.

[ChristopherLandry]

I have a Macro-Enabled Workbook that I've been using for over a year with no issues in using the macros inside. Yesterday I copied this workbook into my OneDrive folder.

Now, when I open the file from the local copy on my hard drive, I cannot run, edit, or delete any macros. I can record a new macro, but that macro has the same limitations as previously created macros, it cannot be run, edited, or deleted.

Trying to run a macro produces this error message:

That sounds like a lead, except that when I open the workbook I do not get a security banner at all, though the banner settings are enabled.

I have tried these settings in the Trust Center and they have not altered the behavior of macros.

I added the local hard drive location for OneDrive to Trusted Locations, including checking the box to include all subfolders, but this has not fixed the issue.

I should not have to use the un-secure macro security settings I posted above, but I would make do if they actually worked. As it is, I am stumped on how to fix this issue.

I would really love to be able to use OneDrive for my important documents, but most of my important Excel Workbooks have at least one macro in them, since I use them to save time on tasks that are repeated often.

Update 4/4/2022

I've done some more testing and it is definitely an issue related to the Trusted Locations in Office. I created a new folder and added that folder to Trusted Locations. I was able to use macros from files in that folder without any issues. Then I uninstalled and re-installed OneDrive and pointed it to that folder for its base location. After OneDrive took over the folder, macros no longer worked from files in that folder.

Basically, Office is not recognizing a OneDrive folder as a Trusted Location despite the folder being in the list.

If it helps, here are my Office and OneDrive versions:

Office - Version 2203 (Build 15028.20160 Click-to-Run) Current Channel

OneDrive - Version 2022 (Build 22.033.0213.0002)

Update 4/18/2022

I ran a test with Dropbox, since it's a similar service. I am able to get the local Dropbox folder recognized properly as a Trusted Location in Office and macros work fine once that's done. Which means it's not automatic just because an app is monitoring the folder for synchronization.

So, this seems to be purely a question of how to fix OneDrive to make it work correctly with Trusted Locations.

[#2]

@ChristopherLandry  Hi Chris, did you end up getting any good solutions? We are having (maybe) similar issues, sadly don't know how to make Excel to trust our personal onedrive folders

[#3]

@zfeng1380 Hello. Unfortunately Microsoft has been completely silent about this issue as far as I'm aware.

Here's a more complete image than the one I put in my original article showing how to get to the Trusted Locations settings in Excel Options for adding a local folder to the Trusted Locations list.

If you get to this point and your Excel workbook's macros continue to work after the workbook is added to a OneDrive folder, please let me know. Maybe Microsoft has fixed this in the background with an update somewhere along the way.

[#4]

@ChristopherLandry  thanks for the detailed reply. Unfortunately no luck still. Life is so tough using 2 office products....

[#5]

Having the exact same issue.
Our current workaround is to copy a file to your desktop, run the required macro there, then copy it back onto our shared Onedrive office folder.

But this is only a temporary solution, the best will be to allow macros to be run on Onedrive directly. Would love to see if the smart people can come up with a solution.

[#6]

Just an added note, all files used to work perfectly before day before yesterday (6 September 2022). Problem only started then.

[#7]

@Peet_Luckhoff hi, we had the same issue, we could't get it work, even the very expensive external IT consultancy firm we hired (we paid over USD 100 per hour!!) couldn't get it work. So the solution we used at the very end is to switch everything to OneDrive Business accounts (as opposed to OneDrive Personal), it solves the problem at the expense of extra cost, 

[#8]

@peace410 

Thanks for the reply.

It is just weird that it has been working perfectly for a good 4 months now, and suddenly 2 days ago, it doesn't anymore. 

When I say works perfectly, the different users could open the files on a shared onedrive folder, and run the macros in the files without issues.

Some added info, the macros are still enabled when I open the files on my pc, and only on my pc. I am the one who set up the Shared Onedrive Folder in the first place.  However, it doesn't work on the other users pc's anymore.  They have "Can Edit" Access to the folder.

[#9]

@Peet_Luckhoff the cause is because of Microsoft's security upgrade related to macro. please see below for the official documentation (https://docs.microsoft.com/en-us/deployoffice/security/internet-macros-blocked)

if that official documenttation cannot solve your issue, there is a community discussion on this, some found it helpful, please google "Microsoft has blocked Macros from running because the source is untrusted", the very first search result should be that one.

and again if you still cannot fix it, and i will assume you use OneDrive - Personal (not business) account, then what worked for us at the very end is bite the bullet and upgrade to business acount

[#10]

@peace410 

I contacted Microsoft support on this and they could not resolve it. They did have me create feedback in the the MS Feedback Hub. https://aka.ms/AAi4hrx . Please upvote for my feedback to give it higher priority. I am using Windows 11, OneDrive Personal. Strangely, one workbook in the shared folder does still work with macros.   

[#11]

hi paul, good to hear your active involvement in solving the issue, and sad to hear you have the same issue as many of us. Yes we tried microsoft support without any success too. Sadly MS won't do anything in its Feedback hub, its just there to give ppl false hope.

so if you need the issue to get fixed asap, the quickest and only known solution to mankind (but not yet known to microsoft support, lol) is to upgrade your account to business

[#12 · Accepted solution]

I assume you added your local Onedrive location.
now you should add the online Onedrive location name "https://d.docs.live.net/" make sure that you checked the "Subfolders of this location are also trusted"box.
Noted that you have to check the "Allow Trusted Locations on my network (not recommended)" box first.

[#13]

@exeg 

Thanks, exeg.  That suggestion worked for me.  

Others keep in mind that if you have assigned macros to objects, you may need to reassign them if you have moved the file.

[#14]

@exeg  Thanks that suggestion also worked for me!

[#15]

@exeg 

Thank you, exeg. I think that OneDrive online location made all the difference. I don't think I would have guessed that specific site location was the home of OneDrive.

Now that I can add my most important spreadsheets to OneDrive, which all have important macros, the software is actually useful to me!

[#16]

Thank you very much!! That worked for me

[#17]

@exeg 

I understand you were able to solve this problem.

I added a local one-drive location and checked the "Subfolders of this location

I added also the online one-drive location and checked the "Subfolders of this location
Can you send a picture or something similar so I can understand better?

I thank you for the help

[#18]

Try this:
File | Info | Properties | Advanced properties | clear the Author information. Save the workbook, close, and re-open.

[#19]

Make sure you also check the box at the bottom of Trusted Locations for "Allow Trusted Locations on my network"

[#20]

@exeg Thank you so much for posting this. It was driving me crazy!