Forum Discussion
Customized Ribbons, Macros, & Copying Files
- You can export your customisations to a file, edit that file in a text editor and after that re-import the file:
- Open Excel
- Right-click ribbon and click "Customize the Ribbon..."
- Click the "Import/Export" button
- Choose "Export All Customizations"
- Save to a location of your liking
- Close Excel
- Find that file
- Edit a copy of the file just created (use Notepad), changing the location of your macros (look for items starting with "onAction=")
- Save file
- Open Excel
- Right-click ribbon and click "Customize the Ribbon..."
- Click the "Import/Export" button
- Choose "Import Customization File"
- Select the edited file
ALternatively, you could consider placing buttons on a worksheet pointing to your macros. Those should remain pointing to the macro's inside the file itself.
I'll check the link in more detail, but at first glance, I don't understand it.
The "collection of buttons" solution isn't appealing to me. As I've mentioned, there are a dozen worksheets in the file, and I need the macros available on all worksheets. Plus, there isn't a lot of screen space available for the roughly two dozen macros that are in the ribbons. Finding space for a set of buttons would mean a significant reorganization of all the worksheets & a re-implementation of parts of the macros.
I wonder if I can create a macro to update the custom ribbon after I copy the file?
- You can export your customisations to a file, edit that file in a text editor and after that re-import the file:
- Open Excel
- Right-click ribbon and click "Customize the Ribbon..."
- Click the "Import/Export" button
- Choose "Export All Customizations"
- Save to a location of your liking
- Close Excel
- Find that file
- Edit a copy of the file just created (use Notepad), changing the location of your macros (look for items starting with "onAction=")
- Save file
- Open Excel
- Right-click ribbon and click "Customize the Ribbon..."
- Click the "Import/Export" button
- Choose "Import Customization File"
- Select the edited fileI wouldn't call this solution "elegant", but it works. Thank you.
Note: I found an "onAction" entry for each of the buttons on my customized ribbon, and when I copied the exported customization file, I struggled at first, but then figured out that I could do a "replace all" of the "2017" file name with the "2018" file name. In all, I replaced about 20 occurrences. Then, I opened the "2018" file, imported the edited "2018" customization file, and everything worked. Bonus: I can keep the customizations for each of the different versions of the spreadsheet, making it easy to recreate reports from previous years.
Ideally, Microsoft will consider an option to take care of this -- perhaps an option or checkbox available when creating a button on a customized ribbon that references a macro?
- MSFT have considered an option to take care of this: adding ribbon customizations to files. So rather than having to update your general ribbon customizations (which is what the above process does), you avoid all this hassle by adding the customisations to the file in question. Those customizations then only appear when you open that file.