Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
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" <u16627@uwe wrote in message news:85203155d772f@uwe... 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
Thank you Rick.you have given me right solution
Rick Rothstein (MVP - VB) wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
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. Rick Rothstein (MVP - VB) wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
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 "tkraju via OfficeKB.com" <u16627@uwe wrote in message news:8521a7391536d@uwe... 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. Rick Rothstein (MVP - VB) wrote: 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. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
Thank You Rick,
you are right.Your testing logic is really hundred percent right.Thanks You so much.... Rick Rothstein (MVP - VB) wrote: 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] or corrects the entry by putting only numeric values. -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...mming/200806/1 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
code for conditional validation to textboxes
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/ Rick Rothstein (MVP - VB) wrote: 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] or corrects the entry by putting only numeric values. -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Validation subroutine for textboxes | Excel Programming | |||
Show Results in TextBoxes, ONLY if All TextBoxes have Data in them | Excel Programming | |||
format validation in UserForm textboxes | Excel Programming | |||
Addition code for 110 TextBoxes | Excel Programming | |||
Addition code for 110 TextBoxes | Excel Programming |