isnumeric() and hyphen

[John Smith]

Why does isnumeric() treat hyphen differently than it does other
letters? In the following code, the first hyphen does not trigger
the msgbox, though alphabets do. So is the second hyphen.

sub textbox1_change()

if NOT isnumeric(textbox1.value) then
Msgbox ("You must enter a number.")
end if
end sub

 

[Dave Peterson]

So that you can enter negative numbers???

Why does isnumeric() treat hyphen differently than it does other
letters? In the following code, the first hyphen does not trigger
the msgbox, though alphabets do. So is the second hyphen.

sub textbox1_change()

if NOT isnumeric(textbox1.value) then
Msgbox ("You must enter a number.")
end if
end sub

 

[John Smith]

But if I mistakenly type "100-", isnumeric() doesn't sense the
error. It only senses the error after I punch another key,
alphabets or numbers. Don't know what isnumeric() was waiting for.

 

[Dave Peterson]

Ah. I was confused by the "first hyphen" reference. I thought you meant the
first character in the textbox.

My guess (and it's only my guess) is that isnumeric() is very forgiving. It'll
do it's best to convert anything that looks like it can be a number into a
number.

And 100- is close enough to be -100 that isnumeric() forgives you.

I think that lots of people (accountants???) use this kind of formatting and may
even like it.

If you want to ignore those entries, maybe you could check the position of the
hyphen--or even use val().

Option Explicit
Private Sub TextBox1_Change()
With Me.TextBox1
MsgBox IsNumeric(.Value) & vbLf _
& Val(.Value) & vbLf _
& Format(.Value, "0")
End With
End Sub

 

[NickHK]

John,
IsNumeric will anything that looks like a number and is somewhat easy to
fool.
Depending on what you want to allow/deny, maybe Val, or CDbl and trap the
error will work.
Or monitor the key strokes with the KeyDown/keyPress events.

Alternatively, give the user an input box restricted to numbers:
Const NumbersOnly As Long = 1
Application.InputBox(,,,,,,,NumbersOnly )

NickHK

 

[John Smith]

Thanks for the reply. I decided to use keypress event to check
every key that is punched and make sure it is between 0 and 9.