Modifying Controls While a Dialog Box Is Visible

You can make any dialog box you've created easier to use and understand by modifying controls while the dialog box is visible. For example, you can disable controls, change the focus, modify worksheet cell links, and change the dialog box size.

Enabling a Control

You can use the Enabled property of a control to prevent the user from making changes to an option unless a specified condition is met. This is often used to make a set of option buttons available only when the user selects a check box, as shown in the following code. This procedure is assigned to the check box, and it runs whenever the state of the check box changes.

Sub SetOptions()
    With ActiveDialog
        If .CheckBoxes(1).Value = xlOn Then
            .OptionButtons(Array(1, 2, 3, 4)).Enabled = True
        Else
            With .OptionButtons(Array(1, 2, 3, 4))
                .Enabled = False
                .Value = xlOff
            End With
        End If
    End With
End Sub

You could also use the Enabled property to create a procedure for an edit box that makes the OK button available only when the user has entered a part number that conforms to a standard pattern.

Setting the Focus to a Control

You can set the focus to a control in a dialog box by setting the Focus property of the dialog box (the control with the focus is acted on whenever the user presses ENTER). The following example sets the focus in the active dialog box to the button named "test."

Sub SetFocus()
    ActiveDialog.Focus = "test"
End Sub

Note

You can set the focus only while the dialog box is running. For more information, see "Focus property" in Help.

Modifying Links Between a Control and a Worksheet

You can establish links between a control and a cell on a worksheet when the control is created, but you can also create, modify, and delete links between a control and a cell while a procedure is running. You use the LinkedCell property of the control to do this. The LinkedCell property belongs to the linked control and not the linked cell. The ListFillRange property also specifies the input range for list boxes. For more information, see "Linking Controls to Worksheet Cells" earlier in this chapter.

Changing Dialog Box Properties

You can change some of the properties of the dialog box itself (such as its size) while it's running. A common use for this is to expand a dialog box to reveal additional options when the user clicks the Options button. You can place controls below what's usually the bottom border of the dialog box. When the user clicks Options, the procedure changes the vertical size of the dialog box to reveal the hidden options.

Sub DisplayOptions()
    ActiveDialog.DialogFrame.Height = 91
End Sub