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