Forum Discussion
Customized Ribbons, Macros, & Copying Files
I run a small business with a big honkin' spreadsheet with a dozen or so worksheets inside. To facilitate the operations with this spreadsheet, I've created a couple dozen macros to navigate within the spreadsheet, to create reports, to create statements for clients, and so forth.
Every year, when it's time to do the data processing, I copy the previous year's spreadsheet & rename it for the current year. The good news: when I copy "2017" to "2018", the macros get copied with it. If I go to Developer --> Macros, I find the macros associated with the "2018" file. Also, in some spots, I have placed buttons inside a worksheet, and when pressed, those buttons refer to the "2018" macros.
The bad news: I've created a customized ribbon to hold all the macros. The problem is that when I copy "2017" to "2018", the customized ribbon in "2018" refers to the macros in "2017". Thus, when I click on a button on the customized ribbon, Excel opens "2017" and executes the macro in the "2017" file. This is a big problem:
- Both spreadsheets look alike (in thumbnails), and it's easy for me to make "2018" changes to the "2017" file.
- Sometimes I have to tweak the macros, but I want the 2017 macros to stay unchanged, so that I could revisit "2017" if I needed to.
- It's a big bother updating the customized ribbon. I have to delete the old button & reimplement to new button, and with so many buttons on the ribbon, it's a long & tedious effort.
So, my question is: Can I implement a customized ribbon & tell it to refer to the local macros? Therefore, when I copy the spreadsheet at the beginning of the year, the ribbon will automatically refer to the local macros?
Thanks.
- 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
7 Replies
- Yes you can create a ribbon INSIDE the file itself, but it isn't really simple. See: http://rondebruin.nl/win/section2.htm for lots of examples.
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 file
