Forum Home
Press F1
 
Thread ID: 105552 2009-12-06 22:31:00 Excel VBA - need help kbp (7436) Press F1
Post ID Timestamp Content User
836983 2009-12-06 22:31:00 Hi

Can someone help me please.

I have written this code in Excel VBA (as below) and it's working fine except, it also takes "+" and "-" value in the field. But I only want numeric. I understood that IsNumeric was only for numeric but somehow it considers "+" and "-" as numeric. Does anyone know the solution please or could tell me what I am doing wrong ?

Thank you


Private Sub CheckTellerValue()

If IsNumeric(txtTeller) Then

Else
sError = sError & vbNewLine & "Personnel Number must be in numeric format."
txtTeller.SetFocus

End If

End Sub 		kbp (7436)
836984 2009-12-07 02:32:00 I haven't done VBA for a long time, so my info below may be incorrect....

IsNumeric is fairly broad, and allows for decimals, commas, dollar signs and plus/minus

e.g -$3,848.23 would return True

You'd have to write your own function. There are a few ways to do the logic.

Here's a few links I found:
www.vb-helper.com
www.andreavb.com
www.rolbe.com

If you can't get them to work after trying, I'll be happy to help, but if you've done the above by yourself, you should be fine 		mejobloggs (264)
836985 2009-12-07 09:28:00 This is probably not the most elegant, but try something like this:


Sub chartest()

Dim test As String
test = "1234"
Dim numericOK, noSigns As Boolean
If IsNumeric(test) Then
numericOK = True
End If
If InStr(1, test, "+") = 0 And InStr(1, test, "-") = 0 Then
noSigns = True
End If
If numericOK And noSigns Then
Debug.Print "OK"
Else
Debug.Print "not ok"
End If
End Sub

HTH 		Tony (4941)
836986 2009-12-08 03:29:00 Nice and simple though, I like it mejobloggs (264)
836987 2009-12-08 03:33:00 Nice and simple though, I like itOf course I've since realised it doesn't cover things like dollar and cent signs, full stops, commas etc - although it could be easily extended. Tony (4941)
836988 2009-12-08 03:46:00 Hi, looks as though your using a textbox control. You may wish to restrict whats entered by using the KeyPress event ...

www.cpearson.com 		Parry (5696)
836989 2009-12-08 04:18:00 See, I said there was a more elegant way... Tony (4941)
1