473,372 Members | 1,052 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,372 software developers and data experts.

VBA- Order of Precedence for Events


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)
  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
  28. Private Sub ID_Exit(ByVal Cancel As MSForms.ReturnBoolean)   
  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
  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
Jul 1 '07 #1
1 3814
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

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

Similar topics

by: guy lateur | last post by:
Hi all, I am trying to write some code (macro's, if you like) to glue together our Office applications (mainly Word, Excel and Outlook). We have a lot of different projects going on...
by: andreas | last post by:
Hi there, I have the following situation: 1. DOT.NET Application is started 2. DOT.NET Application instantiates Access.Application.8 3. Opens a specified database (MDB) 4. DOT.NET...
by: Igor Mendizabal | last post by:
Hello We have a VB6 application that integrates an VBA IDE from which the final user can make modifications. We want to pass the application to VB.NET, and the first step, before rethinking...
by: Illya Havsiyevych | last post by:
Hello How easily parse VB/VBA code by VB/VBA code ? Is any ready solutions ? Thank's, illya
by: Matt Alanzo | last post by:
Our SOHO 2 person compay sells furniture (not programmers). In '98 we paid $,$$$ for a VBA -Access '97 accounting application, including VBA source code .... an huge investment for us then (and...
by: Terry Bell | last post by:
We've had a very large A97 app running fine for the last seven years. I've just converted to SQL Server backend, which is being tested, but meanwhile the JET based version, running under terminal...
by: Igor Mendizabal | last post by:
Hello We have a VB6 application that integrates an VBA IDE from which the final user can make modifications. We want to pass the application to VB.NET, and the first step, before rethinking...
by: Ann Scharpf via AccessMonster.com | last post by:
I am not sure which would be the best place to post this question, so I'm posing it here with Access general questions. I have reached the point many times in Word and in Access where my ignorance...
by: Striker | last post by:
I have never used VB, so sorry for the novice questions. I have a very small app in vba that only uses excel as a place to put a button and some code that I need to move to VB. Now it seems...
by: MMcCarthy | last post by:
Although some users find Macros simple and easy to use, there are some major limitations to using them. Although you can use macros to perform tasks, there is limited control on when and how those...
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...
by: ryjfgjl | last post by:
In our work, we often need to import Excel data into databases (such as MySQL, SQL Server, Oracle) for data analysis and processing. Usually, we use database tools like Navicat or the Excel import...
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
by: ryjfgjl | last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.