Security warning when running macro in ribbon

thelordgiveth

New Member
Joined
Dec 31, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
An issue that has arisen in the last few weeks. I have a custom ribbon which executes VBA code in a .xlsm workbook, which is in a Trusted Location. When I try to execute from another workbook, I get the "Microsoft Office has identified a potential security concern. Automatic update of links has been disabled...." message. It doesn't tell me anything else. Neither the macro workbook nor the workbook I am actually in contains (or has ever contained) any links. The only "link" is that I am calling a macro.

The warning box gives me the choice of pressing Disable (which yields another helpful box reading "400") or Enable. If I press the latter, the macro runs and the .xlsm workbook loads (which it may always have done) and becomes visible and active (which it didn't do before). I minimise the .xlsm workbook and can then continue to execute any of the macros called by the ribbon buttons from any number of workbooks without any more warning messages appearing. Unless I close the macro workbook (or of course start a freshg Excel session).

It is only a minor annoyance (as long as I don't run the macro with the wrong workbook active!) but I don't like not understanding why this is happening and in particular why it has started to happen now. Dodgy software update?
 

thelordgiveth

New Member
Joined
Dec 31, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Simple things first. I created a new custom ribbon toolbar with one simple macro from a normal .xlsm file (i e not the Personal Macro Workbook). I still got the message. When I changed this to run the same macro in the Personal Macro Workbook, I did not get the message.

So that is an obvious solution on the face of it. I'm not sure that I really want to have a couple of hundred macros from two different projects jostling each other in the PMW and there might be name conflicts in both the variables and subroutines. I could do it for the more frequently used project, I suppose. Or I could revert to just running the macros by selecting them, which I do for macros that I use only occaionally.

I haven't looked at the add-in approach yet.
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,523
Office Version
  1. 365
Platform
  1. Windows
Does it make a difference if you store the macro in the same folder as the Personal Workbook macro ?
Also when you are using the normal .xlsm are you still using the same one as previously ? If so what happens if you copy the macro into a new workbook (just in case there is any shrapnel in the existing one) ?
 

thelordgiveth

New Member
Joined
Dec 31, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Does it make a difference if you store the macro in the same folder as the Personal Workbook macro ?
Also when you are using the normal .xlsm are you still using the same one as previously ? If so what happens if you copy the macro into a new workbook (just in case there is any shrapnel in the existing one) ?
First question - yes! So all I need to do, it seems, is save a copy of the relevant macro-enabled workbook in the startup folder and change the path for each of the buttons (not sure if I can do that by editing or whether I have to recreate the buttons).

I was using a new macro-enabled workbook for the test so no shrapnel. Just one macro generating a message box. Yes, of course it said "Hello"...
 

Alex Blakenburg

MrExcel MVP
Joined
Feb 23, 2021
Messages
3,523
Office Version
  1. 365
Platform
  1. Windows
Haha.
If moving it to the Personal Macro folder makes a difference then although I know you said you set up the trust setting, it would still indicate that it is some sort of trust issue.

I had a play with trying to change the path for my macro button and I also got the impression you probably need to recreate it.
 

thelordgiveth

New Member
Joined
Dec 31, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows

ADVERTISEMENT

To update, I have copied the macro-enabled workbook into the Startup folder and added a new button linking to one of is macros. No warning message when hitting the new button; still a warning message when calling the same code from the original workbook.

So, a fairly painless solution (as I only have ten or so buttons to recreate).

The root cause does seem to be a Trust Issue for a supposedly trusted folder. Perhaps Excel needs to see its relation ship counsellor.
 

thelordgiveth

New Member
Joined
Dec 31, 2021
Messages
9
Office Version
  1. 2019
Platform
  1. Windows
Some interesting consequences of transferring the macro workbook to the startup folder and using ribbon buttons to run them.

First, just doing it for one project does not work very well. This is because selecting a macro ad hoc (via Developer, Macro) brings up a long list of macros from the startup folder workbook, as well as the current workbook, including many subroutines not intended to run on their own. Yes, you can filter to restrict to the current workbook but that's a pain every time.

So my choice was to put all my regularly used macro workbooks in the startup folder and set up further ribbons. That makes the "long list of macros" even longer but if I hardly ever use that any more, that's fine.

Secondly, I call some Excel macros from within Access VBA (note that startup macros are not automatically available; you have to open Excel and the macro workbook to achieve this). To get this to run now I have to prefix the macro name with the workbook name. This took me some time to realise - and cleaning up after automation errors is messy!

Thirdly, my impression is that macros are running considerably faster. The startup folder is on a SSD whereas the macro workbooks were previously on a HD but I would not have expected this to make any difference once loaded into memory.

Hope the above notes are of some use.
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,409
Messages
5,831,448
Members
430,069
Latest member
bubbleboom

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top