Toolbars, Custom Buttons and Menus

Location:   http://www.mvps.org/dmcritchie/excel/toolbars.htm
Home page: http://www.mvps.org/dmcritchie/excel/excel.htm
[View without Frames]

The Excel that I used in writing this page was XL95 XL7 (Excel Version 7.0.A).  My system is Windows NT 4.0 Workstation.  I have since upgraded to XL2000 XL9 (Excel ver 9) and these instructions basically remain the same as for XL95.  The main difference is the picture of my toolbars, and simplified attachment of tooltip description to a button, other differences are noted with version symbols for XL2000.  I expect that anything marked as Excel 2000 XL9 also applies for Excel 97 XL8 and Excel 98 (Macintosh version).

Print your Excel window and retain a copy

The first suggestion here is to print an Excel screen to a sheet of paper and file it.  This way if anything happens to your toolbars it will help you restore or improve your settings.  It may turn out that the toolbars simply became rearranged or deactivated.
Make sure the EXCEL window is selected then use  ALT + PrintScreen.  Paste the window to the word pad or to MS Word.  Print the document and file the sheet with your Excel materials.

Loss of an entire section on the toolbars

A toolbar may be placed in the spreadsheet area (floating toolbar).  There are four docking areas.  A toolbar may be docked above the formula bar at the top, or above the status bar at the bottom.  There are two vertical docking areas, one to the left of the row numbers, and one the right of the of the scroll bars.  The vertical docking areas will not accept toolbars containing buttons with pull down menus (STD, MS 3.0, Format -- style, size, and font).

If a section of toolbars suddenly disappears, it is probably because you dragged them off the screen, unchecked the toolbar in the Toolbar window, or unchecked the toolbar on a toolbar pull down from any toolbar. 

To restore an individual toolbar    View --> Toolbars, put a check against the missing toolbar (fig. 1).  Refer to figure3 to identify each toolbar.

Identification of Toolbars Buttons (icons)

Use the [\?] button to identify a button. 

Immediate recovery of your toolbars(EMERGENCY Recovery)

Before continuing, Full Screen mode (F11) hides toolbars, hit F11 to reshow (also on View menu).

Any changes to your toolbars affect the appearance of all Excel books.  If you have not exited your Excel window you may restore by clicking on your *.XLB (toolbar) file in the directory containing your Microsoft Windows (c:/WINNT, ...).

Use MS Start --> FIND use *.XLB to locate something like EXCEL5.XLB (Win95), EXCEL8.XLB (Win97), or <user>8.XLB (network) or Administrator.XLB (WinNT).  You can open that file from the FIND window.  Restoration of the toolbars is immediately apparent.  [is BOOK.XLB in same category?]

For my own use in XL95 that was   c:\WinNT\Administrator.xlb
or in XL2000 it is   c:\WinNT\Profiles\Administrator\Application Data\Microsoft\Excel.xlb

Using the find will probably show up only one .XLB so it really should not be a problem, but here is another set of possiblities, this one oriented to version of Excel rather than system, which may be more correct, though Excel version, system, and network all appear to play a role.
  Excel 5: Excel5.xlb
  Excel 95: <UserName>.xlb
  Excel 97: <UserName>8.xlb
  Excel 2000: Excel.xlb in the user's Application Data area.

Backup your system

If you backup your system on a regular basis you can restore your toolbars later.  The above relies on your immediate attention.  With a backup you can restore over the damage later.

Builtin defaults for Toolbars

The builtin defaults can be seen in Figure 3, on another page (due to the large picture sizes).  Other versions may vary.  To restore a toolbar to it's builtin default use the RESET button in Toolbars.
View --> toolbars --> (select toolbar) --> [RESET] button

Title Bar Missing in Full Screen View


Full screen view does not have a title bar at top,
should have menu bar,
does not show toolbars,
does show sheet name tabs
does not show status bar at bottom

Because you may be missing somethings the shortcuts are also shown

View alt + v
     Full Screen Alt + u (toggle)

To close full screen view
     Close Alt + c

View Alt + v
     Tools Alt + t
        Customize Ctrl + c

Tools Alt + t
     Customize Alt + c

Menu messed up or missing

RClick on the menu or toolbar area (Alt+t)
Click on Customize (Alt+c)
Toolbars Tab on the Customize dialog,
near the end activate menus with
    [x] Worksheet Menu bar.

Another possibility is that the menus got docked at the sides or bottom of your spreadsheet to left of column numbers or to right of vertical scrollbars.  You should always see menu bars, if visible, at the top of full screen view -- View [Alt+V], Full Screen [Alt+U].

