MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Textbox number validation


Posted by Bob on June 08, 2001 11:09 AM

I asked this question a while back and got a long keypress function validation macro that I don't want to get into. I have 3 separate textboxes in a userform I am using for a social security number input, and want to make sure the user inputs 3 numbers in the first, 2 in the second, and 4 in the third. Most importantly, I want to make sure they're numbers. I've looked all over the internet and found certain code that sets the focus on the textbox, but I can't get that to work. No matter what I have done (using others' code), after pressing ok on the message box, it tabs to the next textbox. Here's what I have currently for the first textbox:

Private Sub TextBox3_Exit(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsNumeric(TextBox3.Value) Then
MsgBox "INVALID ENTRY"
With TextBox3
.SetFocus
.SelStart = 0
.SelLength = 1000
End With
End If
End Sub

This works for the error, but goes on to wherever I clicked the mouse on after pressing ok. I have also tried the cancel = true and tryagain = true type routines I've found, but still can not get the cursor back into textbox3. Any help is greatly appreciated.


Posted by Damon Ostrander on June 11, 2001 9:32 AM

Hi Bob,

Simply use the TextBox3_BeforeUpdate event rather than the Exit event. In your If block set Cancel to True. Then when you enter bad data into the textbox, control will never leave it and you won't even have to use the SetFocus method to keep the focus on it.

Damon

Posted by Bob on June 11, 2001 10:22 AM

Damon, that works great without using the msgbox, but if I use the message box to alert the user, it doesn't put the focus back on the userform (or the textbox for that matter). Any ideas for this? Here's what I'll use instead I guess:

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If IsNumeric(SS1) And Len(SS1) = 3 Then
Exit Sub
Else:
'MsgBox "ENTRY MUST BE A 3 CHARACTER NUMBER."
Cancel = True
With TextBox3
.Text = "###"
.SelStart = 0
.SelLength = 1000
.SetFocus
End With
End If
End Sub

I don't know how to use the messagebox and put the focus back on textbox3. Am I still missing something? Thanks for your help in any case Damon.