check that textbox entry is numeric

[Katherine]

My userform is coming along great guns, but theres an extra little touch i need that i can't figure out how to do.

I've got a textbox for the user to enter the price of a property, but this entry must be numbers only - no text characters and no punctuation/symbols. I was thinking of popping up a messagebox if the user's entry is invalid, which is no problem in itself, but i can't work out how to actually check whether what they've entered is numeric or not.

I've only got as far as:

private sub txtPrice_Exit(ByVal Cancel As MSForms ReturnBoolean)

before i've come unstuck. Can anyone dig me out of this hole? Cheers!

 

[Helmut Weber]

Hi Katherine,
really only 0 - 9?
What about 5.5?
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Katherine]

The value is the price of a house, so 0 isn't acceptable and neither are decimal points or commas as a thousands seperator. Values are going to be things like 125000 (if it a property for sale) or 800 (if its a property for rent)

 

[Helmut Weber]

Hi Katherine,
striving for a bulletproof solution,
have a look at this:
Public Function IsFigure(f As String) As Boolean
IsFigure = False
If f = "0" Then ' can't be converted
IsFigure = True
Exit Function
End If
On Error GoTo ende
If CInt(f) Then IsFigure = True
ende:
End Function
---
Private Sub TextBox1_Change()
Dim s As String
s = TextBox1.Text
If s = "" Then GoTo ende
If Left(s, 1) = "0" Then
s = Right(s, Len(s) - 1)
GoTo ende
End If
If IsFigure(Right(s, 1)) = False Then
s = Left(s, Len(s) - 1)
End If
ende:
TextBox1.Text = s
End Sub
---
Plus in case that someone tries to fill the textbox by copy and paste:
Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim l As Long
Dim s As String
s = TextBox1.Text
For l = 1 To Len(s)
If Not IsFigure(Mid(s, l, 1)) Then
TextBox1.Text = ""
MsgBox "Error"
Exit Sub
End If
Next
End Sub

 

[Helmut Weber]

Hi Christian,
not bulletproof! ;-)
Seems so simple, but isn't.
MsgBox IsNumeric(0.5) ' true
Exclude fractions.
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Jonathan West]

My userform is coming along great guns, but theres an extra little touch i

need that i can't figure out how to do.

I've got a textbox for the user to enter the price of a property, but this

entry must be numbers only - no text characters and no punctuation/symbols.
I was thinking of popping up a messagebox if the user's entry is invalid,
which is no problem in itself, but i can't work out how to actually check
whether what they've entered is numeric or not.

I've only got as far as:

private sub txtPrice_Exit(ByVal Cancel As MSForms ReturnBoolean)

before i've come unstuck. Can anyone dig me out of this hole? Cheers!

Try this

Private sub txtPrice_Exit(ByVal Cancel As MSForms ReturnBoolean)

Dim x as Long
On Error Goto Invalid
x = CLng(txtPrice.Text)
Exit Sub

Invalid:
MsgBox "Not a valid number"
Cancel = True
Exit Sub

This code relies on throwing an error if the text in the textbox doesn't
convert to a valid number

 

[Helmut Weber]

Hi Christian,
and besides that I get "true" for
MsgBox IsNumeric(",5")
Not the intention of the localized programmer.


Gruss
Helmut Weber
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Jonathan West]

i
need that i can't figure out how to do. this
entry must be numbers only - no text characters and no punctuation/symbols.
I was thinking of popping up a messagebox if the user's entry is invalid,
which is no problem in itself, but i can't work out how to actually check
whether what they've entered is numeric or not.

Try this

Private sub txtPrice_Exit(ByVal Cancel As MSForms ReturnBoolean)

Dim x as Long
On Error Goto Invalid
x = CLng(txtPrice.Text)
Exit Sub

Invalid:
MsgBox "Not a valid number"
Cancel = True
Exit Sub

That last line should be "End Sub"

 

[Helmut Weber]

Hi everybody,
great fun with this!
A seemably simple thing,
that is getting more and more complicated
CLng("01000,.5") would then result to 1000,
CLng(",5") would result to 0 "zero"
CLng("0,1") would result to 0 "zero"
CLng("1,000") would result to 1
CLng("1.000") would result to 1000,
here with my german localized version.
Is that what is desired?
Not in the intention of the inventor, I'd say.
Feel free for new unexpected variations.
;-)
Greetings from Bavaria, Germany
Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

 

[Katherine]

Fantastic, thanks Jonathan - thats exactly what i needed.

At this rate I'm going to have to put your name to this project - you've written more of it than i have! ;-)

 

[Chad Knudson]

If you want to try to throw out fractional entries altogether, you can parse
the number and then re-generate the string from the parsed value to see if
you get the same results. I end up having quite elaborate
parsing/validation routines due to the requirements of the projects I work
on.

My trouble usually is allowing the user to enter things the way that they
want to, and massaging that information into a standard format that I need
to use in the documents.

Fantastic, thanks Jonathan - thats exactly what i needed.

At this rate I'm going to have to put your name to this project - you've

written more of it than i have! ;-)

 

[JB]

Hi Christian,
and besides that I get "true" for
MsgBox IsNumeric(",5")
Not the intention of the localized programmer.


Gruss
Helmut Weber
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98

Thats you told Christain