If the above does not work, try opening the .XLB file, and if that doesn't work rename the .XLB file so that Excel will be forced to create a default one.

Missing Builtin Menus

View --> Tool bars --> Customize    (shortcut RClick on menu bar)

Within the Customize dialog scoll down on left side to "Built-in Menus" then on the right side grab "File" (or whichever is mising) oand drag it to the first spot on the Excel "Worksheet Menu Bar".

The menus I see on my menu bar are:
     File, Edit, View, Insert, Format, Data, Tools, Window, Help

If you catch an error immediately before closing Excel, one method of recovery is to open the latest .XLB file you find on your system possibly named excel.xlb as previously describe in Emergency above.

If you've made real serious errors in defining menus and toolbars and want to start from scratch then delete you .xlb toolbar and Excel will recreate the default toolbars for you.  Probably not something to do unless you are starting completely over on a new machine or version of Excel and don't like what you currently have.

Missing Toolbars

Try View --> Toolbars -- the following should be checked on
     [x] Standard
     [x] Formatting
if you made up some of your own tool bars possibly also
     [x] Toolbar 1 - 3

Identification of Toolbars

If you look carefully you will see a line between sections of toolbars.  Tool bars may be identified by pointing the cursor to the sectional area surrounding a group of buttons.  Clicking will then reveal the name of the toolbar (docking area only) on the status line, which normally says "Ready".  Figure 2 indicates "Auditing Tools". 

Multiple toolbars

If you look carefully at your toolbars you will see a vertical line between sets of toolbars.  You can select an entire toolbar by clicking beside one of the buttons in the toolbar.  A toolbar can be dragged to any of the toolbar areas including the spreadsheet itself.  If the toolbar is dragged out of the Excel window it is removed, and can be restored from View --> Toolbars.

Restoring Toolbars

Toolbars are added to a docking area in the order that you check them off under toolbars.  If you uncheck all toolbars and hit [OK], then you check Standard, then Audit, then Formatting that will be their order.  You can move them around though.

Add or delete a toolbar button

Toolbars may be customized using View --> Toolbars --> Customize.

The pictures shown are for XL95 XL7, but all of the wording applies also to XL2000 XL9.

To add a button to a toolbar select one of the choices under customize, move the button to the toolbar you want to see it in.  Click on an button in customize and look in lower left corner of customize window for it's description.

Removing a button from a toolbar is done from within the view toolbars, simply move the button out of the toolbar.  It can be moved to another toolbar.

If a button is dragged off a toolbar during customize it can only be restored from an existing toolbar or the builtin pattern toolbars.  It is for this reason that you should store originals in a hidden toolbar.

 
  figure 1 -- Toolbars
figure 1 -- Toolbars (View --> Toolbars)

Add or delete a toolbar button 
  1. Display the toolbar you want to change, and then click Toolbars on the View menu.
  2. Click Customize.
  3. To add a button, click the name of the category in the Categories box, and then drag the button or item from the Buttons area to the displayed toolbar.
To delete a button, drag it off the toolbar.

Tip   When you delete a built-in toolbar button from a toolbar, the button is still available in the Customize dialog box. However, when you delete a custom toolbar button, it is permanently deleted. To delete a custom toolbar button from a toolbar but save it for later use, create a toolbar for storing unused buttons, move the button to this storage toolbar, and then hide the storage toolbar.

Changes on my toolbars that are not show below but may be shown at a later date.  What I use is simply an example of how to customize toolbars.  It works for me, you will probably have other preferences.

"Select Visible Cells" button found under Customize on the Utility set of buttons and looks like four horizontal rectangle (2x2).  Starting with selected cells it will reduce the selection to only those that are visible.  This button is especially useful when changing fonts and background colors on Group outlines created with Data --> Subtotals.  I place it to left of BOLD [B] button on my toolbars.

"Strike Through" button which I placed to right of bold, italic, underscore, and double underscore.

Some buttons I created to handle inserting a line and going to the top of a column, both of which can be seen below under Put a descriptive Name on that Custom Button.

 

figure 2 -- Sample toolsbars
figure 2 -- Sample Screen shot for Excel 2000 XL9
       

 

A view with individual toolbars identified in Excel 2000 XL9 is not included here because of the space it would occupy on this page and the size of the download.  But you can see it and additional toolbars for both Excel 2000 XL9 and and Excel 95 XL7) on Figure 3, another page.

 

There is a also a set of Custom buttons that you can use to activate your own macros.  The yellow smilely in figure 2, for instance, cab be used to activate the PutFileNameInFooter macro.

Additional Toolbar Things

