by Allen Wyatt
(last updated February 24, 2018)
Ian created a macro and then customized the ribbon with a button that executes that macro. (Works great!) He transferred the workbook into Dropbox so that a colleague could use it. When she opened the workbook, the button Ian had added to the ribbon was missing. He wonders why this occurs and how he can get around it.
The answer to this question depends, in large part, on how you made the customizations to your version of Excel.
If your customizations involved simply adding the macro to the Quick Access Toolbar, those changes are not stored in a particular workbook like macros are; they are stored by Excel in what is referred to as a QAT file. How you work with a QAT file depends on the version of Excel you are using. You can, if desired, simply use Windows to search for any file ending in a QAT extension. If both you and the colleague are using Excel 2007, you can simply copy the QAT from your system to the colleague's system, making sure it overwrites whatever QAT file may exist on that system.
If you are using Excel 2010 or later and your colleague is using the same version of Excel as you, then you should follow these steps:
Figure 1. The Quick Access Toolbar options of the Excel Options dialog box.
The file created in step 7 is your export file. You can copy it to Dropbox, and the colleague can use the same steps listed above, with the caveat that he or she should import in step 5, making sure that your export file is what gets imported.
Of course, you may want to consider modifying how you actually made the changes to the Quick Access Toolbar. The latest versions of Excel allow you to specify that a Quick Access Toolbar change be saved with a particular workbook. So, for instance, you could save the change with the same workbook that contains the macro—the same workbook you are saving to Dropbox and wanting your colleague to use. To do that, follow these general steps:
The QAT customization is now only active when the workbook that contains that customization is active. (The key is the selection you make in step 5.
If your changes were not actually made to the Quick Access Toolbar but were, instead, made to the ribbon itself, then the above won't really help. Instead, you'll need to follow a more detailed series of steps that involve the transfer and installation of what is called an OfficeUI file. You can find a lot of in-depth info on transferring this file here:
The problem with any of the solutions presented so far is that they have the likelihood of overwriting any customizations that Ian's colleague had already set up for his or her system. In other words, Ian's customizations wouldn't be added to those customizations; they would replace those customizations. This has the potential of making Ian's colleague quite grouchy.
The best solution may be for Ian to create an add-in that makes the macro available and, in doing so, makes changes to the user's interface. Ian would need to do two things:
This may seem a bit of overkill, but it isn't that difficult. (Honestly, the macro to modify the user interface might be the hardest, but that level of difficulty depends on how extensive the changes are that you want the macro to make.) Doing the conversion into an add-in isn't that difficult at all; you can find details here:
Once the add-in is done, then the colleague can install and activate the add-in, regardless of the version of Excel he or she is using.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (7790) applies to Microsoft Excel 2007, 2010, 2013, and 2016.
Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!
Does the pale coloring scheme used to mark selected cells by Excel 2007 drive you nuts? You aren't alone. Here's an idea ...Discover More
Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...Discover More
Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off ...Discover More
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.