Stopping the Close Button

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 worksheet. 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...End With structure keeps frmPersonal in scope, and frmPersonal is not unloaded until after the End With statement.

There are a number of simple ways in which the preceding problem could be corrected, but the following 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 End If End Sub

The QueryClose event can be triggered in four ways. You can determine what caused the event by using the following intrinsic constants to test the CloseMode parameter.

Constant

Value

Reason for the Event

vbFormControlMenu

0

The user clicked the x in the Control menu on the UserForm.

vbFormCode

1

The Unload statement was used to remove the UserForm from memory.

vbAppWindows

2

Windows is shutting down.

vbAppTaskManager 3 The application is being closed by the Windows Task Manager.

vbAppTaskManager 3 The application is being closed by the Windows Task Manager.

0 0

Post a comment