switch automatically between text boxes

[Valeria]

Dear experts,
I have a spreadsheet (excel 2003) which contains a userform wiht 3 textboxes.
Users need to enter their input on textbox 1 &3, the one in the middle has a
set value.
textbox 1 = 4 characters
textbox 3 = 2 characters
I would like the users to be able to input the values for textboxes 1 & 3
without having to hit the tab key, so basically input the 6 characters at the
time which would split into the textboxes 1 & 3

Is this possible?
Thanks for your help,
Best regards

 

[Mike H]

Hi,

You could attach this code to textbox1

Private Sub TextBox1_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
If Len(TextBox1.Text) <> 6 Then
MsgBox "Wrong number of characters, Enter 6 numbers"
TextBox1.Text = ""
Exit Sub
End If
TextBox3.Text = Right(TextBox1, 4)
TextBox1.Text = Left(TextBox1, 2)
End Sub

Mike

 

[Valeria]

Hi Mike,
I think your code works on doubleclick - I actually have an OK button which
the user will have to click on when he has finished entering his code.
Also, it is important for the user to see that he is entering the 2 inputs
on 2 separate boxes - and that the first 4 digits are before the fixed input
and the last 2 afterwards (it is a batch number - so I want to avoid any
possible confusion).
This is why I was thinking about the possibility of Excel directly switching
from the 1st textbox to the 3rd one after the first 4 digits are inputed...
Thanks!
Kind regards

 

[Mike H]

Hi,

You need an event which can be an inbuilt one such as double_click or a
manual one like you button press so attach the same code to your button.

You could use the EXIT event of textbox 1 in a similar way

Private Sub CommandButton1_Click()
If Len(TextBox1.Text) <> 6 Then
MsgBox "Wrong number of characters, Enter 6 numbers"
TextBox1.Text = ""
Exit Sub
End If
TextBox3.Text = Right(TextBox1, 2)
TextBox1.Text = Left(TextBox1, 4)
End Sub

Mike

 

[Valeria]

Hi Mike,
with a huge delay ( I was on something else in the meatime): thank you! It
worked very well.
Kind regards