Add Buttons to the Quick Access Toolbar and Customize Button Images
Today’s author is Ron de Bruin, an Excel MVP. You can find more useful tips from Ron and links to Excel add-ins at his website: http://www.rondebruin.nl/
This post shows you how to add a button to the Quick Access Toolbar (QAT) for one or all workbooks. It also shows an example of how you can change the image of a QAT button. One reason you might want to change a button’s image is that a lot of commands use the same button image (a green circle).
For more information about adding missing built-in commands to the QAT or Ribbon see: http://www.rondebruin.nl/notinribbon.htm
How do you Customize the QAT for All Workbooks?
Right-click on the QAT in the upper right corner of the Excel 2007 window, and then click Customize Quick Access Toolbar:
In the Choose commands from dropdown make your choice, select the command you want, and then click Add:
You can add buttons for all your favorite commands to the QAT this way.
Tip: You can also right-click on a control in the Ribbon, and then click Add to Quick Access Toolbar.
Example:
In the example shown above, I chose Commands Not in the Ribbon in the Choose commands from dropdown list. I then added the Add-ins command to the QAT. After doing so, you should see that your QAT looks like this now:
If you select Macros in the Choose commands from dropdown, and then add a button for a macro to the QAT, you can use the Modify button to choose one of the 181 images available when using this dialog (there are a total of 1871 images, but more about that later). However, you do not have this option for the built-in commands from the user interface, such as the Add-ins command.
Excel saves your QAT setup in a file named Excel.qat.
In Windows XP you will find it here:
C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat
In Vista you can find it here:
C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat
Note: The Excel.qat file will not exist if you have not customized your QAT.
Tip: You can right-click on the green circle button for the Add-ins command, and then click Remove from Quick Access Toolbar to delete it.
How do you Customize the QAT for One Workbook?
You have the option to add commands to the QAT for all workbooks or only for one workbook. When you do this for all workbooks, Excel will save the setup in the Excel.qat file as described above.
What if you want to customize the QAT for one particular workbook? Excel will not save this in the Excel.qat file, but will save the QAT setup in the userCustomization folder in a file named customUI.xml inside the workbook file.
You can read more about this in the example for Change QAT Button Image below.
Example:
Create a new file and save it as OneWorkbookQAT.xlsm
Right-click on the QAT, and then click Customize Quick Access Toolbar.
In the Customize Quick Access Toolbar dropdown, select For <workbook name>.
As in the previous example, I chose Commands Not in the Ribbon in the Choose commands from dropdown, and then added the Add-ins command to the QAT. You should see that your QAT looks like this now:
Note: The new Add-ins command button with the green circle is only visible when the OneWorkbookQAT.xlsm workbook is active.
Just like customizing the toolbar for all workbooks, if you select Macros in the Choose commands from dropdown, and then add a macro button to the QAT, you can use the Modify button to choose one of the 181 images, but you do not have this option for built-in commands from the Excel user interface.
Excel will save the setup in the userCustomization folder in a file named customUI.xml inside the OneWorkbookQAT.xlsm file. This folder will not exist if you have not customized the QAT for the workbook. You can read more about this in the example for Change QAT Button Image below.
Change QAT Button Image
Changing the QAT Images for All Workbooks
Editing the Excel.qat to change images is not good option, so I will not show you how to do this.
Reason: The Excel Customize the Quick Access Toolbar feature does not preserve manual changes made to the Excel.qat file. So, if you subsequently use Excel to reset the QAT or to add or remove commands from the QAT, the imageMso attribute will disappear.
The only good option for all workbooks is this:
Use the example above to customize the QAT for one workbook. Then, after you add the buttons you want to the QAT, save and close the OneWorkbookQAT.xlsm file. Excel 2007 files are really Zip files, so you can change the extension to .zip and open it in a third party zip program or use the default Windows Zip program.
Important: I suggest that you make a backup copy of the original file just in case you break something.
Change the extension of OneWorkbookQAT.xlsm to .zip now and use the default Windows Zip program to open the zip file.
After you change the extension of the Excel file to .zip and open the .zip file you will see this:
Excel will save the QAT setup in the userCustomization folder in a file named customUI.xml.
Note: This folder will not exist if you have not customized the QAT for this workbook.
Drag and drop this file out of the zip file on your desktop and open it in Notepad or another XML editor and you will see this:
<mso:customUI xmlns:mso="http://schemas.microsoft.com/office/2006/01/customui">
<mso:ribbon>
<mso:qat>
<mso:documentControls>
<mso:control idQ="mso:AddInManager" visible="true"/>
</mso:documentControls>
</mso:qat>
</mso:ribbon>
</mso:customUI>
You can change this line:
<mso:control idQ="mso:AddInManager" visible="true"/>
To this:
<mso:control idQ="mso:AddInManager" imageMso="M" visible="true"/>
Then save and close the file, drag and drop it back into the zip file, and say Yes to replace the file. Now close the .zip file, and change the extension back to .xlsm.
If you open the workbook you see that the green circle is changed to an M now.
Note: You can only use the built-in images provided by Excel. There are a few downloads on my ribbon page (see point 5 on that page) that will help you find the names of button images: http://www.rondebruin.nl/ribbon.htm
Perform the following steps to make this customization available for all workbooks.
- Save the OneWorkbookQAT.xlsm file as an Excel Add-in. To do that, click the Office Button, click Save As, and then select Excel Add-in (*.xlam) from the Save as type dropdown.
- Name the file MyQAT.xlam.
- Close and reopen Excel, click the Office Button, click Excel Options, and then click the Add-Ins tab.
- In the Manage drop-down, click Excel Add-ins, and then click Go. If you don’t see MyQAT in the Add-ins available list, use the Browse button to locate the add-in, and then click OK.
- Verify that MyQAT is checked in the Add-ins available list, and then click OK.
Now, every time you start Excel it will load this add-in with your custom QAT. This way you will not have the problem of your images disappearing when you add, remove, or reset the commands on your QAT.
Copy a Customized QAT from one Workbook to Another (Add-in)
If you have made a few different customized QAT's in your workbooks this add-in may be
useful to copy a QAT from one workbook to another. There is a button on the Add-Ins tab to open the user form shown below.
Download version 1.0 of the RDBQAT add-in
Note: This is the first version of the add-in, suggestions and bug reports are welcome.
Check out this page once in a while to see if there is a update.
Many thanks to
Jim Rech for his suggestions and testing.
More Information
Change the ribbon in Excel 2007
http://www.rondebruin.nl/ribbon.htm
There are additional links in the More Information part of that page.
There is a bug in SP2 when you add a button for one workbook. I have a few notes and a workaround on this page: http://www.rondebruin.nl/qatbuttonbug.htm
