469,167 Members | 1,946 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,167 developers. It's quick & easy.

VBA- Order of Precedence for Events

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:

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnCancel_Click()
  2. 'Close the form
  3. iResponse = MsgBox("This will close your document. Are you sure?", vbYesNo)
  4. Select Case iResponse
  5.    Case vbYes
  6.      Unload Me
  7.      ThisDocument.Close
  8. '   Case vbNo
  9. '     frmUserInfo.Show
  10. End Select
  11. End Sub
  12. Private Sub btnFinish_Click()
  13. Unload Me
  14. End Sub
  15. Private Sub CostCenter_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  16.  
  17. 'DO NOT EXECUTE THE SUB IF USER CLICKED CANCEL
  18.  
  19. If Len(Me.CostCenter.Value) <> 4 Then
  20.    MsgBox "CostCenter must be a length of four."
  21.    Cancel = True
  22.    Me.CostCenter.SetFocus
  23.    Me.CostCenter.SelStart = 0
  24.    Exit Sub
  25. End If
  26. End Sub
  27.  
  28. Private Sub ID_Exit(ByVal Cancel As MSForms.ReturnBoolean)   
  29.  
  30. If Not IsNumeric(Mid(Me.ID.Value, 2, 7)) Then
  31.    MsgBox "ID must be in the format of 'M0012345'."
  32.    Cancel = True
  33.    Me.ID.SetFocus
  34.    Me.ID.SelStart = 0
  35.    Exit Sub
  36. End If
  37. End Sub
  38. Private Sub ProjectCode_Exit(ByVal Cancel As MSForms.ReturnBoolean)
  39. If Not IsNumeric(Me.ProjectCode.Value) Then
  40.    MsgBox "Project Code must be numeric- (ex: '95714')."
  41.    Cancel = True
  42.    Me.ProjectCode.SetFocus
  43.    Me.ProjectCode.SelStart = 0
  44.    Exit Sub
  45. End If
  46.  
  47. If Len(Me.ProjectCode.Value) <> 5 Then
  48.    MsgBox "Project Code must be a length of five."
  49.    Cancel = True
  50.    Me.ProjectCode.SetFocus
  51.    Me.ProjectCode.SelStart = 0
  52.    Exit Sub
  53. End If
  54. End Sub
  55.  
Thanks,
Gregg
Jul 1 '07 #1
1 3677
Killer42
8,435 Expert 8TB
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.
Jul 1 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

12 posts views Thread by guy lateur | last post: by
reply views Thread by andreas | last post: by
1 post views Thread by Igor Mendizabal | last post: by
22 posts views Thread by Illya Havsiyevych | last post: by
1 post views Thread by Igor Mendizabal | last post: by
18 posts views Thread by Ann Scharpf via AccessMonster.com | last post: by
12 posts views Thread by Striker | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.