The Valueproperties of the controls on frmPersonal are then assigned the values in vData. The option
buttons are an exception, because the M and F code values need to be translated to True values as appro-
priate. It is only necessary to set one of the option buttons to True, because the other will automatically
be set to False. You can group option buttons by assigning them the same value in their GroupName
property, or by placing them in the same frame. The option buttons here do not have a value in their
GroupNameproperty. They are considered to be in the same group because they are in the same frame.
The Show method displays frmPersonal. Control then passes to frmPersonal until it is hidden, which
occurs when the user clicks OK or Cancel, or the x at the top of the UserForm. The user can also press
Esc to activate the Cancel button because it has had its Cancel property set to True. The user can also
press Enter to activate the OK button, as long as the Cancel button does not have the focus, because the
OK button’s Default property has been set to True.
When frmPersonal is hidden, the cmdShowUserForm_Click event procedure regains control, and
checks to see if the Cancel button was clicked. It does this by examining the value of the Public variable
If Cancelled is False, the procedure loads the values of the controls back into vData, translating the
option button settings back into an F or M value, and the values in vData are directly assigned back to
the worksheet. The final step is to unload frmPersonal from memory.
Stopping t he Close But ton
One problem with the previous code is that, if the user clicks the x, which is the Close button at the top
of frmPersonal, the event procedure does not exit. Instead, it transfers any changes back to the work-
sheet. This is because the default value for Cancelled is False. Normally, clicking the x would also
unload the form and the code would fail when it tries to access the controls on the form. However, in
this case the With...EndWith structure keeps frmPersonal in scope, and frmPersonal is not
unloaded until after the EndWith statement.
There are a number of simple ways in which the preceding problem could be corrected, but the follow-
ing method gives you total control over that little x. You can use the QueryClose event of the UserForm
object to discover what is closing the UserForm and cancel the event if necessary. Adding the following
code to the frmPersonal module blocks the Close button exit:
Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
MsgBox “Please use only the OK or Cancel buttons”, vbCritical
Cancel = True
The code modules behind UserForms (as well as those behind sheets and workbooks)
are class modules. When you define a Public variable in a class module, the variable
behaves as a property of the object associated with the class module. See Chapter 16
for more details.
Chapter 13: UserForms