How2 accept only numbers in userform textbox
 

Hello:

I have a useform which requires a user to input data into some textboxes.
The user then clicks a button and some math is done on the data input by the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of the
textboxes.

Help would be appreciated.

Thanks,
MARTY

How2 accept only numbers in userform textbox
 

On Jan 4, 8:08*pm, MARTY wrote:
Hello:

I have a useform which requires a user to input data into some textboxes. *
The user then clicks a button and some math is done on the data input by the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of the
textboxes.

Help would be appreciated.

Thanks,
MARTY

Dim Num As Variant
Num = InputBox1.Value
If Not IsNumeric(Num) Then
Msgbox "You must enter a number"
Exit Sub
End If

How2 accept only numbers in userform textbox
 

Thank you very much.

"GTVT06" wrote:

On Jan 4, 8:08 pm, MARTY wrote:
Hello:

I have a useform which requires a user to input data into some textboxes.
The user then clicks a button and some math is done on the data input by the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of the
textboxes.

Help would be appreciated.

Thanks,
MARTY

Dim Num As Variant
Num = InputBox1.Value
If Not IsNumeric(Num) Then
Msgbox "You must enter a number"
Exit Sub
End If

How2 accept only numbers in userform textbox
 

Marty,
Write code to Validate the user input BEFORE you perform you math functions.
If the data validates, then do the math. If not, you can use the msgbox
function to prompt the user that some or all of the data is invalid.

You can validate each text box, one at a time, using the 'TextBox1_Exit'
procedure or validate them all at the same time. I prefer to validate them
all at the same time and then tell the user where the mistakes are. The
following code is an example tht you can build from.

Private Sub CommandButton1_Click()
If mAcceptChar(TextBox1.Text, "0123456789") = False Then
MsgBox "Booboo", vbOKOnly
End If
End Sub
Private Function mAcceptChar(sText As String, sCharSet As String) As Boolean
Dim sChar As String
Dim X As Integer

For X = 1 To Len(sText)
sChar = Mid$(sText, X, 1)
If InStr(1, sCharSet, sChar) 0 Then
mAcceptChar = True
Else
mAcceptChar = False
Exit For
End If
Next X
End Function

The easiest way is the simply determine if the text is numeric as in

Private Sub CommandButton1_Click()
If Not IsNumeric(TextBox1.Text) Then
MsgBox "Booboo", vbOKOnly
End If
End Sub

"MARTY" wrote:

Hello:

I have a useform which requires a user to input data into some textboxes.
The user then clicks a button and some math is done on the data input by the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of the
textboxes.

Help would be appreciated.

Thanks,
MARTY

How2 accept only numbers in userform textbox
 

On Jan 4, 9:40*pm, MARTY wrote:
Thank you very much.



"GTVT06" wrote:
On Jan 4, 8:08 pm, MARTY wrote:
Hello:

I have a useform which requires a user to input data into some textboxes. *
The user then clicks a button and some math is done on the data input by the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of the
textboxes.

Help would be appreciated.

Thanks,
MARTY

Dim Num As Variant
Num = InputBox1.Value
If Not IsNumeric(Num) Then
Msgbox "You must enter a number"
Exit Sub
End If- Hide quoted text -

- Show quoted text -

Your Welcome

How2 accept only numbers in userform textbox
 

I have a useform which requires a user to input data into some textboxes.
The user then clicks a button and some math is done on the data input by
the
user.

The problem is that right now there is nothing to prevent the user from
putting anything (text, symbols or whatever) into the boxes, and this
causes
a runtime error when the button is clicked.

I'm looking for some code to put into my userform which will generate an
error message if the user inputs anything other than a number into one of
the
textboxes.

