Using IsNumeric

[ivory_kitten]

I have a text box which uses IsNumeric to validate the text, however if the
user types a number in the box and then wants to change it back to blank,
it's not allowed, the msg box keeps coming up, how do I add to it to stop
that and allow the value to be deleted from the field and left blank.

This is the code

Private Sub RemovalCost2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim strtext As String

strtext = Me.RemovalCost2.Text
If Not IsNumeric(strtext) Then
MsgBox "Please enter a valid number", vbInformation
Cancel = True
Else
Me.RemovalCost2.Text = Format(strtext, "$###,##0.00")
Cancel = False
End If
End Sub


Thanks in advance

 

[Jezebel]

You need a separate check to see if the text box is empty --

If len(strText) > 0 then
If not IsNumeric(strText) then

However:

1) IsNumeric() is a rather dodgy function. Strings like "1,1,1D0" or
"($1,23,,3.4,,,5,,E67$)" return true, which is probably not what you want.

2) Making changes to a control from within one of the control's change
events is a dangerous practice: you run the risk of triggering an endless
loop.

 

[ivory_kitten]

What would you say is a better solution? My numbers need formatting but I
don't want the user to be responsible for doing so, and only numbers can be
entered.

 

[Jezebel]

If you can't rely on the user to enter a legitimate value you're up the
creek without a paddle anyway. Even if what they enter is legitimately
numeric (however defined) it's still open to them to enter the *wrong*
number. Rather than testing the characters, convert the string to a number
and test it against a legitimacy range (eg must be greater than zero and
less than 1 million...)

The only time I've seen this sort of test serving a purpose was an
accounting application where the operators occasionally forgot to press
Enter between successive values, so we sometimes ended up with values in the
billions. (Sadly, our accounts receivable was never so fruitful...)

 

[Kushal]

just add a small line to your code and it will work fine. Make your code as
follows:

Private Sub RemovalCost2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim strtext As String

strtext = Me.RemovalCost2.Text
If Not IsNumeric(strtext) Then
MsgBox "Please enter a valid number", vbInformation
Cancel = True
textbox.value=false

Else
Me.RemovalCost2.Text = Format(strtext, "$###,##0.00")
Cancel = False
textbox.value=false

End If
End Sub