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
Excel 2007 saves your QAT setup in a file named "Excel.qat" and from Excel 2010 it save it in the Excel.officeUI file, in Win XP you will find it here :
C:\Documents and Settings\<username>\Local Settings\Application Data\Microsoft\OFFICE\
In Vista and in Windows 7 you can find it here :
Note: This qat or officeUI file will not exist if your QAT is not customized.
Editing the qat or officeUI file to change images is no good option so I will not
show you how to do this on this page.
Reason:The Excel "Customize the QAT" feature does not preserve manual changes made to the qat or officeUI 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-2016 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.
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
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 "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-2016), click Excel options(2007) or options(2010-2016), 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.
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