excel vba isnumeric

Public Schedule Face-to-Face & Online Instructor-Led Training - View dates & book

Forum home » Delegate support and help forum » Microsoft Excel VBA Training and help » Excel VBA - IsNumeric

Excel VBA - IsNumeric

resolvedResolved · 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 courses

 

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
What does 'Resolved' mean?

Any suggestions, questions or comments? Please post in the Improve the forum thread.


 

Excel tip:

Trace Dependents / Precedents without the blue arrows

Rather 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.

View all Excel hints and tips


Server loaded in 0.1 secs.