How to use my custom icon in the MS Office 2016 Quick Access Toolbar?

Question

Is there a way to use a custom icon for the Quick Access Toolbar in Microsoft Office applications such as Excel and Outlook? It looks like Microsoft doesn't allow use of custom icons anymore. If so, perhaps there is a way to create a extension?

It's possible to choose a picture from the predefined gallery of icons, but I need many more pictures for the buttons.

Answer 1

1. Icons: as far as I understand, you can only get the stock MS Office icons (which is still a lot more than that measly default selection you're allowed to pick from). Choose the ones you need and write down their names from this Icons Gallery (e.g. the name of the icon with a zero on it is _0; there might be a more convenient alternative to find icon names, but this was sufficient for my needs of a few icons)

2. Editing QAT: Below you'll basically find an edited version of instructions from this awesome site Customize QAT Icons, where I originally found the solution . You'll need to create a workbook-specific QAT, add your custom macros there, manually edit the image names and then save this file as an auto-loading addin, so that your custom QAT is always loaded and your custom icons are not overwritten during edits of the default QAT. For simplicity I just removed the default QAT for all documents and have all my settings stored in this addin.

   • Create a workbook OneWorkbookQAT.xlsm
   • Go to File -> Options -> Quick Access Toolbar and on the right hand side under the option Customize Quick Access Toolbar select For OneWorkbookQAT.xlsm
   • Edit the (currently empty) QAT as usual, add whatever buttons/macros you want. Save file and close it.
   • Change extension of OneWorkbookQAT.xlsm to .ZIP and open it in WinRAR or something
   • Go inside the userCustomization folder and open the only file inside - customUI.xml. This file has all the QAT buttons you created earlier
   • Find the commands you want to change icons for and replace the name in the imageMso field to the name of the icon you want (from step 1 above). An example of one button in this file <mso:button idQ="doc:CycleCurrencyFormats_1" visible="true" label="Currency Style" imageMso="AccountingFormat" onAction="CycleCurrencyFormats"/>, where **AccountingFormat** is the name of the icon, which is a $ sign, same as the one you see for accounting number format (hence the name :)
   • Save all your changes, don't forget to click OK in the WinRAR popup dialogue, prompting you to overwrite your old file inside the archive.
   • Rename the file back to OneWorkbookQAT.xlsm
   • Open the file and Save As Excel Add-in in your addin folder (usually C:\Users\YOUR USERNAME\AppData\Roaming\Microsoft\AddIns), e.g. under the name MyQAT.xlam
   • Open Excel, press Alt-T-I to open the Add-in options and click on the Myqat checkbox to enable auto-loading of this addin with your customized Quick Access Toolbar

That's it, now every time you open Excel, this add-in will auto-load and add your custom QAT to the QAT available for all workbooks (if you haven't removed it)

TIP: if some of your buttons rely on macros, you'd need to copy all those macros to the OneWorkbookQAT.xlsm file for them to work, otherwise they won't be accessible to the workbook-only QAT.

Also, for future edits you either save the OneWorkbookQAT.xlsm and then just overwrite MyQAT.xlam or in VB editor change IsAddin property of ThisWorkbook Microsoft Excel Object to False so that the addin workbook becomes visible and you are allowed to edit the QAT.