Please advise:
I'm trying to avoid a BeforeUpdate event from being triggered as a result of actions in an InputBox (within the same BeforeUpdate procedure) I use to ask the user to confirm (or deny) the outrageous value just entered. If the user does confirm the value in the InputBox, everything works okay. If the user either hits the Cancel button in the InputBox or does not confirm the input value, the code works but triggers an immediate return to the BeforeUpdate procedure. (It issues the error msg in the beginning of the subroutine that I'm missing an input value.)
I'm using EnableEvents = False, but it isn't working. The problem is in my "validity check" and my code follows:
Private Sub txtFlexROM_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
FlexROMIsInvalid = False
txtFlexRoundedROM = ""
txtFlexImp = ""
If chkFlexUnavail.Value = False Then
If IsEmpty(txtFlexROM) = True Then
MsgBox "You must enter a valid Flexion measurement or click unavailable.", vbOKOnly
Exit Sub
End If
If IsNumeric(txtFlexROM) = False Then
MsgBox "You must enter a valid Flexion measurement number or click unavailable.", vbOKOnly
Exit Sub
End If
Application.EnableEvents = False
' test for validity
If (Round(txtFlexROM / 10, 0) * 10) < -40 Or _
(Round(txtFlexROM / 10, 0) * 10) > 220 Then
vConfirmInput = ""
On Error GoTo Canceled
vConfirmInput = Application.InputBox _
(Prompt:="This Flexion measurement is invalid. " _
& "Hit Cancel to enter a different value. To confirm it, please re-enter it and hit Okay.", _
Title:="Cancel or Confirm Flexion measurement", Type:=1)
If IsEmpty(vConfirmInput) = False And IsNumeric(vConfirmInput) And _
vConfirmInput - txtFlexROM = 0 Then
FlexROMIsInvalid = True
txtFlexROM.ForeColor = &H8000000D
Application.EnableEvents = True
Exit Sub
Else
txtFlexROM = ""
chkFlexUnavail.SetFocus
txtFlexROM.SetFocus
Application.EnableEvents = True
Exit Sub
End If
End If
Other code follows...
Canceled:
Application.EnableEvents = True
End Sub
Thanks for any help!
Bookmarks