Results 1 to 2 of 2

Prevent unwanted beforeUpdate event

Threaded View

bdoodle Prevent unwanted beforeUpdate... 11-15-2011, 11:06 AM
bdoodle Re: Prevent unwanted... 11-15-2011, 03:49 PM
  1. #1
    Registered User
    Join Date
    07-13-2011
    Location
    Boston, MA USA
    MS-Off Ver
    Excel 2010
    Posts
    28

    Prevent unwanted beforeUpdate event

    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!
    Last edited by bdoodle; 11-15-2011 at 03:50 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1