I am running Excel 2010 and creating macros in VBA. I created custom buttons on the Ribbon and assigned my VBA macros to the buttons using the built-in Customize Ribbon tool (accessed by right-clicking the ribbon and selecting "Customize the Ribbon..." )
Here is the problem: The VBA macros which are assigned to the ribbon buttons have absolute paths, not relative paths. Therefore, if I change the name of the Excel file, open it, then try to use any of my custom ribbon buttons, I get the error message: "Cannot run the macro <macro name>. The macro may not be available in this workbook or all macros may be disabled." In other words, Excel is looking for the macros in the file with the OLD name, which no longer exists.
Also, if I move the Excel file to another directory, I get the same error message when pressing any of my custom ribbon buttons. Again, this is because the macro is assigned with an absolute path name, and the file it is looking for does not exist because I moved the file.
This is easily verified by opening the Customize Ribbon tool. On the left side, the available macros are listed. If I hover the cursor over my macros, a popup balloon shows they are in the currently open Excel file directory. But if I hover the cursor over the assigned macros on the right side of the window, the balloon shows the macros are in the now non-existent file.
I need to be able to re-name and/or move my Excel file and still have my custom buttons work without re-assigning all of them. Any ideas?
Here is the problem: The VBA macros which are assigned to the ribbon buttons have absolute paths, not relative paths. Therefore, if I change the name of the Excel file, open it, then try to use any of my custom ribbon buttons, I get the error message: "Cannot run the macro <macro name>. The macro may not be available in this workbook or all macros may be disabled." In other words, Excel is looking for the macros in the file with the OLD name, which no longer exists.
Also, if I move the Excel file to another directory, I get the same error message when pressing any of my custom ribbon buttons. Again, this is because the macro is assigned with an absolute path name, and the file it is looking for does not exist because I moved the file.
This is easily verified by opening the Customize Ribbon tool. On the left side, the available macros are listed. If I hover the cursor over my macros, a popup balloon shows they are in the currently open Excel file directory. But if I hover the cursor over the assigned macros on the right side of the window, the balloon shows the macros are in the now non-existent file.
I need to be able to re-name and/or move my Excel file and still have my custom buttons work without re-assigning all of them. Any ideas?
