Add buttons to the QAT and customize the images of the buttons
Note:
On this page you can learn how to add a button to the QAT for one or
all workbooks.
And a example how you can change the image of a QAT button. One of the reasons why you want
to change a image is that a lot of commands use the same image(green circle).
If you want to have more info 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 and choose "Customize Quick Access Toolbar".
In the “Choose commands from” dropdown make your choice.
Then select the button you want and press “Add”
You see that you can add all your favorite buttons to the QAT this way.
Tip: You can also right click on a control in the Ribbon and choose "Add to Quick Access Toolbar"
Example:
We choose “Commands not in the Ribbon” in the “Choose commands from” dropdown.
Then we add the “Add-ins” button to the QAT and you see that your QAT looks like this now.
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 one of the 181 images (there are 1871 images) 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 you can find it here :
C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat
Note: This file will not exist if you have not customized your QAT
Tip: Right click on the green circle button and choose "Remove from Quick Access Toolbar" to delete it.
How do you customize the QAT for one workbook
You have the option to add buttons to the QAT for all workbooks or only for one workbook.
For all workbooks you have read that it will save the setup in the “Excel.qat” file.
What If you want to change the QAT for one certain workbook. It will not save this in the “Excel.qat” file
but save the QAT setup in the “userCustomization” folder in a file named “customUI.xml”
You can read more about this in the example "Change QAT button Image".
Example:
Create a new file and save it as "OneWorkbookQAT.xlsm"
Right click on the QAT and choose "Customize Quick Access Toolbar"
In the “Customize Quick Access Toolbar” dropdown choose “For <workbook name>”.
We choose “Commands not in the Ribbon” in the “Choose commands from” dropdown.
Then we add the “Add-ins” button to the QAT and you see that your QAT looks like this now.
Note: The new Add-ins button with the green circle is only visible when your workbook is active.
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 one of the 181 images (there are 1871 images) but you do not have this option for built-in buttons in the User Interface.
It will save the setup in the “userCustomization” folder in a file named “customUI.xml” inside the
"OneWorkbookQAT.xlsm" file. You can read more about this in the example "Change QAT button Image".
Note: this folder will not exist if you have not customized the QAT for this workbook.
Tip: Right click on the green circle button and choose "Remove from Quick Access Toolbar" to delete it.
Change QAT button image
QAT for all workbooks
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 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 and after you add the buttons you want to
the QAT save/close “OneWorkbookQAT.xlsm”. Excel 2007 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 this example 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.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 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
<mso:control idQ="mso:AddInManager" 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
See the link on the bottom of this page to find the names of all images.
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. Office Button>SaveAs.....Excel Add-in(*.xlam)
Close and reopen Excel and click on the Microsoft Office button, click Excel Options, click the Add-Ins tab.
In the Manage drop-down, click Excel Add-ins, and click Go. Use “Browse” to go to 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.
Copy a customized QAT from one workbook to another(Add-in)
Note: This is the first version of the add-in, suggestions and bugs 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.
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
How do I find the names of the button images
There are a few downloads on my ribbon page (point 4) that will help you find the names.
http://www.rondebruin.nl/ribbon.htm
More Information
Change the ribbon in Excel 2007
http://www.rondebruin.nl/ribbon.htm
There are more links in the "More Information" part of that page
And a example how you can change the image of a QAT button. One of the reasons why you want
to change a image is that a lot of commands use the same image(green circle).
If you want to have more info 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 and choose "Customize Quick Access Toolbar".
In the “Choose commands from” dropdown make your choice.
Then select the button you want and press “Add”
You see that you can add all your favorite buttons to the QAT this way.
Tip: You can also right click on a control in the Ribbon and choose "Add to Quick Access Toolbar"
Example:
We choose “Commands not in the Ribbon” in the “Choose commands from” dropdown.
Then we add the “Add-ins” button to the QAT and you see that your QAT looks like this now.
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 one of the 181 images (there are 1871 images) 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 you can find it here :
C:\Users\<username>\AppData\Local\Microsoft\Office\Excel.qat
Note: This file will not exist if you have not customized your QAT
Tip: Right click on the green circle button and choose "Remove from Quick Access Toolbar" to delete it.
How do you customize the QAT for one workbook
You have the option to add buttons to the QAT for all workbooks or only for one workbook.
For all workbooks you have read that it will save the setup in the “Excel.qat” file.
What If you want to change the QAT for one certain workbook. It will not save this in the “Excel.qat” file
but save the QAT setup in the “userCustomization” folder in a file named “customUI.xml”
You can read more about this in the example "Change QAT button Image".
Example:
Create a new file and save it as "OneWorkbookQAT.xlsm"
Right click on the QAT and choose "Customize Quick Access Toolbar"
In the “Customize Quick Access Toolbar” dropdown choose “For <workbook name>”.
We choose “Commands not in the Ribbon” in the “Choose commands from” dropdown.
Then we add the “Add-ins” button to the QAT and you see that your QAT looks like this now.
Note: The new Add-ins button with the green circle is only visible when your workbook is active.
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 one of the 181 images (there are 1871 images) but you do not have this option for built-in buttons in the User Interface.
It will save the setup in the “userCustomization” folder in a file named “customUI.xml” inside the
"OneWorkbookQAT.xlsm" file. You can read more about this in the example "Change QAT button Image".
Note: this folder will not exist if you have not customized the QAT for this workbook.
Tip: Right click on the green circle button and choose "Remove from Quick Access Toolbar" to delete it.
Change QAT button image
QAT for all workbooks
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 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 and after you add the buttons you want to
the QAT save/close “OneWorkbookQAT.xlsm”. Excel 2007 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 this example 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.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 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
<mso:control idQ="mso:AddInManager" 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
See the link on the bottom of this page to find the names of all images.
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. Office Button>SaveAs.....Excel Add-in(*.xlam)
Close and reopen Excel and click on the Microsoft Office button, click Excel Options, click the Add-Ins tab.
In the Manage drop-down, click Excel Add-ins, and click Go. Use “Browse” to go to 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.
Copy a customized QAT from one workbook to another(Add-in)
Note: This is the first version of the add-in, suggestions and bugs 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.
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
How do I find the names of the button images
There are a few downloads on my ribbon page (point 4) that will help you find the names.
http://www.rondebruin.nl/ribbon.htm
More Information
Change the ribbon in Excel 2007
http://www.rondebruin.nl/ribbon.htm
There are more links in the "More Information" part of that page