VBA- Order of Precedence for Events

[GreggaR0und]

Hi;

I am writing VBA in Word. The application is that a Userform opens up from a template in order to acquire user input for the remainder of the form.

The Userform has three fields which then get validated. All of that works fine. However, I want to allow for the condition that a user opening the form doesn't want to complete it. They have the option of pressing the CANCEL button on the form.

The problem is that even though I have an event handler for the OnClick event of the Cancel button, the order of precedence seems to remain with the Exit event of the field which I'm on. The validation keeps in even though the form is blank and the user has pressed Cancel.

How can I intercept the OnClick event and avoid executing the validation code?

Here is the code:

Private Sub btnCancel_Click()
'Close the form
iResponse = MsgBox("This will close your document. Are you sure?", vbYesNo)
Select Case iResponse
   Case vbYes
     Unload Me
     ThisDocument.Close
'   Case vbNo
'     frmUserInfo.Show
End Select
End Sub
Private Sub btnFinish_Click()
Unload Me
End Sub
Private Sub CostCenter_Exit(ByVal Cancel As MSForms.ReturnBoolean)
 
'DO NOT EXECUTE THE SUB IF USER CLICKED CANCEL
 
If Len(Me.CostCenter.Value) <> 4 Then
   MsgBox "CostCenter must be a length of four."
   Cancel = True
   Me.CostCenter.SetFocus
   Me.CostCenter.SelStart = 0
   Exit Sub
End If
End Sub
 
Private Sub ID_Exit(ByVal Cancel As MSForms.ReturnBoolean)   
 
If Not IsNumeric(Mid(Me.ID.Value, 2, 7)) Then
   MsgBox "ID must be in the format of 'M0012345'."
   Cancel = True
   Me.ID.SetFocus
   Me.ID.SelStart = 0
   Exit Sub
End If
End Sub
Private Sub ProjectCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(Me.ProjectCode.Value) Then
   MsgBox "Project Code must be numeric- (ex: '95714')."
   Cancel = True
   Me.ProjectCode.SetFocus
   Me.ProjectCode.SelStart = 0
   Exit Sub
End If
 
If Len(Me.ProjectCode.Value) <> 5 Then
   MsgBox "Project Code must be a length of five."
   Cancel = True
   Me.ProjectCode.SetFocus
   Me.ProjectCode.SelStart = 0
   Exit Sub
End If
End Sub
 

Thanks,
Gregg

[Killer42]

Probably the simplest way would be to add an OK button. Do all the validation from there, rather than as you leave each field.

Also, here's a technique you can use in VB6 - I don't know whether it applies in VBA so it may not work for you. Each control has a CausesValidation property. If this is False, then the validation event doesn't fire for the field the user left to get there. In other words, you can set the Cancel button to not fire the Validation event in the field the user came from. So if you then did your validation in the Validation event (which seems appropriate anyway) this would get around it.