Using the IsNumeric() function - exponent sign

The IsNumeric() function will determine if a string or variant holds a numeric value or not.

It is useful, among other places, to be used in an edit control's (textbox) Validation or Change event.

However, it also recognizes the exponent sign of Scientific format, such as 1E2 (= 100). or 1D2 (double precison) as being numeric. Rightfully so.

But when a textbox should only allow numbers 0 - 9, decimal marks, or plus/minus signs, how do you prevent the user from entering 10D2?

Simply add an exponent of "e0" to the string which is to be validated (1e0 = 1), because you cannot have two exponent signs in a number.

So,

IsNumeric(TheString & "e0")

will validate the string as being numeric or not, as you may have expected it to work.

This is good for the Validate event. You may want to allow an empty string to pass first if an entry is not a must.

Only thing left here is to format the value, if it passes the IsNumeric() test, so a .1 or -.1 for example formats to 0.1 or -0.1:

Format$(TheString)

So, that is good for the validation event. What about the KeyPress or KeyChange events in order to keep the user from entering a non-numeric value in the first place?

Simple solution:
First, the KeyDown/KeyPress is not a good place for this, extra work, because you need to handle the Paste problem two-fold (the windows older Shift-Ins method, and the newer Ctrl-V, and each can only be captured in a different event!).
Use the Change event. It will capture the Changes made directly by the user typing, and by the user pasting.

For the change event, here use a slightly different value to tack on to the testing string:

IsNumeric(TheString & "0e0")

Notice I've added an extra 0.

This, "0e0" evaluates to zero as can be seen by
?CSng("0e0")

This will allow for the user to type +/-/decimal mark or enter nothing as it will evaluate to .0 or -.0 or 0 etc.
Do not do this in the Validate event however - use that which I showed above, otherwise you'll end up with just a + or - or whatever.

What to do if the entered value in the change event is non-numeric?

The fast route is to just use a public variable, or the control's Tag property to store the previous value and swap it out to that. You would need to make sure this gets reset, say when you move to a different record.

Windows has the ability to Undo an edit control's value using the SendMessage API function:

'Declare this in a public module:
Public Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Public Const EM_UNDO = &HC7
Public Const EM_EMPTYUNDOBUFFER = &HCD

Then, in the edit control's change event, if the IsNumeric fails, send:

SendMessage text1.hWnd, EM_UNDO, 0&, 0&

and whether it fails or passes, use:

SendMessage text1.hWnd, EM_EMPTYUNDOBUFFER, 0&, 0&

at the end of the event in order to reset the undo buffer.

If you are not using a control array, and have many edit controls which need numeric validating, then tranfer the code to a public proceedure in a public module, and pass the edit control to it:

Public Function ValCtrlNumeric(myTextBox as VB.TextBox) As Boolean

End Function

Do the validation and UNDO there and set the ValCtrlNumeric function's return value to a fail or succeed True/False.

Now hopefully this will help you in validating numbers in an edit control using the simple, and simple-to-use, IsNumeric() function.

uwp