User Form tabbing

Topic

New Reply

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

Reply | Quote

Replies

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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 

Reply | Quote

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!
Computer Specs

Reply | Quote

Good Call.

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

AW

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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!
Computer Specs

Reply | Quote