How do I create tooltips for new toolbar buttons?
	Is there a way to display my custom application name in alert boxes instead of "Microsoft Excel"?
	How do I make my message boxes look similar to Microsoft Excel's?
	How do I obtain the user's confirmation before taking an action?
	To use dropdowns on worksheets, do I have to write VBA code?
	How can I turn off messages from Excel while a macro is running?

How do I create tooltips for new toolbar buttons?
Posted November 11, 1996
Keywords: Toolbar Button Tooltip

You do this by changing the Name property of the ToolbarButton object with a macro. The following example shows the syntax:

Sub ChangeTooltip()
   Toolbars("MyToolbar").ToolbarButtons(x).Name = "My Tooltip"
End Sub
	

Just replace MyToolbar with the name of the toolbar that your button sits on and replace x with the index number of your button on that toolbar. You can find the name of the toolbar by right-clicking over the toolbar area and finding the toolbar name that turns your toolbar on and off. You get the index number of your button by counting, from left to right, buttons and spaces between buttons up to and including your button. You only have to set the tooltip once, Excel will remember it after that. And you can reset it as many times as you want.

Is there a way to display my custom application name in alert boxes instead of "Microsoft Excel"?
Keywords: Msgbox Message Alert Caption
Posted October 10, 1996

In VBA the MsgBox statement and the MsgBox() function take three parameters: the first parameter is the message to display, the second parameter is a constant that controls the icons and buttons in the message box, and the third parameter is the caption in the message box. Thus you have

MsgBox "This message has the standard Excel caption."
MsgBox "This message comes to you from my great app.", , "My Great App"

While you are at it, you might as well use the second parameter and display icons indicative of the type of message. Examples:

MsgBox "This is for your information.", vbInformation, "My Great App"
MsgBox "Could not access specified file.", vbExclamation, "My Great App"

How do I make my message boxes look similar to Microsoft Excel's?
Keywords: Msgbox Message Icons Buttons Professional
Posted October 14, 1996

Appropriate use of icons will give your message box dialogs a professional look. Select the icon that is indicative of the type of message you are displaying. The MsgBox routine takes three parameters: the message to display, a constant controlling the icons and buttons, and the caption of the message box dialog. Examples:

szMesg = "MyApp will begin processing all the files in the specified directory now."
MsgBox szMesg, vbInformation, "MyApp"
MsgBox "Warning: No files processed.", vbExclamation, "MyApp"
MsgBox "Fatal Error: System files corrupted." vbCritical, "MyApp"
If MsgBox("Delete specified records?", vbQuestion+vbYesNo, "MyApp") = vbNo Then
   Exit Sub
End If
	

Of course, consistency is essential to achieve a professional look and feel. To avoid missing arguments, you can easily write 3 or 4 wrapper routines which take the message as parameter and call the MsgBox function with the required arguments.

How do I obtain the user's confirmation before taking an action?
Keywords: Confirmation Question Msgbox Message
Posted October 14, 1996

The best way to obtain the user's response to a question is to use the MsgBox() function. It takes three parameters: (1) the message to display, (2) a constant that determines which of a set of standard buttons are made available, and which icon is displayed, and (3) the caption for the message dialog box. So you have

If MsgBox("Rename ABC to PQR?", vbQuestion+vbYesNo, "MyApp")=vbNo Then
   Exit Sub
End If
	

If you need more control over the buttons displayed, or you need additional controls like list boxes, then you have to use custom dialogs. For most cases, the MsgBox() function offers a fast and easy solution.

To use dropdowns on worksheets, do I have to write VBA code?
Keywords: Dropdowns VBA Formulas
Posted July 6, 1996

Not necessarily. You can accept and process user input via dropdowns without writing code. To do this, you need to set the properties of the dropdown as explained in the example below. Suppose you need the user to pick one of the following values from a dropdown: New York, London, Bombay, Tokyo. Store these values in a cell range, say, K1:K4 and name the range as ValidValues. Name cell L1 as SelectedIndex and enter the formula

=INDEX(ValidValues,SelectedIndex)
			

in cell L2. Use the Format->Object menu option or right-click the dropdown and choose Format Object. Set the Input Range as ValidValues and the Cell Link as SelectedIndex. If you wish, you can change the Drop Down Lines to 4. Now, whenever the user clicks the dropdown and chooses a value, cell L2 will display the selected value.

Tips: Ensure that the cells used to store the values are located away from the main input and output cells so that they do not get deleted when rows or columns are deleted. You might also want to hide these cells from the user. In the Format->Object dialog, after clicking an edit box, you can also click on the worksheet and drag the mouse to indicate the address of the input range. However, to enter a range name as specified in the example, you must type in the name. The advantage of using names is that you can later move the cells or increase the size of the named range.

How can I turn off messages from Excel while a macro is running?
Keywords: Warning Message Alert
Posted January 16, 1996

Most messages can be suppressed using the following code:

Application.DisplayAlerts = False	

There are a few exceptions to this, including "Selection is too large, Continue without Undo?". These can be trapped with On Error statements.

Back to Top

Back to the FAQ Table of Contents

Do they have reserved parking for non-handicap people at the Special Olympics?