Customize Quick Access Toolbar Images (Excel 2007-2013)

Customize Quick Access Toolbar Images (Excel 2007-2013)

from Ron De Bruin

On this page I show you how to add built-in controls to the QAT, you will notice that there are controls that have no image and will show a green circle when you add them to the QAT as you see in the screenshot, no problem if it is one but a few of them is not what we want.

If you select “Macros” In the “Choose commands from” dropdown and add a macro button to the QAT you are able to use the Modify button to choose another image but you do not have this option for built-in buttons in the User Interface.

Excel saves your QAT setup in a file named “Excel.qat” and in Win XP you will find it here :
C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\Excel.qat

In Vista and in Windows 7 you can find it here :
C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat

Note: This file will not exist if your QAT is not customized.

 

Change QAT button image

Editing the Excel.qat to change images is no good option so I will not show you how to do this.

Reason:The Excel “Customize the QAT” 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 to/from the QAT,
the imageMso attributes will disappear.

The only good option for all workbooks is this:

Create a workbook named “OneWorkbookQAT.xlsm” and after you customize the QAT for this workbook save/close “OneWorkbookQAT.xlsm”. Note: In the “Customize Quick Access Toolbar” dropdown choose “For OneWorkbookQAT.xlsm” when you add the controls that you want in the QAT. Excel 2007-2003 files are really Zip files so we 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. You can also use another third party Zip program, for example the free 7-Zip.

Why ? : Much easier, because you not have to change the extension and can edit the file in the zip.

http://www.7-zip.org/

After you install 7-Zip you can do this.

1: Right click on “OneWorkbookQAT.xlsm”
2: Choose 7-Zip > Open archive
3: Right click on the file named “customUI.xml” in the “userCustomization” folder and choose “Edit”
Or select the file and press F4
4: Edit the file and save and close the “customUI.xml“ file
5: Say YES to update the archive

But in the example below I use the default Windows Zip program.
After you change the extension to zip of the Excel file and open the zip file you see this.

It will save the QAT setup in the “userCustomization” folder in a file named “customUI.xm””
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 see this line for every control:

<mso:control idQ=”mso:PageBreakInsertExcel” visible=”true”/>

You can add the image with imageMso in this line

<mso:control idQ=”mso:PageBreakInsertExcel” imageMso=”M” visible=”true”/>

Then save and close the file and 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 built-in images, visit this page to find the names of all the imageMso names : Ribbon Examples files and Tips

Save as add-in

Save “OneWorkbookQAT.xlsm” now as a Excel Add-in (name it MyQAT.xlam for example). This way you will not have the problem of your images disappearing when you Add, Remove or Reset your QAT.
Close and reopen Excel and Use the shortcut Alt ti or click the Microsoft Office button(2007) or File(2010-2013), click Excel options(2007) or options(2010-2013), click the Add-Ins tab. In the Manage drop-down, click Excel Add-ins, and click Go.

Use “Browse” to select the add-in and then click on OK.
Verify MyQAT is checked in the add-in list and then click OK.

Now every time you start Excel it will load this add-in with your custom QAT.

 

QAT Add-in

Note: This is the first version of the add-in, suggestions and bugs are welcome.

If you have made a few different customized QAT’s in your workbooks this add-in is maybe useful to copy a QAT from one workbook to another. There is a button on the Add-Ins tab to open the userform.

Download version 1.0 of the RDBQAT add-in

Leave a Reply

Your email address will not be published. Required fields are marked *