Instead of looking for an incorrect entry and popping up a MessageBox, how
about stopping invalid key presses from ever being entered? Assuming one of
your TextBox'es is named TextBox1 (change the 3 With statements to your
TextBox's actual Name if it is different), comment out any Change, KeyPress
or MouseDown event code you presently have and copy/paste the code after my
signature into UserForm's code window. Run your UserForm's code and try
typing into the TextBox... you will only be able to type in digits, a single
decimal point and a single leading plus or minus sign. In addition, you can
control the maximum number of digits on either side of the decimal point via
the MaxDecimal and MaxWhole constants in the Change event (set these to
large numbers if you don't need to restrict their size). If you like this
solution, you will have to set up these 3 event procedures for each TextBox
you want to control (remember to change the Name of the TextBox in the 3
With statements).

Note that you will have check for the Text property to see if it contains a
single character consisting of a plus, minus or decimal point and take some
action if that is the case since each of these characters must be allowed as
a starting character. I'm thinking this shouldn't happen too often, if at
all, so a MessageBox for this condition would be tolerable.

I guess I should mention that I'm in the US where the decimal point is a
"dot". If your decimal point is some other characters, then make the obvious
substitutions in the If-Then tests above; or you could query the system for
the decimal point character, store it in a variable and concatenate that
into the string values above in place of the decimal point ("dot") that I
show above. Here is what I use to get the system's decimal point.

DecimalPointSymbol = Format$(0, ".")

Rick

'For typing floating point numbers in the TextBox
'=================================================
' Set the maximum number of digits before the
' decimal point in the MaxWhole constant. Set
' the maximum number of digits after the decimal
' point in the MaxDecimal constant.
Dim LastPosition As Long

Private Sub TextBox1_Change()
Static LastText As String
Static SecondTime As Boolean
Const MaxDecimal As Integer = 2
Const MaxWhole As Integer = 6
With TextBox1
If Not SecondTime Then
If .Text Like "*[!0-9.+-]*" Or _
.Text Like "*.*.*" Or _
.Text Like "*." & String$(1 + MaxDecimal, "#") Or _
.Text Like "*" & String$(MaxWhole, "#") & "[!.]*" Or _
.Text Like "?*[+-]*" Then
Beep
SecondTime = True
.Text = LastText
.SelStart = LastPosition
Else
LastText = .Text
End If
End If
End With
SecondTime = False
End Sub

Private Sub TextBox1_MouseDown(ByVal Button As Integer, _
ByVal Shift As Integer, _
ByVal X As Single, _
ByVal Y As Single)
With TextBox1
LastPosition = .SelStart
'Place any other MouseDown event code here
End With
End Sub

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
With TextBox1
LastPosition = .SelStart
'Place any other KeyPress checking code here
End With
End Sub

How2 accept only numbers in userform textbox
 

Dim Num As Variant
Num = InputBox1.Value
If Not IsNumeric(Num) Then
Msgbox "You must enter a number"
Exit Sub
End If

The above test is not as fool-proof as I think you think it is. Here is the
response I post over in the compiled VB newsgroups whenever some uses
IsNumeric to test more than a single character entry for its being a
number...

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

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

How2 accept only numbers in userform textbox
 

The easiest way is the simply determine if the text is numeric as in

Private Sub CommandButton1_Click()
If Not IsNumeric(TextBox1.Text) Then
MsgBox "Booboo", vbOKOnly
End If
End Sub

The above test is not as fool-proof as I think you think it is. Here is the
response I post over in the compiled VB newsgroups whenever some uses
IsNumeric to test more than a single character entry for its being a
number...

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

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

How2 accept only numbers in userform textbox
 

Thanks for the input Rick. I almost never use the IsNumeric function as I
usually use the mAcceptChars one I also listed. I do like yours better
though!

"Rick Rothstein (MVP - VB)" wrote:

The easiest way is the simply determine if the text is numeric as in

Private Sub CommandButton1_Click()
If Not IsNumeric(TextBox1.Text) Then
MsgBox "Booboo", vbOKOnly
End If
End Sub

The above test is not as fool-proof as I think you think it is. Here is the
response I post over in the compiled VB newsgroups whenever some uses
IsNumeric to test more than a single character entry for its being a
number...

I usually try and steer people away from using IsNumeric to "proof"
supposedly numeric text. Consider this (also see note below):

ReturnValue = IsNumeric("($1,23,,3.4,,,5,,E67$)")

Most people would not expect THAT to return True. IsNumeric has some "flaws"
in what it considers a proper number and what most programmers are looking
for.

I had a short tip published by Pinnacle Publishing in their Visual Basic
Developer magazine that covered some of these flaws. Originally, the tip was
free to view but is now viewable only by subscribers.. Basically, it said
that IsNumeric returned True for things like -- currency symbols being
located in front or in back of the number as shown in my example (also
applies to plus, minus and blanks too); numbers surrounded by parentheses as
shown in my example (some people use these to mark negative numbers);
numbers containing any number of commas before a decimal point as shown in
my example; numbers in scientific notation (a number followed by an upper or
lower case "D" or "E", followed by a number equal to or less than 305 -- the
maximum power of 10 in VB); and Octal/Hexadecimal numbers (&H for
Hexadecimal, &O or just & in front of the number for Octal).

NOTE:
======
In the above example and in the referenced tip, I refer to $ signs and
commas and dots -- these were meant to refer to your currency, thousands
separator and decimal point symbols as defined in your local settings --
substitute your local regional symbols for these if appropriate.

As for your question about checking numbers, here are two functions that I
have posted in the past for similar questions..... one is for digits only
and the other is for "regular" numbers:

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

How2 accept only numbers in userform textbox
 

Thanks for the input Rick. I almost never use the IsNumeric function

And now you know why. <g

as I usually use the mAcceptChars one I also listed. I do like yours
better though!

Feel free to make use of it as you wish in the future.

Rick