Automatically Tab to Next Field in Excel User Form 2

[strebor]

I have an Excel user form that has some 'fixed' length fields (two digits) and for convenience sake I would like to be able to enter the required number of digits and have the focus change to the next field without having to use the tab key or reposition the mouse cursor.

I've looked through a number of forums... I'm sure someone has done this, but I can't find anything about it.

strebor

 

[firefytr]

Hi there,

Yes, you can do this. Here is an example. I created a userform, added four textboxes to it and one command button, all default naming conventions used. I made use of the keyup event to check for the length of the value used, but it gives you the basic idea...

Private Sub CommandButton1_Click()
    Unload Me
End Sub

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(Me.TextBox1.Value) = 2 Then Me.TextBox2.SetFocus
End Sub
Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(Me.TextBox2.Value) = 2 Then Me.TextBox3.SetFocus
End Sub
Private Sub TextBox3_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(Me.TextBox3.Value) = 2 Then Me.TextBox4.SetFocus
End Sub
Private Sub TextBox4_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If Len(Me.TextBox4.Value) = 2 Then Me.CommandButton1.SetFocus
End Sub

HTH

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fumei]

If a TextBox has MaxLength = 2, and AutoTab = True, then typing:

23

will automatically move the focus to the next control in the Tab Order.

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

We must have sent at exactly the same time Zack.

Your code seems a little excessive.....

faq219-2884

Gerry
My paintings and sculpture

 

[fumei]

Just kidding by the way. There is no way I would be seriously critical of Zack. Howya doing?

faq219-2884

Gerry
My paintings and sculpture

 

[firefytr]

Yeah, excessive if you didn't know you could set the maxlength property. LOL! Thanks Gerry! Learn something new each day.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[firefytr]

I've been busy!! LOL! Classes are finally dying down. One left which ends mid-June. Finished the other big one last night. So glad I'm done! Now I can actually focus on my other class and make some of my kids baseball games!! I am sooo looking forward to this Memorial Day weekend break. I'm not on call or anything!! First *full* weekend I've had off since the first of the year.

But besides that, I've been really good. Thanks for asking Gerry. Hope you've been well yourself. I meant to email you again and see if you were going to make the trip to Seattle for a night or two, but like other things it got piled onto the list. I did manage to bump into Mr. Jollans a couple times while I was there. And some others from VBAX as well!

Overall I'm just glad I'm alive, to be able to still visit great places like these, and have such a damned supportive family. It's really, really good to be back.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[strebor]

Thank you all. And in less than 10 minutes I had 3 things that work... and I came up with a forth.

Private Sub txtHour_Change()
    If Len(txtHour.Text) = 2 Then
    txtMin.SetFocus
    End If
End Sub

But I really like Gerry's method. Thank you all.

strebor

 

[firefytr]

Yes, Gerry's method is best! Like my signature says, simplicity IS the ultimate sophistication. Any solution you can do natively will surely be better than with.

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci

 

[fumei]

I just wish you could do the same with formfields. Set a max length and an AutoTab. Sigh...oh well. This is one of the reasons why I (for the most part) prefer userforms to in-the-document formfields.

What I do not understand is why ActiveX textboxes in a document (NOT on a userform), have a MaxLength property and an AutoTab property...but they do not seem to work the same.

faq219-2884

Gerry
My paintings and sculpture

 

[firefytr]

I would assume it has to do with how they are more considered a shape and on a different 'layer' of the document, each shape being unto it's own and no real continuity between other shapes. At least that's my assumption... ;-)

Regards,
Zack Barresse

Simplicity is the ultimate sophistication. What is a MS MVP? PODA
- Leonardo da Vinci