code for conditional validation to textboxes

[tkraju via OfficeKB.com]

How to codify the following logic:
A textbox in a userform can be left blank without entering any
data/value.But if entry is needed only numeric values(numbers with or without

decimals) be entered.
Msg. be fired the moment it finds a text character.Example:Textbox Entry='R'
or
'123h'.cursor should not move from the textbox until
correction/rectification done by user.
While rectification if user deletes whole entry and leave the textbox blank,
or
corrects the entry by putting only numeric values.

 

[Rick Rothstein \(MVP - VB\)]

Add this function to your UserForm's code window (it will return True if the
String value passed into it is an integer or properly formed floating point
value)...

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

and then use this code for the TextBox's Exit procedure..

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Len(.Text) > 0 And Not IsNumber(.Text) Then
MsgBox "Invalid entry! Please try again."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Of course, change the example TextBox1 name I used for the TextBox to the
actual name of your TextBox.

Rick

 

[tkraju via OfficeKB.com]

Thank you Rick.you have given me right solution

Add this function to your UserForm's code window (it will return True if the
String value passed into it is an integer or properly formed floating point
value)...

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

and then use this code for the TextBox's Exit procedure..

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Len(.Text) > 0 And Not IsNumber(.Text) Then
MsgBox "Invalid entry! Please try again."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Of course, change the example TextBox1 name I used for the TextBox to the
actual name of your TextBox.

Rick

How to codify the following logic:
A textbox in a userform can be left blank without entering any

[quoted text clipped - 11 lines]

or
corrects the entry by putting only numeric values.

 

[tkraju via OfficeKB.com]

Mr.Rick,
I have a some modification of my question,can you modify the code a bit more
robust.
How to codify the following logic:
I have 2 text boxes viz.TextBox1 and Textbox2 in a userform.TextBox1 has a
archival value from a worksheet source.
Condition1:The Textbox2 can be left blank without entering any
data/value.But if entry is needed only numeric values(numbers with or without

decimals) be entered, and the TextBox2 value should be equal to TextBox1.
cursor should not move from the textbox until
correction/rectification done by user.
While rectification if user deletes whole entry and leave the textbox blank,
or
corrects the entry by putting exact values as in TextBox1.

Add this function to your UserForm's code window (it will return True if the
String value passed into it is an integer or properly formed floating point
value)...

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

and then use this code for the TextBox's Exit procedure..

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Len(.Text) > 0 And Not IsNumber(.Text) Then
MsgBox "Invalid entry! Please try again."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Of course, change the example TextBox1 name I used for the TextBox to the
actual name of your TextBox.

Rick

How to codify the following logic:
A textbox in a userform can be left blank without entering any

[quoted text clipped - 11 lines]

or
corrects the entry by putting only numeric values.

 

[Rick Rothstein \(MVP - VB\)]

I am probably missing something in your question, but why can't you simply
test if the Text values of the two TextBoxes are equal or not (doesn't
matter if the entry is numeric or not... if there's something in TextBox1,
then it has to equal TextBox2... period)...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox2
If Len(.Text) > 0 And .Text <> TextBox1.Text Then
MsgBox "Invalid entry! Entry must match archival value."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Rick

Mr.Rick,
I have a some modification of my question,can you modify the code a bit
more
robust.
How to codify the following logic:
I have 2 text boxes viz.TextBox1 and Textbox2 in a userform.TextBox1 has a
archival value from a worksheet source.
Condition1:The Textbox2 can be left blank without entering any
data/value.But if entry is needed only numeric values(numbers with or
without

decimals) be entered, and the TextBox2 value should be equal to TextBox1.
cursor should not move from the textbox until
correction/rectification done by user.
While rectification if user deletes whole entry and leave the textbox
blank,
or
corrects the entry by putting exact values as in TextBox1.

Add this function to your UserForm's code window (it will return True if
the
String value passed into it is an integer or properly formed floating
point
value)...

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

and then use this code for the TextBox's Exit procedure..

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox1
If Len(.Text) > 0 And Not IsNumber(.Text) Then
MsgBox "Invalid entry! Please try again."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Of course, change the example TextBox1 name I used for the TextBox to the
actual name of your TextBox.

Rick

How to codify the following logic:
A textbox in a userform can be left blank without entering any

[quoted text clipped - 11 lines]

or
corrects the entry by putting only numeric values.

 

[tkraju via OfficeKB.com]

Thank You Rick,
you are right.Your testing logic is really hundred percent right.Thanks You
so much....

I am probably missing something in your question, but why can't you simply
test if the Text values of the two TextBoxes are equal or not (doesn't
matter if the entry is numeric or not... if there's something in TextBox1,
then it has to equal TextBox2... period)...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox2
If Len(.Text) > 0 And .Text <> TextBox1.Text Then
MsgBox "Invalid entry! Entry must match archival value."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Rick

Mr.Rick,
I have a some modification of my question,can you modify the code a bit

[quoted text clipped - 54 lines]

 

[tkraju via OfficeKB.com]

Mr.Rick,
My 3rd question with same conditions(If Textbox2 value should be greater than
TextBox10)
Can I modify the code line to test the both test boxes,for " if textbox2
value should be greater than
textbox1, with all the above conditions remains same.
If Len(.Text)>0 And .value > TextBox1.value Then
will this modification works or not/

I am probably missing something in your question, but why can't you simply
test if the Text values of the two TextBoxes are equal or not (doesn't
matter if the entry is numeric or not... if there's something in TextBox1,
then it has to equal TextBox2... period)...

Private Sub TextBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
With TextBox2
If Len(.Text) > 0 And .Text <> TextBox1.Text Then
MsgBox "Invalid entry! Entry must match archival value."
Cancel = True
.SelStart = 0
.SelLength = Len(.Text)
End If
End With
End Sub

Rick

Mr.Rick,
I have a some modification of my question,can you modify the code a bit

[quoted text clipped - 54 lines]