In Microsoft Excel versions 5.0 and later, you can assign status bar text
to a toolbar button by using a Microsoft Visual Basic for Applications
procedure or by using the Status Bar Text box in the Macro Options dialog
NOTE: To display the Macro Options dialog box, click Macro on the Tools
menu, select the name of the macro assigned to the toolbar button, and
click the Options button.
ToolTips is a new feature in Microsoft Excel starting with version 5.0.
ToolTips can only be assigned to a toolbar button by using a macro.
The following information describes how you can set the status bar text and
ToolTip for a toolbar button and provides examples.
Status Bar Text
The text that is displayed on the status bar when you choose a button on a
toolbar is a property of the macro assigned to the button you choose, not a
property of the button itself. When you assign a macro to a toolbar button,
the status bar text for that macro is assigned to the corresponding toolbar
button and is displayed when you position the mouse cursor over that
NOTE: This mouse pointer behavior differs from earlier versions of
Microsoft Excel. In earlier versions you have to hold down the left mouse
button while the pointer is on top of the toolbar button in order to read
the status bar text.
The status bar text changes whenever a macro is assigned to a toolbar
button. If a macro that you assign to a toolbar button does not have status
bar text, the text displayed on the status bar when you choose that button
is the default text for that toolbar button. For example, if you assign a
macro to a button taken from the Custom category, and you do not add a
Status Bar Text entry for this macro, the default text displayed on the
status bar for this button is "Creates A Button To Which You Can Assign A
A ToolTip is a property of the individual toolbar button. The only way to
change a ToolTip for a button is by using a Visual Basic macro to
explicitly set the Name property for a particular toolbar button.
VISUAL BASIC MACRO EXAMPLES
The following examples demonstrate how to change the ToolTip name for
buttons on a toolbar (Macro to Change ToolTip Names for Buttons on Toolbar)
and how to add a button to a toolbar and then assign a ToolTip, macro, and
status bar text to that button (Macro to Create and Customize Toolbar and
Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.
Macro to Change ToolTip Names for Buttons on Toolbar
When you create a custom toolbar button in Microsoft Excel, the default
ToolTip that is created for that button is named "Custom." The only way to
change this ToolTip is by using a Visual Basic macro. The following is an
example of how you can accomplish this:
Set mytoolbar = Toolbars("standard")
For Each mytool In mytoolbar.ToolbarButtons
Message = "Enter a new tooltip name" ' Set prompt.
Title = "Tooltip changer" ' Set title.
Default = mytool.Name ' Set default.
' If tool is not a gap, display input box.
If Not mytool.IsGap Then
MyValue = InputBox(Message, Title, Default)
' Change tooltip name.
mytool.Name = MyValue
- You can change the word "standard" in the second line of the following
macro to the name of the toolbar that you want to change, for example
"formatting" or "toolbar 1."
- This macro allows you to change the ToolTip for each button on a
toolbar. If you do not want to change the ToolTip for a particular
button, click Cancel.
Macro to Create and Customize Toolbar and Buttons
The following Visual Basic macro example does the following:
- Prompts you for the name of the custom toolbar that you want to create.
- Adds a button to this toolbar.
- Prompts you for the ToolTip text that you want for the button.
- Assigns the sample macro "myMacro" to the new toolbar button.
- Prompts you for the status bar text that you want displayed for the new
' Dimension variables.
Dim mytoolbar As String, mytooltip As String, mystatbar As String
Dim used As Boolean, x As Integer
' Set variable used to value False.
used = False
' Prompt for name of new toolbar to create.
mytoolbar = InputBox("Enter name of new toolbar to add:")
' Check for duplicate toolbar name.
For x = 1 To Application.Toolbars.Count
If UCase(mytoolbar) = UCase(Application.Toolbars(x).Name) Then
' If same name exists, set variable used to value True.
used = True
' If toolbar name exists, prompt for name again.
If used = True Then
MsgBox "Sorry, this toolbar name is already being used." & _
" Please enter name of toolbar that doesn't already exist."
' Display original dialog box asking for name.
' Check for blank toolbar name entered.
If mytoolbar = "" Then GoTo none
' Create a new toolbar with the name entered above.
' Show the newly created toolbar.
.Visible = True
' Add the "hand" button to the new toolbar
' and assign macro "myMacro" to the new button.
' Note that you can modify this line to add any button
' you want, and assign any existing macro.
.ToolbarButtons.Add Button:=229, OnAction:="myMacro"
' Prompt for ToolTip to display for new button.
mytooltip = _
InputBox("Enter text of ToolTip to display for new button:")
' Add ToolTip to new button with name entered above.
.ToolbarButtons(.ToolbarButtons.Count).Name = mytooltip
' Prompt for text to display on the status bar when you
' hold the mouse over this button.
mystatbar = InputBox("Enter text that you want to appear" & _
" on the status bar for this button")
' Assign status bar text entered above to macro "myMacro"
' that is assigned to the new button.
Application.MacroOptions Macro:="myMacro", statusbar:=mystatbar
' Sample macro that is assigned to new button.
MsgBox "This macro is assigned to your new toolbar button!"
- The Toolbars(x).ToolbarButtons.Count command will count both the buttons
and the spaces between the buttons (unless the buttons are directly side-
by-side) starting with the first toolbar button and continuing to the
In the preceding example, the Toolbars(x).ToolbarButtons.Count command
is not necessary, because the procedure creates a new toolbar and adds
only one button. However, this command is shown here to demonstrate how
to return the number of buttons on a given toolbar.
- It is not recommended to assign status bar text directly to a toolbar
button using a Visual Basic macro. Because status bar text is assigned
to a macro, you can assign a macro to a button, and then assign the
status bar text to the macro to effectively create status bar text for a
particular button. The example above, the macro "myMacro" is
automatically assigned to the new button that is created to show how you
can then add the status bar text.
- The StatusBar argument of the MacroOptions method only affects the
status bar when you position the mouse over a button or a menu command
without selecting it. To display text on the status bar while a macro is
running, you can use the StatusBar property as in the following example
where <text> is the text that you want to appear on the status bar when
you run the macro.
Application.StatusBar = "<text>"
To return control of the status bar to Microsoft Excel after running the
macro that contains the above statement, set the StatusBar property to
False as in the following command:
Application.StatusBar = False
"Visual Basic User's Guide," version 5.0, Chapter 12, "Managing Toolbars
and Toolbar Buttons with Visual Basic"
For more information about toolbar buttons, choose the Search button in
Help and type:
Article ID: 112632 - Last Review: October 11, 2006 - Revision: 2.3
- Microsoft Excel 95 Standard Edition
- Microsoft Excel 5.0c
- Microsoft Excel 5.0a for Macintosh
|kbcode kbhowto kbprogramming KB112632|Retired KB Content Disclaimer
This article was written about products for which Microsoft no longer offers support. Therefore, this article is offered "as is" and will no longer be updated.