Displaying the Custom Dialog Box

Run Dialog

The Run Dialog button on the Forms toolbar displays the dialog box. You will usually use this toolbar button while you're testing the dialog box. You can use the Show method to display the custom dialog box from a procedure. When the dialog box appears, the user is free to interact with its controls — entering text in an edit box, clicking an option button, clicking an item in a list box, and so on.

Note

Although you can change the position of a dialog frame on its dialog sheet, you cannot set or change the position of the displayed dialog box using Visual Basic. The dialog box appears with its upper-left corner in the same position as the last dialog box displayed.

When the user clicks a button that dismisses the dialog box (that is, a button for which the DismissButton property is True and the CancelButton property is False), the following actions occur:

• Visual Basic runs any procedure assigned to the button.
• Any edit boxes with data validation turned on are checked for valid data.

  If the contents of an edit box are invalid, Microsoft Excel displays a message and switches the focus to the edit box so that the user can supply a value of the correct data type. This process continues until every edit box contains valid data.

• The dialog box is removed, and the Show method returns True.

When the user clicks a button that cancels the dialog box (that is, a button for which the CancelButton property is True), the following actions occur:

• Visual Basic runs any procedure assigned to the button.
• The dialog box is removed, and the Show method returns False.

For example, suppose that there's a button named "Object Color" on a worksheet. To change a drawing object's color, the user clicks this button to display a dialog box.

The following example shows the procedure assigned to the Object Color button.

Sub DoColorDialog()
    colors(1) = 5   'blue
    colors(2) = 10  'green
    colors(3) = 6   'yellow
    colors(4) = 22  'orange
    originalColor = Selection.Interior.ColorIndex
    saveInteriorColor = originalColor
    If DialogSheets("ChangeColorDialog").Show = False Then
        Selection.Interior.ColorIndex = originalColor
    End If
End Sub

The colors array must be declared at the module level so that it can be used by all procedures in the module. The colors in the array should be set in the same order as the tab order for the color option buttons.

When the user selects a graphic object and clicks the Object Color button, the procedure sets the color-index array values to the four color options, sets the saved color value and the original color value to the current selection color, and then displays the dialog box on the dialog sheet named "ChangeColorDialog."

The user can choose one of the four color options and then click Test, Undo, OK, or Cancel. The ChangeColor procedure is assigned to both the OK and Test buttons, so it runs whenever the user clicks either OK or Test. The UndoColor procedure is assigned to the Undo button. These procedures are discussed in the following sections. If the user clicks Cancel or presses ESC, the Show method returns False, and the procedure resets the object to its original color. For more information about using code with controls, see "Assigning Code to Controls" earlier in this chapter.