Using Message and Input Dialog Boxes

The easiest way to add a dialog box to your application is to use a predefined built-in dialog box. Visual Basic provides two types of predefined dialog boxes: the message box and the input box. Both types are easy to use, but you have limited control over how they look and what type of information you can get from or display for the user.

The following table lists the functions and methods for adding predefined dialog boxes to your Visual Basic application.

Message Box

The MsgBox function creates a simple dialog box that can display a short message and a predefined set of buttons. The user can click one of the buttons to respond to the message or to cancel the message box. The simplest message box contains only a message string and an OK button, as in the following example.

MsgBox prompt:="Please close all files", title:="Files"

The MsgBox function performs any necessary string conversions before it displays the message string. You can use any data type for the prompt argument.

Tip

You can use a message box as a simple debugging tool. Use the message box text to display an interim value in a long calculation or to display a status message in a large procedure. For more information about debugging, see Chapter 6, "Debugging."

You can also create more complex message boxes that contain different icons and buttons. The following example uses the MsgBox function to display a message box containing the Warning Message icon and Yes and No buttons. The No button is the default response. The value that the MsgBox function returns depends on the button the user clicks; when you use the return value, you must enclose the MsgBox function arguments in parentheses (the preceding example ignores the return value, so the parentheses aren't required).

Sub CreateSampleBox()
    msg = "Do you want to continue?"
    dialogStyle = vbYesNo + vbExclamation + vbDefaultButton2
    title = "Error"
    response = MsgBox(msg, dialogStyle, title)    ' Get user response.
    If response = vbYes Then                        ' Evaluate response and
        msg = "You clicked Yes."                    ' act appropriately
    Else    
        msg = "You clicked No or pressed ENTER."
    End If
    MsgBox msg                                    ' Display action taken.
End Sub

Note

The constants vbYesNo, vbExclamation, and so on are built into Visual Basic. For a list of these constants, see "MsgBox function" in Help.

Input Box

The InputBox function creates and displays a simple dialog box that contains a prompt, an edit box, and OK and Cancel buttons. You can use this input box to allow the user to enter data. You can change the text in the title bar, the prompt displayed to the user, and the position of the dialog box on the screen. To create a more elaborate dialog box, you must use a dialog sheet and create a custom dialog box. For more information about creating a custom dialog box, see "Using Custom Dialog Boxes" later in this chapter.

The return value from the InputBox function is a string containing the data in the edit box. If the edit box is empty or the user clicks Cancel, the return value is an empty string (""). The following code displays a simple input box.

radius = InputBox("Enter the circle's radius:", "Circle Radius")

For more information about the InputBox function, including the complete syntax, see "InputBox function" in Help.

The InputBox method of the Application object works like the InputBox function, but the method also allows you to specify the desired data type for the entered data (a range or a string, for example). If the user enters data with an incorrect type, Microsoft Excel displays a message box indicating that the entry isn't valid.

If you specify a desired data type, the return value from the InputBox method has that data type if the user clicked OK or pressed ENTER to dismiss the dialog box. If you don't specify a data type, the return value is a string (which can be an empty string if the user clicked OK with nothing in the edit box). Whether or not you specify a data type for the entered data, the return value is False if the user clicked Cancel or pressed ESC to cancel the dialog box. For more information about the InputBox method, including the complete syntax, see "InputBox method" in Help.

Note

Notice the difference between the terms dismiss and cancel. When the dialog box is dismissed, the Show method returns True, and Microsoft Excel processes the changes made to the dialog box. When the dialog box is canceled, the Show method returns False, and Microsoft Excel ignores changes made to the dialog box.

The following code uses the InputBox method to ask the user for a search range and a search value. The search range must be a valid Range reference (sheet1!a1:a20, for example), and the search value must be a number.

Sub CountValues()
    cellCount = 0
    Set rangeToSearch = Application.InputBox( _
        Prompt:="Enter the range to search", _
        Type:=8)        'type 8: must be a Range object
    searchValue = Application.InputBox( _
        Prompt:="Enter the search value", _
        Type:=1)        ' type 1: must be a number
    If searchValue = False Then Exit Sub        'user clicked Cancel
    For Each c In rangeToSearch
        If c.Value = searchValue Then
            cellCount = cellCount + 1
        End If
    Next c
    MsgBox cellCount
End Sub