Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
So that you can enter negative numbers???
John Smith wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
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 wrote: So that you can enter negative numbers??? John Smith wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
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 John Smith wrote: 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 wrote: So that you can enter negative numbers??? John Smith wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
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" wrote in message ... 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 wrote: So that you can enter negative numbers??? John Smith wrote: 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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
isnumeric() and hyphen
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. Dave Peterson wrote: 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 John Smith wrote: 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 wrote: So that you can enter negative numbers??? John Smith wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Isnumeric | Excel Programming | |||
vba problem with isnumeric | Excel Programming | |||
opposite of IsNumeric | Excel Discussion (Misc queries) | |||
IsNumeric Test | Excel Programming | |||
Not IsNumeric not working - or is it me? | Excel Programming |