This article was previously published under Q112632
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.
In Microsoft Excel versions 5.0 and later, you can assign status bar textto a toolbar button by using a Microsoft Visual Basic for Applicationsprocedure or by using the Status Bar Text box in the Macro Options dialogbox.
NOTE: To display the Macro Options dialog box, click Macro on the Toolsmenu, select the name of the macro assigned to the toolbar button, andclick 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 andToolTip 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 atoolbar is a property of the macro assigned to the button you choose, not aproperty 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 toolbarbutton and is displayed when you position the mouse cursor over thattoolbar button.
NOTE: This mouse pointer behavior differs from earlier versions ofMicrosoft Excel. In earlier versions you have to hold down the left mousebutton while the pointer is on top of the toolbar button in order to readthe status bar text.
The status bar text changes whenever a macro is assigned to a toolbarbutton. If a macro that you assign to a toolbar button does not have statusbar text, the text displayed on the status bar when you choose that buttonis the default text for that toolbar button. For example, if you assign amacro to a button taken from the Custom category, and you do not add aStatus Bar Text entry for this macro, the default text displayed on thestatus bar for this button is "Creates A Button To Which You Can Assign AMacro."
A ToolTip is a property of the individual toolbar button. The only way tochange a ToolTip for a button is by using a Visual Basic macro toexplicitly set the Name property for a particular toolbar button.
VISUAL BASIC MACRO EXAMPLES
The following examples demonstrate how to change the ToolTip name forbuttons 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, andstatus bar text to that button (Macro to Create and Customize Toolbar andButtons).
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 defaultToolTip that is created for that button is named "Custom." The only way tochange this ToolTip is by using a Visual Basic macro. The following is anexample of how you can accomplish this:
Sub ChangeTooltips() 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 End If Next End Sub
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 button.
Sub AddButton() ' Dimension variables. Dim mytoolbar As String, mytooltip As String, mystatbar As String Dim used As Boolean, x As Integer again: ' 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 End If Next ' 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. GoTo again End If ' Check for blank toolbar name entered. If mytoolbar = "" Then GoTo none ' Create a new toolbar with the name entered above. With Toolbars.Add(mytoolbar) ' 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 End With ' 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 none: End Sub ' Sample macro that is assigned to new button. Sub myMacro() MsgBox "This macro is assigned to your new toolbar button!" End Sub
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 last button.
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
Application.StatusBar = "<text>"
where <text> is the text that you want to appear on the status bar when you run the macro.
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 Toolbarsand Toolbar Buttons with Visual Basic"
For more information about toolbar buttons, choose the Search button inHelp and type: