XL: Setting Status Bar Text and ToolTips for Toolbar Buttons

• Microsoft Excel for Windows, versions 5.0, 5.0c
• Microsoft Excel for the Macintosh, versions 5.0, 5.0a
• Microsoft Excel for Windows 95, version 7.0

SUMMARY

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 box.

NOTE: To display the Macro Options dialog box, choose Macro from the Tools menu, select the name of the macro assigned to the toolbar button, and choose 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 of how to do this.

MORE INFORMATION

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 toolbar button.

NOTE: This mouse cursor behavior differs from earlier versions of Microsoft Excel. In earlier versions you have to hold down the left mouse button while the cursor 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 Macro".

ToolTips

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 Buttons).

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and 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 needs. If you have limited programming experience, you may want to contact the Microsoft fee-based consulting line at (800) 936-5200. For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

   http://www.microsoft.com/support/supportnet/refguide/default.asp

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:

   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 macro below 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 example above, 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 possible to assign status bar text directly to a toolbar button using a Visual Basic macro. Because status bar text can only be 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
  

REFERENCES

"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:

    toolbar buttons