How to ignore or override the BeforeUpdate event if user cancels

[Guest]

Example
1. User is restricted by BeforeUpdate to entering number from 1-4 in text box.
(This is simplified and could really involve a database lookup.)
2. Form has a Cancel button and of course the red-X intends to close the form
3. User enters a 5 and gets MsgBox and is locked into the text box
4. User leaves 5 in text box and wants to immediately Cancel (close the form)

Is there a technique where the user could hit the Cancel button and get out
of the form even though the BeforeUpdate keeps wanting to fire before the
Cancel button can be recognized?

Or is this form behavior that just has to be lived with?

Example code for text box is:
Private Sub Text2_BeforeUpdate(Cancel As Integer)
If IsNumeric(Text2) = False Then
MsgBox "Enter a number from 1 to 4"
Cancel = True
ElseIf CDbl(Text2) < 1 Or CDbl(Text2) > 4 Then
MsgBox "Enter a number from 1 to 4"
Cancel = True
End If
End Sub

 

[UpRider]

Use the msgbox to advise the user to tap ESC once or twice.

UpRider

 

[John W. Vinson]

Example
1. User is restricted by BeforeUpdate to entering number from 1-4 in text box.
(This is simplified and could really involve a database lookup.)

Why not use a list-of-values combo box presenting only the valid numbers, so
the user can choose one - instead of letting them type anything and then
slapping their hand? If the user is tabbing through the form, they can type a
4 (just as in a textbox); they don't need to use the mouse.

2. Form has a Cancel button and of course the red-X intends to close the form
3. User enters a 5 and gets MsgBox and is locked into the text box
4. User leaves 5 in text box and wants to immediately Cancel (close the form)

Is there a technique where the user could hit the Cancel button and get out
of the form even though the BeforeUpdate keeps wanting to fire before the
Cancel button can be recognized?

If (as may well happen) you need to use a textbox anyway, you can use the
Function form of the MsgBox command:

Private Sub Text2_BeforeUpdate(Cancel As Integer)
Dim iAns As Integer
If IsNumeric(Text2) = False Then
iAns = MsgBox("Enter a number from 1 to 4", vbYesCancel)
Cancel = True
If iAns = vbCancel Then
Me.Undo ' erase the record
End If
ElseIf CDbl(Text2) < 1 Or CDbl(Text2) > 4 Then
iAns = MsgBox("Should be 1-4. To add anyway click Yes," _
" to make another selection click No, to start over click Cancel"", _
vbYesNoCancel
Select Case iAns
Case vbNo
Cancel = True
Me.Text2.Undo ' just undo the textbox
Case vbCancel
Cancel = True
Me.Undo ' undo the whole form
Case Else
' do nothing
End Select
End If
End Sub

John W. Vinson [MVP]

 

[Guest]

The first answer requires the user to do extra, (Windows) un-intuitive action
of escaping before you can Exit.
The second answer would work but is not my actual problem.

Sorry, I left out two details:
1. Text box is not bound so .UNDO doesn't work.
2. User wants available a full range of values from 1 to 4 as used in
example. Note a slider will not work because user wants to put in precise
values such as 3.6125 or 2.25

Maybe the first answer is the only decent workaround, I was hoping the user
would only have to hit the X and the form was gone.
Allen

 

[Guest]

I added
SendKeys "{ESC}"
following the MsgBox alerting the user to the error.
The SendKeys reverted the text box contents to prior, valid data and left
the focus in the text box.
Then hitting the X-exit closes form with no further complaint.
This is exactly the result I wanted.
Thanks UpRider

 

[John W. Vinson]

I added
SendKeys "{ESC}"
following the MsgBox alerting the user to the error.

Me.Undo has the same effect and avoids the many irksome problems with
Sendkeys.

John W. Vinson [MVP]