Is it possible to set the Zoom value (in standard toolbar) to a new value for all next openings of Excel 95? (--Primoz Bradac--)
Set the zoom to the new default and 'Save As' Book.xlt (Template) to the startup directory, (With XL97, normally C:/Program Files/Microsoft Office/Office/Xlstart/) (--Nick Hodge--) [assumes you don't already have a template]

If you have book.xlt or sheet.xlt templates, they belong in your XLSTART directory. Used for defaults when creating new files. The sheet.xlt is used for default when adding new sheets to an existing Excel file.

The startup directory will be found as something similar to
    C:\Program Files\Microsoft Office\Office\Xlstart\
    H:\Program Files\Microsoft Office2000\Office\XLStart

Some buttons have keyboard opposites that can be used by using the shift key in combination with a button to get it's opposite. 
One example is using shift with the single underscore button to effect a double underscore.  Another is Shift+F1 to get the [\?] help button.  See article by Laura Stewart in Woody's Office Watch (21 July 1999, Vol 4 No 30).

Custom Buttons

For information on invoking a macro using a Custom Button refer to HELP topic topics

Custom Buttons

Toolbars --> [customize] --> custom -->
then in XL97 - XL2000 select the Commands Tab.
Drag a button to the toolbar --> assign an existing macro

I have assigned one custom button to include a filename with full pathname in the left footer.  Since the toolbars are in effect for all my Excel usage the macro is installed as follows:   personal.xls!PutFileNameInFooter

Creating a Custom button with a NEW image

I don't know how you store the graphics so that you upgrade to a new EXCEL or put your new buttons into someone else's Excel.

From looking at:       HELP --> buttons (toolbar), button image

It becomes apparent that one should at least take certain steps to not lose the newly created buttons.  The buttons should be placed onto a hidden toolbar so that they do not get accidentally deleted.  From the hidden toolbar the icons can be copied to another toolbar. APPARENTLY THEY CAN NOT BE COPIED.

&nbps;
 
  1. View --> toolbar --> Customize
  2. If you don't already have a "Special" toolbar, create a new toolbar well identified e.g.
    enter a name:  Special Toolbar for David McRitchie
    Then hit the [NEW] button.
  3. Bring up "Special" on to the floating area (easier here), and populate with buttons from the Customize area for instance. figure 4 -- Button Edit
  4. You will assign a macro to each button.
  5. Now to edit each button.
  6. Right-click on the button on the "Special" toolbar, and edit it.
  7. After you have finished editing, save.
  8. After you have the button edited and the macros assigned, you should hide the "Special" toolbar so that nothing bad happens to it. You can use the [x] to hide, but be very careful not to move buttons off of it. 
     
    Buttons can only be moved from one tool bar to another or deleted by moving off the toolbar when working with toolbars.  Be careful or you could lose your buttons.
    View --> toolbar --> Customize
    An entire toolbar can be moved or hidden at any time.
     
  9. You are now ready to use the "Special" toolbar to add buttons to existing or new toolbars.

 

Creating Custom Buttons on Excel Spreadsheet

Toolbar buttons are 16 x 16 pixels.

You can create custom buttons using your spreadsheet by pasting any image to the button.  Example to create an icon from a spreadsheet cell:  Use a pointsize of 36 to make things easier, and move the borders so that the cell is absolutely square.  Copy the cell (Ctrl+c), (you do not even need to convert it to a picture). 

RClick on toolbars area, custom, place a picture on the toolbar, Paste Picture will copy your cell picture or other picture in the clipboard over the top of the picture on the toolbar.

 
 ABCDEFGH
1 H I ๑ ๒ 1 2 3 Q

Custom Buttons reassigned for Testing

bu123

I have some custom buttons [Q] [1] [2] [3] [<>] which I keep reassigning macros to for testing. 

Reassignment is real simple.  right-click on toolbars, customize, right-click on button [1], reassign macro, exit custom.XL7XL8XL9 

Even though XL2000 XL9 makes adding tooltips simple, these buttons are only for testing so I do not bother putting descriptive tooltips on them during customize.XL9

Put a descriptive Name on that Custom Button

coltop  insrtrow  smiley
**Top of Column**  **Insert Row**  **path in Lfoot**

In XL2000 XL9 placing descriptive tooltips on buttons is greatly simplified.  It is as easy as reassigning a macro since it is on the same dialog.  right-click on toolbars, customize, right-click on button, change the descriptive name: ___ in white box, exit custom.

Instructions for XL95 XL7 and XL97 XL8 follow.

You can place a descriptive name (tool tip) on the button that can be seen when passing the cursor over the button by running a single-use macro.  You must include the name of the toolbar and you must supply the position of the button on the toolbar and blank positions on the toolbar must be counted.  If the description disappears between buttons there is a blank that must be counted.  Because of the difficulty in accurately counting positions, make a note of the other tooltip comments and change your own one at a time.  I have commented mine out after use to avoid problems later.  If you cannot see the tooltips see HELP.  (view --> toolbars --> show tooltips)

To find out the toolbar name click on the space in the toolbar group beside a toolbar button -- the name will show on the Status Bar (lower left corner where Ready normally appears).

Markbuttons is simple code but Bill Manville has a much better solution (1999-02-08)for XL95 that displays each button and allows you to change it, and points out that in XL97 you simply use View / Toolbars / Customize / right-click the button and change the name.  Excel 2000 provides for tooltips on your buttons within Customize. 

Sub Markbuttons()
    'Application.Toolbars("Toolbar 4").ToolbarButtons(1).Name = "**Top of Column**"
    'Application.Toolbars("Toolbar 3").ToolbarButtons(1).Name = "**Insert Row**"
    'Application.Toolbars("Auditing").ToolbarButtons(13).Name = "**path in Lfoot**""
End Sub

Sub GotoTopOfCurrentColumn()
    Cells(1, ActiveCell.Column).Select  'J.Campion 2000-03-20
End Sub

Sub GotoBottomOfCurrentColumn()
   'Tom Ogilvy 2000-06-26
   Cells(Rows.Count, ActiveCell.Column).End(xlUp).Select
End Sub

Sub gotolastnotlen0()
   Dim lstrow As Long, i As Long  'D.McRitchie 2000-12-04 prog.
   lstrow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
   For i = lstrow To 1 Step -1
     If Len(Cells(i, ActiveCell.Column)) <> 0 Then GoTo done
   Next i
done:
   Cells(i, ActiveCell.Column).Select
End Sub

Sub MacroDialogBox()
'Application.SendKeys "^%{F8}"
Application.SendKeys "%{F8}"
End Sub
By the way the [Home] button will return you to the leftmost cell on a row, providing you haven't invoked Transition Navigation Keys.  VBA code would be 
   Cells(ActiveCell.Row, 1).Select
Something similar to navigating to the topmost,bottommost,rightmost,leftmost cell in that column or row is double-clicking on a cell boundary which will move you in that last cell in that direction before it hits a boundary or blankcell.

Information on Existing Toolbar Button [XL95]

Pass cursor over the button to see the Tool Tip.

View the macro behind the button

right-click on button, choose Toolbars; then right-click on button choose Assign Macro, which will show you which macro you have already assigned.

If you delete the macro but want to keep the button, move the button to your special (hidden) toolbar.  If you do not move it off and not into a toolbar it will be lost forever.

Change the button image

right-click on button, choose Toolbars; then right-click on button choose Edit button.

Menu and Tool Menu [#menu]

Putting the macro into your Tool menu, Pertains to XL95 . 

For XL97 and XL2000 see topics Toolbar and Menu examples for XL2000 a section specifically rewritten for XL2000 and at the very end see Related Articles, Specifically relating to Menu and Menus below.

Under Tools --> Macro --> (list) -- after selecting a macro there is an options button that allows you to specify a shortcut.  It also allows you to add the macro to your Tools menu.  I identify my material with an asterisk in front so that it doesn't look like the professional and shipped Excel options. 

Elimination of a shortcut or toolbar menu option when the macro has ceased to exist or has been moved.  Tools --> Edit Menu -- that should be enough to get you started -- this option available only when viewing a macro sheet (VBA).  You can change the associated file, macro, description, or delete the menu item.

Additional information in HELP -- Deleting and restoring menus and menu items

Comments (Tool Tips), Pertains to XL95 . 

One of these will work for you.
  RClick --> Insert comment
  View --> Insert --> comment
  Insert --> Comment (XL95)

The following have options you may be interest in:
  Also Tools --> Options --> View
  File --> Page setup --> Sheet
  desktop --> Control settings --> Display --> Apperance --> tool tips

Toolbar and Menu examples for XL2000

Changes have been made to XL2000 such that menus are created and changed to work same as adding macro buttons in previous versions.  Additional information on buttons is scattered throughout this page above and below this point.

More information on XL2000 menus can be found in related area. Items are added to ../icons/toolbar and to Menus via
   Customize, Commands,  Macros
   (you can copy these 16 bit x 16 bit buttons)

       Insert Row, Formulas  Top of Column  Bottom of Column  Insert Footer  #1 Reassign for testing  #2 Reassign for testing  #3 Reassign for testing  #4 Reassign for testing  List Subs and Functions  (sour face) Reassign for testing  Macros [Alt+F8]  GoTo Sub or Function  Backup By Date  Previous Sheet -- Ctrl+PageUP  Next Sheet -- Ctrl+PageDN  MakeHTML_Link  Euro  IE5 - Goto HTML  CharMap  notepad  ClearConstants  DeleteThisSheet - Delete Sheet

Toolbar Button Assignment I created for use in XL2000
Several of the macros to go with these buttons were included above.

Install toolbar button in Excel 2000 (« within Customize «):

Editing your Menus in XL2000

You may note some similarities between menus and toolbars and buttons in Excel 2000, and that is because they are the same in Excel 2000, differing only in appearance, and yes you can make one look like the other.

All changes are done under Customize, which you can get to by either

Continuing with building Toolbar menus.

Tools (menu)   My own Customizations in XL2000
I sometimes like to place an asterisk in front of my menu items so they stand out from those that came with Excel, and other canned menu items.

Creating a Euro toolbar button Euro like the Dollar Sign ($) toolbar button

Using the same format as would be used on the Dollar Sign button, but substituting the Dolloar($) sign with the Euro(€) we have a number format for the macro. 
Sub Euro_Format()
    Selection.NumberFormat = _
        "_(€* #,##0.00_);_(€* (#,##0.00);_(€* "" - ""???_);_(@_)"
End Sub

Creating an icon button from a Euro [€] character or any other text

While the instructions here may prove useful for other purposes, have discovered that there is a HELP topic on this Entering, displaying, and printing the euro sign which includes a hyperlink to an addin that works like the [$] button, and although I didn't suceed in installing the addin described, I did copy the Euro button [€] Euro from it, and make some slight changes.  (You can copy the icon from this page if you can't install the addin)

Whether you copy a cell or an image, the image will be resized to 16 bits horizontally and 16 bits vertically.  Note we want a stylized elongated Euro character.

Prepare button image
  1. Select a cell, choose font size of 10 as the most font design work usually goes into 10 point fonts.  Set the zoom for the spreadsheet at 200% for visibility.
  2. Place the Euro character into the selected cell  Alt+0128 
  3. Format cell, alignment, center both vertically and horizontally.
  4. Snugly fit the cell borders to the cell but make the cell taller than it is wide, so that the conversion to an icon will elongate the character horizonally when it makes it 16 x 16 bits.
  5. Copy the cell (Ctrl+C) to the clipboard.
  6. It is not necessary to make this a picture, although pictures also work.
Create the toolbar button «
To create/change the button view, toolbars, customize, or right click in toolbar area then customize, next create the button by going to command tab, then macro, and dragging the Happy Face to the toolbar.  Right click on the new button, paste button image.

Obtain toolbar buttons from program .exc and .ico files

Icon Explorer obtainable from ZDNET installs directly into library it is unzipped in (h:\downloadh\iconExplorer).  Change settings to from file instead of from folder.  Wants a diskette in drive A:.  Select the folder or file.  Copy picture from display and paste into "Paste Button Image", some have to be edited afterwards.    IE5 - Goto HTML  CharMap  notepad 
  http://hotfiles.zdnet.com/cgi-bin/texis/swlib/hotfiles/info.html?fcode=00125B&b=pcm [broken link: search ZDNET for icon explorer]

Additional Features of Standard Toolbar Buttons

Status Bar (see my Formula page)

For layout and information on Statusbar see Status Bar.  There are some features for adding up cells, or counting cells, in the selected areas which is why this information is located on the Formula page.

Related

Microsoft Technical Support -- Knowledge Base

Access Microsoft's Knowledge Base, Troubleshooting Wizards, and downloadable files

Microsoft Technical Support--Support Options (http://support.microsoft.com/support/a.asp?M=S)

Example: 
  1. Search for is about:  Excel for Windows
  2. I want to search for:  excel and XL95 and toolbars

Related Articles found elsewhere

Related Articles, Specifically relating to Menu and Menus

In XL97 and XL2000 menus are treated more like custom buttons, and yes you can still add menus, and menu items manually.  Because menus and custom buttons are now related the following list repeats some references already mentioned; likewise, some already mentioned related articles which are of interest and some MS KB articles mentioned later are not repeated below.  John Walkenbach's "Menu Maker" makes arranging a menu with categories and button faces on menu items a lot easier.
This page was introduced upon opening on January 1, 1998. 

[My Excel Pages -- home]    [INDEX to my site and the off-site pages I reference] 
[Site Search -- Excel]     [Go Back]    [Return to TOP] 

Please send your comments concerning this web page to: David McRitchie mailto:DMcRitchie@msn.com