Transferring Ribbon Customizations

by Allen Wyatt
(last updated February 24, 2018)

8

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.

Working with the Quick Access Toolbar

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:

  1. Click the File tab on the ribbon.
  2. Click Options. Excel displays the Excel Options dialog box.
  3. At the left side of the dialog box click Quick Access Toolbar. (See Figure 1.)
  4. Figure 1. The Quick Access Toolbar options of the Excel Options dialog box.

  5. Click the Import/Export drop-down list at the bottom-right corner of the dialog box. Excel displays two options.
  6. Choose Export All Customizations. Excel displays the File Save dialog box.
  7. Using the controls in the dialog box, select a location where you want the backup file saved.
  8. Click Save. Excel saves the customization file where you specified in step 6.
  9. Click on Cancel to dismiss 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:

  1. Make sure that the macro for your colleague is stored in a new workbook.
  2. Save the workbook as a macro-enabled workbook, as you normally would.
  3. Display the Excel Options dialog box. (In Excel 2007 click the Office button and then click Excel Options. In Excel 2010 and later versions display the File tab of the ribbon and then click Options.)
  4. At the left side of the dialog box click Quick Access Toolbar.
  5. At the upper-right corner of the dialog box, use the Customize Quick Access Toolbar drop-down list to choose the workbook that contains your macro. (This should be the workbook you want to share with your colleague.)
  6. Use the controls in the dialog box to add the macro to the QAT.
  7. Close the Excel Options dialog box.
  8. Save the workbook and share it with your colleague.

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.

Working with the Ribbon

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:

https://msdn.microsoft.com/en-us/library/office/ee704589

Creating an Add-In

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:

  • Create an "auto open" macro that would modify the user's interface when the workbook is opened. This macro would need to be in the same workbook as the macro Ian created for the colleague.
  • Convert the workbook into an add-in.

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:

https://excelribbon.tips.net/T008528

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.

Author Bio

Allen Wyatt

With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author. He is president of Sharon Parq Associates, a computer and publishing services company. ...

MORE FROM ALLEN

Shortcut for AutoCorrect Dialog Box

There is no built-in keyboard shortcut that will display the AutoCorrect dialog box. This doesn't mean that there aren't ...

Discover More

Hyphenating a Selection

Word provides a hyphenation tool that can help you hyphenate words within a document. If you want to apply hyphenation to ...

Discover More

Adding a Drop Shadow to a Text Box

Drop shadows add a nice touch to text boxes, making it seem like they are hovering above the page. Here are the simple ...

Discover More

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!

More ExcelTips (ribbon)

Selected Cells Not Shaded

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

Setting the Calculation Default

Excel can recalculate your worksheets either automatically or manually. The default is to calculate them automatically, ...

Discover More

Turning Headers On and Off

Normally Excel displays row and column headers in a worksheet. If you prefer, you can turn these navigational aids off ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is four more than 2?

2018-03-10 11:10:29

John Mann

@Michael (Micky) Avidan

I would agree that using Freeze Panes is a possible solutioin to my second problem - but not always.

I have worksheets where Freeze Panes would work perfectly to keep a macro button visible, but I also have other worksheet where it wouldn't be a very desirable solution. On those sheets, the cell(s) above and to the left of the freeze boundary contains important headings or logos. While I suppose it would be possible to use the logo as a button, it would not allow me the ability to put instruction text on the button. On the worksheet I on which I have placed a button to trigger a macro, I have inluded text "Click to clear data" (the macro clears data from a range of cells), and I have also included a note about the keyboard short cut which will also run the macro.

Going back the the original topic of this thread, I have extensively modified my QAT in an attemtp to partially make up for the absence of proper tool bars. The lack of proper, context sensitive toolbars is on of the reasons I sometimes us either Lotus Smart Suite (I have the last version) or Open Office, for spreadsheet or workproccing.


2018-03-04 16:21:32

Michael (Micky) Avidan

@John Mann,
Your second "problem" is not a problem at all.
Try to add 3-4 empty rows at the top of the sheet and place the commndbutton in this empty area.
Now, all you need is to Pane Freeze those 3-4 rows (and some columns) in order to have the Button available all the time.
----------------------------
Michael (Micky) Avidan
“Microsoft® Answers" - Wiki author & Forums Moderator
“Microsoft®” Excel MVP – Excel (2009-2018)
ISRAEL


2018-03-03 15:58:29

John Mann

The problem with putting a button on a worksheet is at least two-fold.
First, it's only on one sheet, so if your workbook has several worksheets, you need to duplicate the button on each of them
Second, when you scroll around the worksheet, the button will move around also, and will soon disappear off the screen. Adding a keyboard short cut - if remembered - will get around that problem.
I do use a button to run a macro on one of my worksheets, but it has only a few rows and columns so scrolling off screen is not an issue.


2018-02-28 07:14:48

Terence

Wouldn't it be easier to just apply the macro to a button with the worksheet, still has the same ease of access (for the most part) and doesn't require any QAT adjustments.
If the other user uses Excel a lot they likely have their own custom QAT and have memorised the shortcut keys to access it so manipulating that might not be the best idea.


2018-02-26 20:03:35

Ruthie

Never mind. I should have read farther into the article before posting!


2018-02-26 20:01:36

Ruthie

Copying a QAT sounds like it might be problematical. Wouldn't overwriting my QAT with someone else's QAT erase my existing customizations?


2018-02-25 10:01:09

Allen

Willy,

The method you suggest (and prefer) is actually mentioned in the above tip. (See the paragraph beginning "Of course, you may want to consider modifying how you actually made the changes to the Quick Access Toolbar.")

I had to address other ways because it was not entirely clear from Ian's situation if his customizations were solely to the QAT or if they were to the ribbon itself, as well.

-Allen


2018-02-24 08:36:46

Willy Vanhaelen

In my opinion, the simplest and safest method is to add a button to the QAT and specify to save it with the current workbook instead of "For all documents (default)". This way the buton will also additionaly show in that workbook's QAT on all other systems without changing anything else on their ribbon customasations.

"Working with the Ribbon" is also a good alernative but it is a little more complicated.

I don't see how an Add-In could solve this. As far as I know, add-ins have no special abilities of specifying what is displayed in the QAT.

(see Figure 1 below)


Figure 1. 




This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.