98.7% Of all customers recommend us, we're so confident about our results we publish all reviews and stats
View Live Stats View ReviewsForum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - IsNumeric
Excel VBA - IsNumeric
Resolved · Low Priority · Version 2007
Richard has attended:
Excel Advanced course
Excel VBA Intro Intermediate course
Excel VBA Advanced course
Excel VBA - IsNumeric
Hi
I am creating a Userform which I need validations on the cells. A number of which are highlighting the cell if a non-numeric character is entered. Having searched the net I have been using this code:
Private Sub EmployeeNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If Not IsNumeric(EmployeeNumber.Text) Then
EmployeeNumber.BackColor = &HFF&
Cancel = True
Else
EmployeeNumber.BackColor = &H80000005
Call EnableApply
End If
End Sub
This works pretty well, however if only a single numeric character is entered, it must take as non-numeric and highlights red.
I would ideally like to have a message box appear as soon as a non-numeric character is entered but due to this problem, it would be very frustrating to the user as the message box would appear as soon as they type in to the cell even if it is numeric.
Could you please explain why it see's a single number as non-numeric and if there is anyway I can change it?
Thank you
Richard
RE: Excel VBA - IsNumeric
Hi Richard
Thanks for getting in touch. I looked up your specific issue but couldn't find any related solutions.
I tried approaching it in a different way instead. Instead, detect the KeyAscii value and format whether it's a number or not.
Private Sub EmployeeNumber_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
EmployeeNumber.BackColor = &H80000005
Select Case KeyAscii
Case 45, 48 To 57
EmployeeNumber.BackColor = &H80000005
Case Else
EmployeeNumber.BackColor = &HFF&
End Select
End Sub
For your reference here is a list of the ASCII values:
http://www.bettersolutions.com/vba/VIX243/VD622016333.htm
Kind regards
Gary Fenn
Microsoft Office Specialist Trainer
Tel: 0207 987 3777
Best STL - https://www.stl-training.co.uk
98%+ recommend us
London's leader with UK wide delivery in Microsoft Office training and management training to global brands, FTSE 100, SME's and the public sector
Training information:
Welcome. Please choose your application (eg. Excel) and then post your question. Our Microsoft Qualified trainers will then respond within 24 hours (working days). Frequently Asked Questions
Any suggestions, questions or comments? Please post in the Improve the forum thread. |
Excel tip:Trace Dependents / Precedents without the blue arrowsRather than using the toolbar you can press CTRL+] which is the equivelent of trace dependants and CTRL+[ for precendants. Both of these ways though will not show the blue arrows but jump to the cell containing the formula. |