To assign properties to a command button in a dialog, right-click the button and select Format Object. It's helpful to do things like assign a default property to one of the buttons so it will be automatically activated when the user presses the return key.
Figure 4. How to assign button properties
The Dismiss property will cause the dialog box to close when the user clicks the button. If there's no need to validate the dialog entries, go ahead and check the Dismiss box. If validation is required it's best to keep the dialog open just in case the user needs to change something. In that case, leave the Dismiss box blank and assign a validation macro to the button. Do the validation steps in the macro and let the macro control whether or not the dialog should be closed (dismissed). Here's a code sample that accomplishes this.
Sub ValidateGraph()
Dim Btn As Object
'Set the DismissButton property to be True so the dialog will close
' if all the validation tests pass.
Select Case GWizReturn
'Since the user could have clicked two possible buttons, make sure
' we choose the right one.
Case GO_NEXT
Set Btn = Wiz3.[ButtonWiz3Next]
Case FINISH
Set Btn = Wiz3.[ButtonWiz3Finish]
End Select
Btn.DismissButton = True
'Check to see if:
'1. Start value is less than end value
'2. There are data to graph
'3. Bin Width/number of bins is valid (nonzero)
If Wiz3.[EditStartValue].Caption = "" Then
MsgBox ("Start Value must be filled in")
'Keep the dialog box displayed until it passes validation.
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditStartValue].Name
Exit Sub
End If
If Wiz3.[EditEndValue].Caption = "" Then
MsgBox ("End Value must be filled in")
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditEndValue].Name
Exit Sub
End If
If Wiz3.[EditBinValue].Caption = "" Then
MsgBox ("Bin Width or Number of Bins must be filled in")
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditEndValue].Name
Exit Sub
End If
If Wiz3.[EditStartValue].Caption >= Wiz3.[EditEndValue].Caption Then
MsgBox ("Start Value must be less than End Value")
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditStartValue].Name
Exit Sub
End If
If Wiz3.[EditBinValue].Caption <= 0 Then
MsgBox ("Bin Width or Number of Bins must be greater than zero")
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditBinValue].Name
Exit Sub
End If
If Int(Wiz3.[EditBinValue].Caption) <> _
Val(Wiz3.[EditBinValue].Caption) And _
Wiz3.[OptionBinCount].Value = xlOn Then
MsgBox ("Number of Bins must not be fractional")
Btn.DismissButton = False
Wiz3.Focus = Wiz3.[EditBinValue].Name
Exit Sub
End If
End Sub
Example 7. Sample validation code
The code in this example is a simplified version of the ValidateGraph routine in the Wizard. Take a look at the real routine to see the extra work needed to fully bullet-proof a dialog. You will gain a new appreciation for the work that Microsoft development teams go through.
Excel also supports built-in validation for edit controls. Set this option in the control's Format Object dialog, shown in Figure 5 below. A new feature in Excel 7 is the ability to set password validation (the code can capture typed characters, but only asterisks are displayed). The Reference validation option checks to be sure the user types a valid cell reference. It also tracks the mouse! All a user needs to do is select cells in the worksheet while the dialog is open and Excel will automatically insert the reference into the edit box, updating it as the mouse moves.
Figure 5. Validation options for edit boxes