View Full Version : forms-comboboxes & textboxes

09-21-2007, 10:57 AM

i would like to restrict the entry into a combobox and a textbox and also for the cursor to move to the next text or combobox

for example

in textbox1 i would like the entry to be restricted to a number between 10000 and 99999 then once the five digits have been entered for the cursor to move to combobox1 and for this entry to be restricted to an entry from the dropdown


09-21-2007, 01:01 PM
This should take care of the textbox validation.
Private Sub TextBox1_Change()
Static abort As Boolean
If abort Then abort = False: Exit Sub
With Me
If Val(.TextBox1.Text) <> .TextBox1.Value Then
abort = True
.TextBox1.Text = Format(Val(.TextBox1.Text), "0")
End If
If Len(.TextBox1.Text) >= 5 Then Me.ComboBox1.SetFocus
End With
End Sub

The rest can be done by setting the .MatchRequired property to True.

09-21-2007, 02:55 PM
Is this the same method used when say entering in a serial or product number for new software where it boots you across to the next textbox?

09-21-2007, 03:32 PM
In userforms, there are two ways to go from control to control using the keyboard

The technique in the code uses the .SetFocus method(?) to set the focus to a particular control.

The other, more common technique, uses the Tab Order to control what control gets the focus after the user presses Enter or Tab.

Since the request was to move the focus after the (fifth) press of a number key, the normal Tab Order technique wouldn't work, requiring the .SetFocus technique of focus control.

09-22-2007, 06:21 AM
You could also:
1. Set the Textbox's MaxLength property to 5
2. Set its Autotab property to True
3. Use the BeforeUpdate event to validate:

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not (Val(TextBox1) >= 10000 And Val(TextBox1) <= 99999) Then
MsgBox "Value must be between 10000 and 99999"
Cancel = True
End If
End Sub