Verify Textbox Information is a Number and Not Text

[pallaver]

So I have a UserForm asking for a couple of different values. These
values should all be numbers.

I already added so that the program doesn't continue if the Textbox
value is "" (blank).

How do I make it not continue if the Textbox value is not a number,
i.e. text only, or text and a number?

I've tried the following two ideas:

XVariance = UserForm1.TextBoxXVariance.Text

1. If Val(XVariance)/1 = Val(XVariance) then ...... else exit sub end
if
2. Const TextCharacters = "abcdefghijklmnopqrstuvwxyz" and If
InStr(XVariance, TextCharacters) Then Exit Sub

I just want to make sure the Textbox contains a number using only
0123456789 and a decimal point.

Seems pretty simple and straightforward, but I'm stuck.

Thankx, NP

 

[Rick Rothstein \(MVP - VB\)]

Here are two functions that I have posted in the past for similar
questions..... one is for digits only and the other is for floating point
numbers... just pass your TextBox entries into the function to test if the
are numeric or not...

Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And _
Not Value Like "*[!0-9]*"
End Function

Function IsNumber(ByVal Value As String) As Boolean
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9.]*" And _
Not Value Like "*.*.*" And _
Len(Value) > 0 And Value <> "." And _
Value <> vbNullString
End Function

Here are revisions to the above functions that deal with the local settings
for decimal points (and thousand's separators) that are different than used
in the US (this code works in the US too, of course).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

I'm not as concerned by the rejection of entries that include one or more
thousand's separators, but we can handle this if we don't insist on the
thousand's separator being located in the correct positions (in other words,
we'll allow the user to include them for their own purposes... we'll just
tolerate their presence).

Function IsNumber(ByVal Value As String) As Boolean
Dim DP As String
Dim TS As String
' Get local setting for decimal point
DP = Format$(0, ".")
' Get local setting for thousand's separator
' and eliminate them. Remove the next two lines
' if you don't want your users being able to
' type in the thousands separator at all.
TS = Mid$(Format$(1000, "#,###"), 2, 1)
Value = Replace$(Value, TS, "")
' Leave the next statement out if you don't
' want to provide for plus/minus signs
If Value Like "[+-]*" Then Value = Mid$(Value, 2)
IsNumber = Not Value Like "*[!0-9" & DP & "]*" And _
Not Value Like "*" & DP & "*" & DP & "*" And _
Len(Value) > 0 And Value <> DP And _
Value <> vbNullString
End Function

Rick

 

[pallaver]

Hi Rick!
Just to take a stab here at the below notation:

!0-9 means just 0123456789
*[ (stuff) ]* means anything other than whatever (stuff) is.

Therefore, with the below, if IsDigitsOnly is true then I must have a
non-blank entry of only digits.

I just added a . (decimal point) to make it okay then for something
like 3.5.

Question: Can ! only be used for !0-9 and !A-Z. Could I do, say, !
5-9 or !g-z?

I think I'll add a const "." function to test for each entry only
having one period.


----- RICK WROTE -----
     Function IsDigitsOnly(Value As String) As Boolean
         IsDigitsOnly = Len(Value) > 0 And _
                        Not Value Like "*[!0-9]*"
     End Function

 

[Rick Rothstein \(MVP - VB\)]

The squared brackets represents a single character. The exclamation mark
says to match any character NOT inside the square brackets for that single
character position. Without the exclamation mark, only characters inside the
square brackets will be matched for that single character position. The help
files has pretty detailed coverage of the Like operator... just type Like in
the Immediate window and press F1.

Rick


Hi Rick!
Just to take a stab here at the below notation:

!0-9 means just 0123456789
*[ (stuff) ]* means anything other than whatever (stuff) is.

Therefore, with the below, if IsDigitsOnly is true then I must have a
non-blank entry of only digits.

I just added a . (decimal point) to make it okay then for something
like 3.5.

Question: Can ! only be used for !0-9 and !A-Z. Could I do, say, !
5-9 or !g-z?

I think I'll add a const "." function to test for each entry only
having one period.


----- RICK WROTE -----
Function IsDigitsOnly(Value As String) As Boolean
IsDigitsOnly = Len(Value) > 0 And _
Not Value Like "*[!0-9]*"
End Function

 

[Rick Rothstein \(MVP - VB\)]

I think I'll add a const "." function to test for each entry

only having one period.

I'm not sure what you are saying here. I will say that the Like operator is
like a (very, very) miniature Regular Expression parser and, as such, it can
be tricky to get the exact match patterns that you need. You have to test
any pattern you develop against the extreme possible entries to make sure
you have a bullet-proof system.

Rick

 

[pallaver]

What do the asterix marks mean?
And I wish I could use F1/Help to my advantage, only it's all in
Japanese, thus it would either take me awhile to translate, and even
then, comprehension is not assured, lol.

 

[Rick Rothstein \(MVP - VB\)]

The asterisk is a wildcard standing for 0 or more characters.

Here is a link to the online (English) help files for Like...

http://msdn.microsoft.com/en-us/library/aa242817(VS.60).aspx

Here are some other links you may find useful...

The direct link to the Reference branch (Language Reference is under this)
is...

http://msdn2.microsoft.com/en-us/library/aa338032(vs.60).aspx

The direct link to the Visual Studio 6.0 branch is...

http://msdn2.microsoft.com/en-us/library/ms950408.aspx

If you need help with ActiveX Controls (like MSChart, DateTimePicker,
ListView, etc.), the direct link to using the various ActiveX Controls is
this...

http://msdn.microsoft.com/library/d...vbcon98/html/vbconusingtheactivexcontrols.asp

Rick



What do the asterix marks mean?
And I wish I could use F1/Help to my advantage, only it's all in
Japanese, thus it would either take me awhile to translate, and even
then, comprehension is not assured, lol.