Share Excel VB module used in ribbon macro with home laptop and work PC.

[Randy Roberts]

I recently upgraded  from Excel 2002 to Office 365  as 2002 has crashed and I can't find the disks. I was surprised to find my workbooks work well with the new Excel. I am currently challenged with creating a ribbon UI for my macros. I have many VB files stored in the file RMEJMOD.xls. The file is currently stored in a Dropbox folder. The path to this folder is different in the home W7 laptop than the W10 PC at work.  The macros used from this file called from all buttons I created in my workbooks work seamlessly but not in the custom ribbon I created. The path appears when you hover the mouse over the macro name in Customize Ribbon. It appears that the ribbon calls carry the full path to the macro and therefore will not execute when I import the ribbon UI from the other machine even when updating the link location. When the macro is selected I get the popup "Sorry, Excel can't open two workbooks with the same name at the same time."

I could create a ribbon for each machine but I hope there is a better way.

I really need a solution for this as I use at least 2 dozen macros in my workbooks every day.

Any help will be greatly appreciated.

[#2]

I would suggest storing your macros in an add-in.  This will allow you to access them on both machines.  You will just need to install the add-in on both machines.  An Add-In can store all of your macros and your ribbon interface and allow you the ability to have access to them all of the time.  This would prevent you from having to call the macros from X or Y file path.

Here is a basic article about how to create an Add-In

https://trumpexcel.com/excel-add-in/

If you want to manipulate/customize the ribbon you'll need the UI Editor  There is also some useful information on Ron's site the involves customizing ribbons.... :

https://www.rondebruin.nl/win/s2/win001.htm

Another option would be to store your macros in the personal macro workbook and link them to the Quick Access Toolbar (QAT).  This would also allow you to store the macros locally and not have to open or call macros from an .xls file stored in X or Y file path.

About the Personal Workbook: https://chandoo.org/wp/using-personal-macro-workbook/

If you need additional help converting the macros to an Add-In or Personal Workbook after reviewing the above links please reach back out.  If you provide me the vba code in this post I'll be happy to create the .xlam add-in file for you and reply back with a link to the file (along with some instructions).

[#3]

@Matt Mickle 

Hello Matt,

I have made 40 odd macros which I have put into a ribbon with a dedicated tab.  These, I use for single tap commands using Excel's in-built Button faces for all my excel work.  Makes it super easy. 

I also have a macro to make safe copies of the VB modules which I extract from Personal.xlsb file and keep in a folder. In the same folder I export the customisation UI file as well.  So I am preserving my work  for reuse, but I am not succeeding in that.

When I have to transfer these to another computer and I try to use the Import Customisation UI, the button faces and Quick access tool bars are restored quite well.  I can even see the button still carrying the information of attached macro. But the modules are not carried forward with the customisation file and therefore the buttons become defunct.

I have also tried to work around by manually importing the code modules into Personal.xlsb file.  However the message "Excel can't open two files of same name at the same time" keeps appearing. Everything seems to be well in place, yet the process is broken.

I feel frustrated having to all macros one by one make all buttons all over again. I reformatted my computer today and have to do the mammoth task all over again. I have been wanting to do similar customisations on my staff computers but its 2 hours of labour per computer..

Why does it have to be so.. is there a way around it. ?