PDA

View Full Version : Excel VBA - need help



kbp
07-12-2009, 11:31 AM
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

mejobloggs
07-12-2009, 03:32 PM
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:
http://www.vb-helper.com/howto_test_for_integer.html
http://www.andreavb.com/forum/viewtopic_2684.html
http://www.rolbe.com/2009/03/06/isinteger-vb-function/

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

Tony
07-12-2009, 10:28 PM
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

mejobloggs
08-12-2009, 04:29 PM
Nice and simple though, I like it

Tony
08-12-2009, 04:33 PM
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.

Parry
08-12-2009, 04:46 PM
Hi, looks as though your using a textbox control. You may wish to restrict whats entered by using the KeyPress event ...

http://www.cpearson.com/excel/TextBox.htm

Tony
08-12-2009, 05:18 PM
See, I said there was a more elegant way...