Results 1 to 11 of 11
  1. #1
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts

    Choosing AfterUpdate or Exit events: unique problems with both

    I am at loss of how to tackle this particular problem I am having with a textbox event on my userform.

    Please consider this code:

    Code:
    Private Sub tb_r1_sru_afterupdate() '(ByVal CANCEL As MSForms.ReturnBoolean)
        Dim mbevents As Boolean
        
        mbevents = True
        If IsDate(Me.tb_r1_sru.Value) Then
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
        Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Me.tb_r1_sru.Value = ""
            Me.tb_r1_sru.SetFocus
            'CANCEL = True
            Exit Sub
        End If
        
        tb_r1_srl.Locked = False
        tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
        mbevents = False
    
    
    End Sub
    This 'afterupdate' event code for textbox 'tb_r1_sru' works in catching errors of the user in entering an invalid time entry. The challenge I am having is sending the cursor back to textbox 'tb_r1_sru' to allow the user to re-enter the value. The cursor, after invalid entry, goes to the next textbox on the userform.

    Using
    Code:
    tb_r1_sru.setfocus
    failed to do what I had hoped.

    Adpating this procedure with an 'exit' event (and adding CANCEL=TRUE) rather than an 'afterupdate' event resolved that issue for me, but introduced another problem. If the user hit the CANCEL commandbutton of the userform, the exit event code would trigger and send out the invalid time message before closing the userform. This unwanted behaviour is what I need to correct with this option.

    So, I'm not sure what would be easiest to resolve. I would be grateful for any help in helping me choose the best option with proper feedback and suggestions to rid each of their respective drawbacks.
    Last edited by JennEx; 2016-03-04 at 08:25.

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jenn,

    You might want to consider using BeforeUpdate as you are error checking and that is the time to do that. Consider this code:

    Code:
    Private Sub tb_r1_sru_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
        If IsDate(Me.tb_r1_sru.Value) Then
          Application.EnableEvents = False
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
            tb_r1_srl.Locked = False
            tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
    		tb_r1_srl.Locked = True  '????? Maybe
         Application.EnableEvents = True 	
       Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Me.tb_r1_sru.Value = ""
            Me.tb_r1_sru.SetFocus
            CANCEL = True
        End If 
    
    End Sub   'tbl_r1_sru_BeforeUpdate
    When changing form values in an Event procedure you should always disable events [Application.EnableEvents = False] to prevent endless calls to the procedure or other event procedures if you are changing other fields as you are in this case.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    kmurdock (2016-03-05)

  4. #3
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Thank you so much! I am so grateful for your support.

    Interesting use of 'application.enableevents'. I was always under the impression this didn't work for suspending object events in a userform. I always had to try to wrap my head around using, in my case, mbEvents as boolean. This seems so much easier to understand when to use it.

    The only problem I am having now, is the command button to cancel doesn't seem to work. Rather than closing the userform, it is just cycling through the 'Else' condition of your code. This happens only if the user enters an invalid entry first. Subsequent attempts to CANCEL fails. If CANCEL is selected as the first action once the userform is open, it will work.

    Code:
    Private Sub rl_cancel_Click()
        Unload Me
    End Sub

  5. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jenn,

    You can't exit a form with an error on it w/o either clearing the error or doing some nasty coding to get around it.

    Perhaps...
    Code:
    Private Sub tb_r1_sru_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
        If IsDate(Me.tb_r1_sru.Value or Me.tb_r1_sru.value = "") Then
          Application.EnableEvents = False
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
            tb_r1_srl.Locked = False
            tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
    		tb_r1_srl.Locked = True  '????? Maybe
         Application.EnableEvents = True 	
       Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Application.EnableEvents = False
              Me.tb_r1_sru.Value = ""  '*** As an alternative you could set this to 24:00 ***
              Me.tb_r1_sru.SetFocus
              CANCEL = True
            Aplication.EnableEvents = True
        End If 
    
    End Sub   'tbl_r1_sru_BeforeUpdate
    BTW: A sterilized version of your workbook would be of great help in solving problems.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  6. #5
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I would love to be able to share a "sterilized" version of my workbook, but I think it would create more problems than good. It relies on several networked files for which I would have to include and clean up any links in the code. It's kinda large at 992kB, and I's be embarrassed to expose my novice understanding of VBA. If it boils down to having to, I can put forth the effort.

    With your latest suggestion, I am getting a 'Type mismatch' error with this line unfortunately.

    Code:
    If IsDate(Me.tb_r1_sru.Value Or Me.tb_r1_sru.Value = "") Then

  7. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jenn,

    OOPS! My Bad.

    Code:
    If (IsDate(Me.tb_r1_sru.Value) Or (Me.tb_r1_sru.Value = "")) Then
    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  8. #7
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    I'm starting to see ...

    or doing some nasty coding to get around it
    Same error, same conditions to recreate asin post #3, but different line ..

    Code:
    Private Sub tb_r1_sru_beforeupdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
        If (IsDate(Me.tb_r1_sru.Value) Or (Me.tb_r1_sru.Value = "")) Then
            Application.EnableEvents = False
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
            tb_r1_srl.Locked = False
            tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
            Application.EnableEvents = True
        Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Application.EnableEvents = False
            Me.tb_r1_sru.Value = ""
            Me.tb_r1_sru.SetFocus
            CANCEL = True
            Application.EnableEvents = True
        End If
    End Sub
    At the point of error , tb_r1_sru.Value = "".

  9. #8
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jenn,

    This is exactly why a sample is very useful.

    Oddly this method should work but doesn't!:
    Code:
    Option Explicit
    
    Private Sub tb_r1_sru_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
        Select Case tb_r1_sru
        
          Case IsDate(tb_r1_sru)
            Application.EnableEvents = False
            tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
    '        tb_r1_srl.Locked = False
    '        tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
            Application.EnableEvents = True
            
          Case ""
            '*** Do Nothing ***
            
          Case Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Application.EnableEvents = False
            tb_r1_sru.Value = ""
            tb_r1_sru.SetFocus
            CANCEL = True
            Application.EnableEvents = True
            
        End Select
        
    End Sub
    
    Private Sub cmdExit_Click()
    
       Unload Me
       
    End Sub
    This construct (same basic logic) does work:
    Code:
    Option Explicit
    
    Private Sub tb_r1_sru_BeforeUpdate(ByVal CANCEL As MSForms.ReturnBoolean)
    
        
        If (IsDate(tb_r1_sru.Value)) Then
        
            Application.EnableEvents = False
             tb_r1_sru.Value = Format(tb_r1_sru.Value, "h:mm AM/PM")
    '        tb_r1_srl.Locked = False
    '        tb_r1_srl.Value = TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0)
            Application.EnableEvents = True
            
        Else
        
          If (tb_r1_sru.Value = "") Then
            '*** Do Nothing ***
          Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
            Application.EnableEvents = False
            tb_r1_sru.Value = ""
            tb_r1_sru.SetFocus
            CANCEL = True
            Application.EnableEvents = True
           End If   '= ""
           
        End If 'IsDate....
        
    End Sub
    
    Private Sub cmdExit_Click()
    
       Unload Me
       
    End Sub
    Ok, I tested this code with a bare bones form and it works. You'll notice that I commented out 2 lines because I didn't put that control on my form. You can uncomment when you copy it into yours.

    Here's the test file: JennExTestForm.xlsm

    HTH
    Last edited by RetiredGeek; 2016-03-04 at 21:20.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  10. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JennEx (2016-03-06)

  11. #9
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    With you generous help, I think we've tackled the problem.

    I had to make one change to your code. It was necessary to move
    Code:
    If (tb_r1_sru.Value = "") Then
    up to be the first test. It was needed to exit the code for any blank entries or the cancel button.

    Code:
    Private Sub tb_r1_sru_beforeupdate(ByVal CANCEL As MSForms.ReturnBoolean)
        Me.cb_r1_crew.Enabled = False
        If (tb_r1_sru.Value = "") Then
            ' *** not nothing ***
        ElseIf (IsDate(Me.tb_r1_sru.Value) Or (Me.tb_r1_sru.Value = "")) Then
            Application.EnableEvents = False
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
            tb_r1_srl.Locked = False
            tb_r1_srl.Value = Format(TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0), "h:mm AM/PM")
            Application.EnableEvents = True
            Me.rrl_submit.Enabled = True
            Me.cb_r1_crew.Enabled = True
        Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
                Application.EnableEvents = False
                Me.tb_r1_sru.Value = ""
                Me.tb_r1_sru.SetFocus
                CANCEL = True
                Application.EnableEvents = True
        End If
    End Sub
    Jenn

  12. #10
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,433
    Thanks
    371
    Thanked 1,456 Times in 1,325 Posts
    Jenn,

    The original code should work fine. You changed the code structure by going to an ElseIf (I personally don't like this construct as it makes it harder to match up the If-Then-Else blocks IMHO).

    Your code now has a redundant test:
    Code:
    Private Sub tb_r1_sru_beforeupdate(ByVal CANCEL As MSForms.ReturnBoolean)
        Me.cb_r1_crew.Enabled = False
        If (tb_r1_sru.Value = "") Then
            ' *** not nothing ***
        ElseIf (IsDate(Me.tb_r1_sru.Value) Or (Me.tb_r1_sru.Value = "")) Then
            Application.EnableEvents = False
            Me.tb_r1_sru.Value = Format(Me.tb_r1_sru.Value, "h:mm AM/PM")
            tb_r1_srl.Locked = False
            tb_r1_srl.Value = Format(TimeValue(tb_r1_sru.Value) + TimeSerial(0, 30, 0), "h:mm AM/PM")
            Application.EnableEvents = True
            Me.rrl_submit.Enabled = True
            Me.cb_r1_crew.Enabled = True
        Else
            MsgBox "Please enter a valid time (eg. '24:00' or '12:00 PM')"
                Application.EnableEvents = False
                Me.tb_r1_sru.Value = ""
                Me.tb_r1_sru.SetFocus
                CANCEL = True
                Application.EnableEvents = True
        End If
    End Sub
    Since it can't get to the ElseIf with a blank value the OR is totally unnecessary.

    BTW, in form code the Me. is redundant and just clouds up the code, again IMHO.

    I'd be interested what didn't work for you that you had to change the code sequence.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  13. The Following User Says Thank You to RetiredGeek For This Useful Post:

    JennEx (2016-03-16)

  14. #11
    New Lounger
    Join Date
    Jan 2016
    Posts
    22
    Thanks
    4
    Thanked 0 Times in 0 Posts
    Hi, sorry for the delay in acknowledging your latest reply. I appreciate the extra effort in sharing your recent observations, and with thanks, I note your comments of improving my code. When I'm writing my code (and it is part of being new and not formally educated in tese matters) the If elseif endif structure helps me think logically as I consider options. The Case structure is much cleaner admittingly, but I've found it awkward in my initial writing. Usually, once I can get the if endif structure working, I will convert it over to the cleaner Select Case format. So, your comments haven't fallen on deaf ears ;-)

    I'd be interested what didn't work for you that you had to change the code sequence.
    Unfortunately, what ever happened is long forgotten and has been replaced by bigger and better problems. Unless it causes me problems in the future, and in the essence of time, I have to be satisfied with how it works now.

    Thanks for your support!

Posting Permissions

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