Customized Ribbons, Macros, & Copying Files

[Jon Cohen]

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.

[#2]

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.

[#3]

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?

[#4 · Accepted solution]

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

[#5]

I 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?

[#6]

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.

[#7]

That would work for me.  To me, it's inconsistent to associate macros to a file and not the customization.

I think it should be an option -- sometimes, I might want some customization to be available for all spreadsheets, but in the case of my small business spreadsheet, I'd want the customization to be associated with the file (so that it gets copied when the file gets copied).

[#8]

Well, there you go, but the only way to do so is by using the methods described on Ron de Bruin's website.