Excel & VBA: run a macro from another workbook from custom ribbon

Question

The subject sums it up...
with WIN10 and 365.

I have created a PERSONAL.XLSB with code that loads a custom tab for my ribbon just by opening a new workbook. I have distributed this PERSONAL.XLSB to another user on the network so they too can load the custom tab for the ribbon just by opening a new workbook. No problems here.

I am getting tripped up with how my buttons on the custom ribbon need to call specific macros.

What I would like to do is have a separate workbook for each tool I am providing. I would like my ribbon button to run a macro contained in another workbook - the published user version of my 'developer' workbook. I want to develop separate tools in separate .xlsm files, publish a debugged copy that is ready for use to a network location, and have all the users access the macros from buttons on a custom ribbon.

Here is the code that creates the button in the custom ribbon:

ribbonXML = ribbonXML + "        <mso:group id='ArchiCAD\_Group' label='ArchiCAD Tools' autoScale='true'>" & vbNewLine 

ribbonXML = ribbonXML + "          <mso:button id='runClmFit' screentip='Adjust Excel to fit ArchiCAD layout guide' supertip='Supertip description' label='Column x Fit' " & vbNewLine 

ribbonXML = ribbonXML + "imageMso='AppointmentColor5' onAction='AdjustColumns'/>" & vbNewLine 

ribbonXML = ribbonXML + "          <mso:button id='runPDF\_Crop' screentip='Crop .pdf file to fit in ArchiCAD layout guide' supertip='Supertip description' label='Crop a .pdf' " & vbNewLine 

ribbonXML = ribbonXML + "imageMso='AppointmentColor5' onAction='RunPdfCrop'/>" & vbNewLine 

ribbonXML = ribbonXML + "        </mso:group>" & vbNewLine 

These custom buttons work great when used in the .xlsm file that contains the source code for the macro. The work great with a MsgBox also.

So... I opened a new workbook and the second from last line of [onAction='RunPdfCrop'] is where I am trying to access the macros.
* I have tried [onAction=Application.Run 'Another Workbook.xlsm'!RunPdfCrop]
... with a wide variety of single quote and double quote placement, hoping to get syntax correct. I end up creating incomplete strings with comments by using different mashups of " and '.
* I have tried [onAction='RunPdfCrop']
... where 'RunPdfCrop' is a sub in PERSONAL.XLSB that has the line [Application.Run 'Another Workbook.xlsm'!PDF_CropDetail] to run the external macro from another workbook. I get a message about how the 'RunPdfCrop' cannot even run because it may not be available in this workbook or all macros may be disabled, even though this macro that calls the external macro is in the PERSONAL.XLSB file.

What am I missing?
Can someone point me in the right direction please?

I cannot imagine that this development strategy is so far fetched that it is impossible...

Thanks to all that read this,
Chris

Answer 1

Sub HowToRunOtherWorkbooksMacro()

Dim w As Workbook 

On Error GoTo OpenFile 

'Check to see if file is already open or not

Set w = Workbooks("Book with macro.xlsm") 

RunMacro:

Application.Run "'Book with macro.xlsm'!MacroName" 

Exit Sub 

OpenFile:

'Open the file because it was not open

Workbooks.Open ("C:\FolderPath\Book with macro.xlsm") 

Resume RunMacro 

End Sub

Answer 2

... where 'RunPdfCrop' is a sub in PERSONAL.XLSB that has the line [Application.Run 'Another Workbook.xlsm'!PDF_CropDetail] to run the external macro from another workbook. I get a message about how the 'RunPdfCrop' cannot even run because it may not be available in this workbook or all macros may be disabled, even though this macro that calls the external macro is in the PERSONAL.XLSB file.

Make sure that your code also opens "Another Workbook.xlsm" - if it is not open at the time of calling, that may be the source of your issue.

But - it may be an anti-viral sort of check imposed on code run from the ribbon. Not sure but stranger things have happened....

Answer 3

Hey Bernie,

Thanks for your interest in this dilemma and a quick response. Before coding anything, I manually tested the theory. I opened the workbook that contained the macro called by the button on the custom ribbon. Then I opened a new, blank Excel file. Then, in the immediate window, I ran:

Application.Run "'<full path to .xlsm file>'!<macro>"

I received a dialog informing me of:

Run-time error '1004':
Sorry, Excel can't open two workbooks with the same name at the same time .

This leads me to believe that prior opening of the file that contains the macro [manually or programmatically] is not necessary because it does not seem to be possible.

Anti-viral blocking does not seem likely either or I would not be able to open the .xlsm file that contains the macro, push the button on the ribbon, and have it work without hesitation.

I appreciate the insight of your idea.

Chris

Answer 4

<edit>
[draft this reply > go to a meeting for 2 hours > return to desk and press 'Submit' > see your reply...]
Let me give the above code a shot...
</edit>

Further investigation shows that opening a new, blank Excel file and running the macro gives me two different results when using two different methods to run it.

* Press the button on the custom ribbon that runs the sub that executes:

Application.Run "'<full path to .xlsm file>'!<macro>"

gives me the message about how the 'RunPdfCrop' macro cannot even run because it may not be available in this workbook or all macros may be disabled, even though this macro that calls the external macro is in the PERSONAL.XLSB file. I have also verified that PERSONAL.XLSB file is a valid project in the VBA solution bar in the IDE.

* from the immediate window of the same new, blank Excel file running:

Application.Run "'<full path to .xlsm file>'!<macro>"

actually opens the .xlsm file that contains the macro and runs the macro from the new, blank Excel file. Now there are two files open - the original blank, new Excel file and the .xlsm file that contains the macro that is running.

Answer 5

Bernie,

I appreciate the snippet of code. I was thinking along the same lines with the 'On Error...', but opted for the manual, immediate window test described below. Regardless, your coded solution gives me the same message about how the 'RunPdfCrop' macro cannot even run because it may not be available in this workbook or all macros may be disabled.

So I navigated to Macro Settings in Trust Center and set it to 'Enable All Macros', which is something I do not want to direct the entire studio to mess with. No help with the macro police taking a day off. That only leaves availability for me to question.

So, to clarify, I am expecting that the 'RunPdfCrop' macro will be available to a new, blank Excel file because the 'RunPdfCrop' macro is found in the PERSONAL.XLSB file. And the PERSONAL.XLSB file is part of every new, blank Excel opening because PERSONAL.XLSB file.

I am starting to question if calling an external macro from a file and expecting availability in that file just because of the PERSONAL.XLSB is a legit understanding of how this works. More testing, but on Monday...

Have a good weekend - chris