Note: The content on this page applied to Excel 2002 and 2003.

This page lists some of the custom toolbar buttons that I use all the time. There are some sites that have free Excel utility addins that provide various toolbars and buttons (see the references below). I created this page mainly to give you the flexibility of adding buttons as you see fit rather than installing someone else's addin. The button images and code on this page are free for general use, but cannot be sold or included in other software for sale.

Toolbar Button Images for Existing Commands

To use these images:

  1. Right-click on an image and select Copy.
  2. In Excel, go to Tools > Customize ...
  3. Find the command you wish to create a button for (eg. Format > Conditional Formatting)
  4. Right-click on the command button (or select the button and click on Modify Selection)
  5. Select Paste Button Image...

To preserve transparency in GIF images ...

  1. Download the images and save them somewhere on your computer.
  2. Insert the GIF file into Excel (Insert > Picture > From File).
  3. Select the image when you have it in Excel and copy it (Ctrl+c)
  4. Follow steps 2-5 in the instructions above to paste the button image.

Assuming you exit out of Excel successfully, changes made to toolbars are saved in your Excel.xlb file somewhere in your Documents and Settings (eg. C:\Documents and Settings\username\Application Data\Microsoft\Excel). By the way, if your toolbar ever gets completely messed up, you can exit out of Excel and delete this file to change all the toolbars back to your default install settings.

Image Command Description
conditional formatting toolbar button Open the Conditional Formatting dialog box.
  The Conditional Formatting command is found in the Format category.
named range toolbar button Open the Define Name Dialog Box
  The command to open up the define name dialog box is Insert > Names > Define .... To my knowledge, the actual command does not exist in any of the command categories, but you can drag and drop the command from the existing toolbar menu. Just make sure you press Ctrl as you drag the menu item (Define...) so that you copy the button rather than move it.
data validation toolbar button Open the Data Validation Dialog Box
  The Validate command is found in the Data category.

Adding Custom Macros to an Excel Toolbar

Before adding these custom buttons, you should create your own personal add-in. Don't worry, this is a piece of cake.

  1. Open up a new workbook.
  2. Start up VBA. (Press Alt+F11)
  3. Insert a new module. (Insert > Module)
  4. Copy and paste the VBA code into the module.
  5. (Take note of what the macro is called).
  6. Get out of VBA. (Press Alt+Q)
  7. Save your workbook as an addin (.xla) file, calling it something like My_Addin.xla
  8. Exit out of Excel, then start it up again.
  9. Enable your new addin (Go to Tools > Add-Ins and select your new addin).

Creating a new macro button in a toolbar.

  1. Go to Tools > Customize
  2. In the Commands tab, click on the Macros category.
  3. Copy (drag and drop) the Custom Button to one of your toolbars.
  4. Right-click on the button and select Default Style to display only the image.
  5. Copy and paste the button image as described above.
  6. Right-click on the button and select Assign Macro ...
  7. Type the exact name of the macro and select OK

Word Wrap Toolbar Button


Image Command Description
word wrap toolbar button Toggle word wrap within a selected cell or range of cells.

Sub WordWrapBtn() 
' Word Wrap Button Macro 
    Dim btnstate As Boolean 
    btnstate = Selection.WrapText 
    With Selection 
        If btnstate Then 
        .WrapText = False 
        Else 
        .WrapText = True 
        End If 
    End With 
End Sub

Custom Cell Format UserForm

This macro requires that you first download the code for the form and then import it into VBA. Below is a screenshot of the form. It is especially useful for formatting text that involves symbol fonts, subscripts, italics, bold, etc. Note that you cannot delete or add characters within the form.

format cell form
  1. Download the following two files, FormatCell.frm FormatCell.frx and save them in some convenient temporary location on your computer. You might want to right-click on the file and select Save Target As...
  2. Open up your custom add-in in VBA.
  3. In the project window, make sure you have your add-in selected (My-Addin.xla) and not the current workbook.
  4. Go to File > Import File and select the FormatCell.frm file.
  5. That's it for the form. Now add the macro listed below (OpenCellFormatForm) into a new or existing module.
  6. Create a command button and assign the OpenCellFormatForm macro to it.
Image Command Description
format cell toolbar button A custom cell text format dialog box.

Public Sub OpenCellFormatForm() 
    FormatCell.Show 
End Sub

References

  • Excel Toolbar Info - http://www.mvps.org/dmcritchie/excel/toolbars.htm - The low-down on toolbars in Excel.

Disclaimer: This article is meant for educational purposes only.

Related Content