Trouble calling VBA macro from custom ribbon tab in Excel 2010

[fluorophore]

I have four macros I would like to call within Excel 2010. They all run fine when called using a command button. In order to have accessing them more convenient (in any worksheet), I wanted to create a custom ribbon tab that has buttons to run each. This also works fine, however I need to have this functionality available to anyone that I send the spreadsheet to.

If I export the ribbon information using "export all customizations," which creates a *.exportedUI file, and then import this on a different machine, the ribbon appears fine, however clicking on one of the macro buttons in the ribbon gives the error:

Cannot run the macro "C:\Users\ ... \filename.xlsm'!macro1'. The macro may not be available in this workbook or all macros may be disabled.

<c:\users...\filename.xlsm'!macro1'. the="" macro="" may="" not="" be="" available="" in="" this="" workbook="" or="" all="" macros="" disabled.


The reason why it's not working is clear: this isn't the path of the current file. For whatever reason, the macro path it tries to access is that of the original file when the ribbon was first created and exported.

It seems odd that the default is to specify the macro location to be the original path rather to just point to wherever the current file resides (since the macros are already embedded there). I have no idea how to fix this, but it should be simple.

I've searched a fair amount on this topic, and there are solutions out there, but I don't see how to apply them to my situation. For example, someone suggests adding this code fixes it:

[COLOR=#333333]Application.Run "'" & Application.ActiveWorkbook.Name & "'!NamShortList"[/COLOR]

The trouble is that this would have to be in a location where the macro is called, and that only occurs in the ribbon. I don't call macros from within VBA code.</c:\users...\filename.xlsm'!macro1'.>

 

[RoryA]

It would be simpler to create the custom tab yourself in the workbook's customui component - see Ron's page here for samples and information.

 

[fluorophore]

It would be simpler to create the custom tab yourself in the workbook's customui component - see Ron's page here for samples and information.

Using xml is easier? Sorry, I have no idea what I'm doing with xml.

 

[RoryA]

Well the only other option I can think of would be to edit the .exportedUI file in Notepad or similar and remove all the path information.

 

[fluorophore]

Well the only other option I can think of would be to edit the .exportedUI file in Notepad or similar and remove all the path information.

I think this worked. Great advice, thanks!

If it proves not to work for all cases, I'll have to explore the CustomUI option above.

Thanks for your help, Rory.

 

[RoryA]

You're welcome.

Using XML in the Ribbon is really not as bad as it may appear at first glance.