Security warning when running macro in ribbon

[thelordgiveth]

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]

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.

 

[Alex Blakenburg]

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]

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]

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]

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.

 

[Alex Blakenburg]

Haha. Thanks for providing an update.

 

[thelordgiveth]

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.