Wednesday, July 14, 2010 3:00 PMI have a userform with 6 fields for data entry. I have used the BeforeUpdate event to validate the data entry into the fields. It works as expected with the following exception. When invalid data is entered into one of the fields (e.g., Field1 has alpha data in a numeric field), the event is triggered and an error message is produced. If I clear the error message and immediately close the window (i.e., press the X on the menu bar), the window disappears, but the BeforeUpdate event for that field fires twice, producing two instances of the error message for that field. Why does BeforeUpdate continue to fire? Why twice? How do I prevent it from happening?
Wednesday, July 14, 2010 4:59 PMModerator
Is this for MS Project (the topic of the forum)?
Wednesday, July 14, 2010 5:41 PMYes, Jan. I have a Userform capturing parameters such as the following: As Of Date, Cost Reserve, Duration Reserve, and so forth--all related to a VBA macro that runs within MS Project. The output is an analysis of project cost and schedule performance. Please let me know if you believe I would have more luck on a different forum.
Wednesday, July 14, 2010 8:31 PMModerator
I had to try it to see it; never tried that before.
Why? Who am I to look into the mind of the people who developped VBA?
If your beforeupdate puts cancel=false it doesn't happen.
Wednesday, July 14, 2010 8:53 PM
I agree, and Userforms seems to be especially weird. It's interesting that it stopped when you added cancel=false. I'm doing that, and I still get the strange behaviour. Here's a sample of the code (the items prefixed with "ufrmGlobal" are declared at the top of the Userform code):
Private Sub txtCostMgmtResAmount_BeforeUpdate(ByVal CancelFlag As MSForms.ReturnBoolean)
Dim CostRes As Single
CancelFlag = False
On Error Resume Next
CostRes = CDec(txtCostMgmtResAmount)
If Err <> 0 Then
CancelFlag = True
ufrmGlobalErrorFlag = True
ufrmGlobalErrorMsg(ufrmGlobalCostResErr) = "Cost entry must be a number in the correct format."
If CostRes > ActiveProject.ProjectSummaryTask.Cost Then
MsgBox "Warning: Cost reserve exceeds total project budget of " _
& Format(ActiveProject.ProjectSummaryTask.Cost, "#,###.##")
Thursday, July 15, 2010 8:24 AMModerator
What I meant was having the cancelflag set to false when EXITING the procedure. In your code it is still true when there is an error. Then the extra messages keep appearing.
Thursday, July 15, 2010 5:39 PMThanks, Jan. I placed CancelFlag=False in the Exit event associated with the field being validated. Doing so eliminated one of the error messages, but not both. Here's the sequence: input an invalid value in the field, close the window (i.e., press the X in the menu bar), the window disappears, the error message from the BeforeUpdate event appears. Is the Exit event the wrong place to reset the flag?
Thursday, July 15, 2010 7:37 PMModerator
Gosh, it's difficult to explain exactly what I mean.
Put Cancelflag=false as final statement in the beforeUpdate procedure
Thursday, July 15, 2010 10:50 PMJan, I was afraid you were going to say that. Setting the CancelFlag to False at the end of the sub means that the cursor moves to the next field, even though the data in the field being validated is still incorrect. I need to keep the cursor there until the input is correct, or the user cancels. I'm able to trap the cancel event, but when the user presses the menu X, I can't find a way to trap it.
Friday, July 16, 2010 1:36 PMModerator
In similar situations I use my own "cancel" such as setting the background of the control to red or in fact change the input. Besides I generally only test these situations at an OK closing of the form, simply refucing to hide it as long as the input isn't acceptable.
That's why you made me experiment with the beforeupdate event for the first time!
Saturday, July 17, 2010 7:16 PM
First, thanks for the help. Second, as it turned out, the problem was elsewhere. I had named the UserForm using my company's naming standards. Unfortunately, for events associated with the UserForm, the system does not recognize the name you have inserted in the "Name" field of the user form. It uses "Userform" as the prefix for associated events. To be safe, you should use the pick list of module names on the coding sheet. That's the best way to ensure the system associate events with the correct module. Once I did this for the QueryClose event and added code to that module, the validations worked as expected. Now, error messages are posted, the cursor remains on the problem field, and if the user presses the menu X, the macro closes as it should.
- Marked As Answer by RobVV Saturday, July 17, 2010 9:48 PM