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.
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
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.
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).