PDA

View Full Version : Solved: Loop to check numeric in userform textbox



blackie42
10-22-2007, 05:52 AM
HI - I have around 50 textboxes in a userform and want to make sure only
numbers entered. Rather than repeat this code I'm looking for a loop that will check each one conforms. Any help appreciated

If (Not IsNumeric(TextBox1.Value) And (TextBox1.Value <> "")) Then
MsgBox "Numbers only"
TextBox1.Value = ""
Exit Sub
End If
If (Not IsNumeric(TextBox2.Value) And (TextBox2.Value <> "")) Then
MsgBox "Numbers only"
TextBox2.Value = ""
Exit Sub
End If

many thanks

Bob Phillips
10-22-2007, 06:01 AM
Dim ctl As MSForms.Control
For Each ctl In UserForm1.Controls
If TypeName(ctl) = "TextBox" Then
If (Not IsNumeric(ctl.Value) And (ctl.Value <> "")) Then
MsgBox ctl.Name & " - Numbers only"
TextBox1.Value = ""
End If
End If
Next ctl

blackie42
10-22-2007, 11:07 AM
thanks for reply - will give it a go at work tomorrow.

one other thing - I pass the contents of each textbox to the same cell eac time e.g txtbox1 to A1, txtbox2 to A2 -all on same sheet. How can I make sure that the userform (when it doesn't contain any data doesn't overwrite the cell) e.g might want to update the cell thats linked to txtbox5 only but becos theres nothing in any other txboxes it wipes every other cell.

just got e.g. => range("A5") = textbox5.value (for every textbox)

thanks

Bob Phillips
10-22-2007, 11:44 AM
I think you mean



If TextBox5.Value <> "" Then Range("A5").Value = TextBox5.value

blackie42
10-22-2007, 02:04 PM
Yep - now why didn't I think of that?

Learning all the time

thanks again for your help

regards

Jon