Results 1 to 11 of 11
  1. #1
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I need help figuring out a solution to a user form problem I have. I have a Text Box that requires the last 4 digits entered into that textbox to be numeric. If it is not, the field will clear and allow the user to resubmit the entry. This is currently running on the After Update Event. The problem I'm having is when the invalid entry is made, the textbox is cleared and then the form auto tabs to the next control. I'm having trouble getting the focus to stay on the textbox. I've also noticed that because I'm changing the textbox value to nothing ("") in the after update event. I'm getting the msgbox to display twice. I'd like to avoid that msgbox from displaying twice. I'm using Excel 2003 and Windows XP. Below is what I'm currently using on this event. I would appreciate any help or suggestions with this. Thanks in advance.


    Private Sub TextBox10_AfterUpdate()
    If Right(TextBox10.Value, 4) Like "####" Then
    Exit Sub ' The last 4 characters are all numeric, Exit the after update event.
    Else
    ' The last 4 characters are not all numeric.
    MsgBox ("This field requires the value to be entered in a specific format."),vbCritical, "Invalid format"
    TextBox10.Value = ""
    End If
    End Sub

  2. #2
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Use "Exit". Set Cancel to true to stop the cursor from exiting.

    Private Sub tbOne_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If tbOne.Value <> "1234" Then
    tbOne.Value = ""
    Cancel = True
    End If
    End Sub

  3. #3
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Using Exit doesn't seem to work. If I enter any value in the textbox, then hit tab. The focus moves to the next control. It doesn't even check to validate if the value entered is in the correct format. That is why I used after update. From there, I can tab to any other control and the only way the exit event is called is if I use the mouse and click on either the Textbox itself or the textbox control just prior to TextBox 10 in the tab order. I need the focus to remain with the current textbox and not require the user to have to click or tab back.


    Private Sub TextBox10_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If Right(TextBox10.Value, 4) Like "####" Then
    ' The last 4 characters are all numeric.
    Exit Sub
    Else
    ' The last 4 characters are not all numeric.
    TextBox10.Value = ""
    Cancel = True
    MsgBox ("This field requires the value to be entered in a specific format. Please try again."),vbCritical, "Invalid format"
    End If
    End Sub

  4. #4
    2 Star Lounger
    Join Date
    Mar 2002
    Location
    Western NY, USA
    Posts
    157
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Check to see what other events you have associated with the text box. Your code should work.

    the exit sub is not needed, the if statement will suffice, but the code works either way.

    I tried it by exiting the text control via the tab key and the mouse. Both work.

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Excel Forms and Controls are the poor relative of the VB world
    But try sending SHIFT TAB to the form with Send Keys and see if it works

    Code:
    Private Sub TextBox10_AfterUpdate() 
         If Right(TextBox10.Value, 4) Like "####" Then
                Exit Sub ' The last 4 characters are all numeric, Exit the after update event.
         Else
               ' The last 4 characters are not all numeric.
               MsgBox ("This field requires the value to be entered in a specific format."),vbCritical, "Invalid format"
               TextBox10.Value = " "
               SendKeys "+{TAB}"
         End If
    End Sub
    Andrew

  6. #6
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Another Itteration...just so you have options.

    Code:
    Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    
           If Right(TextBox1.Value, 4) Like "####" Then
                Exit Sub ' The last 4 characters are all numeric, Exit the after update event.
         Else
               ' The last 4 characters are not all numeric.
               MsgBox ("This field requires the value to be entered in a specific format."), vbCritical, "Invalid format"
               Application.EnableEvents = False
               TextBox1.Value = " "
               TextBox1.SetFocus
               Application.EnableEvents = True
               Cancel = True
         End If
         
    End Sub
    I tested this on A Win-7 machine running Excel 2003 SP-3 and all worked as you wish.
    Note: if you copy the text notice that I changed your TextBox number to 1 from 10.

    It's probably not necessary to say this but just in case you're new to interface design your error message should state that the last 4 characters need to be numeric...unless of course you work for some 3 lettered agency

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  7. #7
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Good Call.

    I was going to post that, but I forgot the
    Application.EnableEvents = False
    and without that it doesn't work.

    AW
    Andrew

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts
    Actually, Application.EnableEvents has no effect on userforms at all. If you want to stop events on a form, you need to use your own boolean flag variable.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Star Lounger
    Join Date
    Mar 2008
    Posts
    98
    Thanks
    0
    Thanked 0 Times in 0 Posts
    First off, thank you to those that provided responses or just took the time to read this post. I've figured out why this didn't want to work when it should have been so simple. The text box that I was having the issue with was inside a frame. When I used the on Exit event for the textbox that was in the frame. The exit event didn't execute until I changed the focus to another control within the same frame. Any button or textbox within that frame that was clicked caused the textbox 10 on exit event to execute. What I needed to do to fix this issue was since textbox 10 was the last control to be edited within the current frame. I needed to use the on Exit event for the frame instead of the textbox. This way when the user tabbed to the next control which was in another frame. The On exit event for the frame would execute and perform the validation on the textbox and leave the focus there if the response was not valid. Otherwise the focus would shift to the next control in the tab order. I should have thought of this sooner but it's one of those problems where you just have to step away for a while and re-think it. Thanks again for everybody's help. Hopefully this info will be valuable to anybody else reading this post.

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by rory View Post
    Actually, Application.EnableEvents has no effect on userform at all. If you want to stop events on a form, you need to use your own boolean flag variable.
    Absolutely correct Rory.
    It was very late and I was not reading the thread correctly.
    Actually I am not sure I was thinking correctly either.

    I assumed it had worked, but did not spot it was using the Exit Event and Cancel
    rather than the After Updated Event.
    Andrew

  11. #11
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    371
    Thanked 1,457 Times in 1,326 Posts
    Gee, that's a new on on me...Thanks it will certainly change how I code things. I did a little searching and here's a link to an article that explains how to handle events in forms, etc. Events in Forms

    Any day you learn something new is a GOOD day.

    RG
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

Posting Permissions

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