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?

 

[Dan_W]

I've noticed the same thing recently. As you say, it occurs when I've clicked a custom button on the QAT bar which has inadvertently directed me to an earlier version of the spreadsheet I was working on. My own fault for not updating it, I guess. I'm just being lazy.

In your case, by contrast, I wonder whether the best approach would be to save the workbook with the code you're calling as an addin and then install it as such. I suspect that this would satisfy MS Office's concerns.
I agree it's probably the result of of update (for example, they're meant to be phasing out certain aspects of Excel - mainly the problematic Excel 4 Macros), but it may not be a dodgy. I can see the security concerns involved.

Also, I would say you're right about the nature of the 'link' being the fact that you're calling the macro.

 

[thelordgiveth]

Thanks for reply, Dan. I had to google "Excel 4 macros" to reassure myself I hadn't used any! It does seem odd that having made it possible to attach macros to buttons in a custom ribbon (it might be stretching it to say it was encouraged but I'm sure I did not do it without something somewhere recommending it) it is then obstructed without any explanation, documentation or, er, an advance warning about the warning.

It's not a major issue for me so while I'll look at the add-in possibility (something I've never done) I'll probably just carry on as I am but it may be more of an issue for others

 

[Alex Blakenburg]

Is there any reason you don’t either include them in your personal macro workbook or put them in your start up folder and open them as hidden?

 

[thelordgiveth]

Is there any reason you don’t either include them in your personal macro workbook or put them in your start up folder and open them as hidden?

Good question! It started off as a workbook alongside the files it was to work upon, which I opened up as and when required. As, once open and set going, the macros would chug away happily for as long time, that was fine. I have other workbooks like this and I suppose I preferred to compartmentalise rather then have diverse code altogether in one humungous and not particularly well documented personal macro workbook. I'm not sure what else I would have to change if I moved its location. Like the add-in idea I'll probably look at it but more out of curiosity than pressing need. My main concern was why something which worked smoothly now had this hiccup inserted.

 

[Alex Blakenburg]

and becomes visible and active (which it didn't do before).

You might be able to fix the becoming visible part by.

• Open the macro workbook
  (you may find it to be easier if you don't have any other workbooks open)
• View > Hide
• Close Excel and when it asks you to save the Macro workbook select "Save"

Why it is asking you about links now when it didn't previously, I am don't know.
Some questions might be:-

• Do you have the first workbook open in email ?
• Are you working on a work laptop and have IT changed any security settings ?
• Is the workbook in a OneDrive or SharePoint folder ?
• Has windows been upgraded ?
• If you open the Macro workbook by itself, do you get an update Link warning ?
• Does the Macro workbook have an on Open Event that might be calling another workbook that has links ?
• If you go to View Unhide, is there another workbook that is opening which has links ?

I am running Windows 11 MS 365 and have tried a Custom Menu Ribbon Command Link I used in the past to a OneDrive macro and it seems to work fine (it has always opened the workbook but I have it set up as View > Hide)
It might give me a Enable / Disable Macro dialogue box but not a Link Update warning.

 

[thelordgiveth]

Thanks. Alex.

I've hidden the workbook now, so thanks for that.

The answers to your bullet points are all No. I hesitated on the sixth one because I did find Autpen and Auto_Close macros but I had commented out all the code within the subs years ago (essentially the code is pretty old, though not quite of pensionable age). When I commented out the relevant Sub and End Sub lines it made no difference; deleting them altogether wouldn't do anything, I presume. I understand that Workbook_Open is preferred to Autpen these days (perhaps it always was) but there is no macro in the file with that name or anything else that isn't a bespokely (of that's a word) named macro.

On the fourth one, I am on Windows 10 ver 20H2. 21H2 is outstanding but I'm in no rush and I'll let people cleverer than me sort out the bugs (sorry, features) in Windows 11, even though my computer would run it quite happily. I see there was a security update to Windows on 14th December, which is about the right timescale but I have no idea whether that would have impacted on what I am doing in Excel. I ought to wade through the KBs...

 

[Alex Blakenburg]

Sorry but I am probably out of ideas.

 

[thelordgiveth]

OK, thanks for trying, Alex.

I had forgotten that I have two groups within the Custom Ribbon and the two groups have macros from different workbooks assigned to them. I get the message with both groups. That would seem to indicate that it is a general issue rather than one relating to the individual macro workbooks, unless it is triggered by something I have done on both.

If it happened every time I run the macro it would be a real drag but when it is only the first of each set of runs it's more a conundrum than a problem.

I will experiment a bit and report the results, more to document the issue better than in the expectation of a solution.

 

[Alex Blakenburg]

Sounds good. Thanks for the update.