Solved: TextBox Character limit

[Emoncada]

I have a few Textboxes in a UF that I would like to do the following.
When Character length hits (10) then Tab to the next TextBox.

Can this be possible?

Any help would be great

[nst1107]

Check this out:Option Explicit
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1)
Case 32 To 127
If Len(TextBox1) = 10 Then TextBox2.SetFocus
End Select
End Sub

[Emoncada]

AWESOME!! Thank You that worked great.

[mdmackillop]

Very neat! :clap2: :clap2: :clap2:

[Emoncada]

Ok I have a little issue. It works great but how can i put
something like this

if TextBox2.visible = False then Go to Next 'visible Tab
Else
TextBox2.SetFocus


Into

Case 32 To 127
If Len(TextBox1) = 10 Then TextBox2.SetFocus
End Select

[nst1107]

Not sure exactly what you need here. Something like this?Option Explicit
Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1)
Case 32 To 127
If Len(TextBox1) = 10 Then
If TextBox2.Visible = False Then
TextBox3.SetFocus
Else
TextBox2.SetFocus
End If
End If
End Select
End Sub

[Emoncada]

Ok Im Stuck. I looked at this code and don't understand why it's doing what it's doing. Here is what I got.

Private Sub TextBox1_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox1.SelStart = 0
TextBox1.SelLength = Len(TextBox1)

Case 32 To 127
If Len(TextBox1) = 10 Then
If TextBox2.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox2.SetFocus
End If
End If
End Select

End Sub
Private Sub TextBox2_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox2.SelStart = 0
TextBox2.SelLength = Len(TextBox2)

Case 32 To 127
If Len(TextBox2) = 10 Then
If TextBox3.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox3.SetFocus
End If
End If

End Select

End Sub
Private Sub TextBox3_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox3.SelStart = 0
TextBox3.SelLength = Len(TextBox3)

Case 32 To 127
If Len(TextBox3) = 10 Then
If TextBox4.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox4.SetFocus
End If
End If

End Select
End Sub
Private Sub TextBox4_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox4.SelStart = 0
TextBox4.SelLength = Len(TextBox4)

Case 32 To 127
If Len(TextBox4) = 10 Then
If TextBox5.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox5.SetFocus
End If
End If

End Select
End Sub
Private Sub TextBox5_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox5.SelStart = 0
TextBox5.SelLength = Len(TextBox5)
Case 32 To 127
If Len(TextBox5) = 10 Then
If TextBox6.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox6.SetFocus
End If
End If
End Select
End Sub
Private Sub TextBox6_KeyUp(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
Select Case KeyCode
Case vbKeyTab Or vbKeyReturn
TextBox6.SelStart = 0
TextBox6.SelLength = Len(TextBox6)

Case 32 To 127
If Len(TextBox6) = 10 Then
If TextBox7.Visible = False Then
TextBoxTrk1.SetFocus
Else
TextBox7.SetFocus
End If
End If

End Select
End Sub

Etc...

I have 30 of those. Now what it's doing is lets say
TextBox7.Visible = False instead of going to TextBoxTrk1 It's going to TextBox3

Now it works perfectly if I manually type in textboxes but if I scan into them this happens does someone know why?

[nst1107]

30 of them... Sounds like fun. What do you mean by "scan into"?

[mdmackillop]

You can cut out a lot of repetition by passing variables to another sub. For example


Private Sub TextBox1_KeyUp(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
DoKeyUp TextBox1, keycode, TextBox2
End Sub

Private Sub TextBox2_KeyUp(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
DoKeyUp TextBox2, keycode, TextBox3
End Sub

Private Sub TextBox3_KeyUp(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
DoKeyUp TextBox3, keycode, TextBox4
End Sub

Sub DoKeyUp(TB, keycode, TB2)
Select Case keycode
Case vbKeyTab Or vbKeyReturn
TB.SelStart = 0
TB.SelLength = Len(TB)

Case 32 To 127
If Len(TB) = 10 Then
If TB2.Visible = False Then
TextBoxTrk1.SetFocus
Else
TB2.SetFocus
End If
End If
End Select
End Sub

[Emoncada]

Thanks MD, but im still having this issue and i don't know why. I have a barcode scanner and it works fine, but when it should go to TextBoxTrk1 it doesn't it goes to TextBox3. I am unsure why.

[mdmackillop]

Try this, adding a Change event for each textbox.


Private Sub TextBox1_Change()
DoChange TextBox1, TextBox2
End Sub

Private Sub TextBox1_KeyUp(ByVal keycode As MSForms.ReturnInteger, ByVal Shift As Integer)
DoKeyUp TextBox1, keycode, TextBox2
End Sub

Sub DoChange(TB, TB2)
If Len(TB) >= 10 Then
If TB2.Visible = False Then
TextBoxTrk1.SetFocus
Else
TB2.SetFocus
End If
End If
End Sub

Sub DoKeyUp(TB, keycode, TB2)
Select Case keycode
Case vbKeyTab Or vbKeyReturn
TB.SelStart = 0
TB.SelLength = Len(TB)

Case 32 To 127
If Len(TB) = 10 Then
If TB2.Visible = False Then
TextBoxTrk1.SetFocus
Else
TB2.SetFocus
End If
End If
End Select
End Sub

[Emoncada]

Still having the same issue. Is there a way then instead of testing for visible to just have it go to the next tab? Based on Tab order?

[mdmackillop]

I cannot test a BarCode input. Have you tried setting breakpoints and stepping through the code/using Watch values to follow what is happening?

[Emoncada]

NO How can i do that?

[mdmackillop]

In the VBE click Debug. Use Toggle Breakpoint to stop the code execution and press F8 to step one line at a time. Use Add Warch to see values of variables.