Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation for TextBoxes
Im trying to figure out a way to add Data Validation for TextBoxes. Ive
used this technique befo If TextBox1 = "" Then MsgBox "Please enter a value for 'Name'!!" Exit Sub End If However, Im not sure how to limit a TextBox to a range of numbers, such as 1-6. How can this be done? Also, how can I do it for many TextBoxes, without specifically adding code for each? A for Each€¦Next should do it, but I may need to add Data Validation for several TextBoxes, such as 1-20, and then apply a slightly different set of Data Validation rules for other TextBoxes, such as 1-4 for TextBoxes 21-25. How would I go about doing that? Thanks!! Ryan--- -- RyGuy |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation for TextBoxes
You can use the code below to restrict text entry in TextBox1 to the
characters "0" to "6". Any other character is rejected and won't be placed in the text box. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("0") To Asc("6") ' OK Case Else KeyAscii = 0 Me.Label1.Caption = "Illegal character." End Select End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 3 Jan 2009 13:58:00 -0800, ryguy7272 wrote: I’m trying to figure out a way to add Data Validation for TextBoxes. I’ve used this technique befo If TextBox1 = "" Then MsgBox "Please enter a value for 'Name'!!" Exit Sub End If However, I’m not sure how to limit a TextBox to a range of numbers, such as 1-6. How can this be done? Also, how can I do it for many TextBoxes, without specifically adding code for each? A for Each…Next should do it, but I may need to add Data Validation for several TextBoxes, such as 1-20, and then apply a slightly different set of Data Validation rules for other TextBoxes, such as 1-4 for TextBoxes 21-25. How would I go about doing that? Thanks!! Ryan--- |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation for TextBoxes
Unfortunately, that method will not prevent users from pasting in "invalid"
characters. Here is some code that I first posted to the compiled VB newsgroups, but have modified for Excel's VBA world... it will restrict the characters allowed in the TextBox (whether typed or pasted) to only the digits 0 thru 6 (the allowable characters are controlled by the list in the 2nd If..Then statement); also note that I have the routine Beep for invalid characters, although the display of a text message is doable if desired... '************* START OF CODE ************* Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-6]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If 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 '************* END OF CODE ************* -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... You can use the code below to restrict text entry in TextBox1 to the characters "0" to "6". Any other character is rejected and won't be placed in the text box. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("0") To Asc("6") ' OK Case Else KeyAscii = 0 Me.Label1.Caption = "Illegal character." End Select End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 3 Jan 2009 13:58:00 -0800, ryguy7272 wrote: I'm trying to figure out a way to add Data Validation for TextBoxes. I've used this technique befo If TextBox1 = "" Then MsgBox "Please enter a value for 'Name'!!" Exit Sub End If However, I'm not sure how to limit a TextBox to a range of numbers, such as 1-6. How can this be done? Also, how can I do it for many TextBoxes, without specifically adding code for each? A for Each.Next should do it, but I may need to add Data Validation for several TextBoxes, such as 1-20, and then apply a slightly different set of Data Validation rules for other TextBoxes, such as 1-4 for TextBoxes 21-25. How would I go about doing that? Thanks!! Ryan--- |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation for TextBoxes
Sorry for the delayed response here; I was battling a virus the past couple
days. For a while it prevented me from getting online, but finally I expunged it, and ultimately prevailed! Im getting a message that reads €˜Subscript out of range and this line is yellow: With Worksheets("Import") Below is my code: Private Sub cmdEnter2_Click() Dim LastPosition As Long Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-6]*" Then Beep SecondTime = True ..Text = LastText ..SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False Dim i As Integer With Worksheets("Import") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(lastrow, 1).Value = txtLastName.Value .Cells(lastrow, 2).Value = txtFirstName.Value .Cells(lastrow, 3).Value = txtMR.Value .Cells(lastrow, 4).Value = txtDate.Value For i = 6 To 60 .Cells(lastrow, i).Value = Me.Controls("TextBox" & i - 6).Text Next i End With 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 '************* END OF CODE ************* I have 69 TextBoxes; four are named and for FName, LName, IDNumber, and Date. The rest, TextBox0 through TextBox64 are set up to receive numbers from users. TextBox0 through TextBox29 should be data-validated for numbers 1-6 and TextBox30 through TextBox64 should be data-validated 1-8. Is this possible with my current loop, or do I need to do away with the loop and code each TextBox separately? Thanks for the help! Ryan--- -- RyGuy "Rick Rothstein" wrote: Unfortunately, that method will not prevent users from pasting in "invalid" characters. Here is some code that I first posted to the compiled VB newsgroups, but have modified for Excel's VBA world... it will restrict the characters allowed in the TextBox (whether typed or pasted) to only the digits 0 thru 6 (the allowable characters are controlled by the list in the 2nd If..Then statement); also note that I have the routine Beep for invalid characters, although the display of a text message is doable if desired... '************* START OF CODE ************* Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-6]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If 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 '************* END OF CODE ************* -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... You can use the code below to restrict text entry in TextBox1 to the characters "0" to "6". Any other character is rejected and won't be placed in the text box. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("0") To Asc("6") ' OK Case Else KeyAscii = 0 Me.Label1.Caption = "Illegal character." End Select End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 3 Jan 2009 13:58:00 -0800, ryguy7272 wrote: I'm trying to figure out a way to add Data Validation for TextBoxes. I've used this technique befo If TextBox1 = "" Then MsgBox "Please enter a value for 'Name'!!" Exit Sub End If However, I'm not sure how to limit a TextBox to a range of numbers, such as 1-6. How can this be done? Also, how can I do it for many TextBoxes, without specifically adding code for each? A for Each.Next should do it, but I may need to add Data Validation for several TextBoxes, such as 1-20, and then apply a slightly different set of Data Validation rules for other TextBoxes, such as 1-4 for TextBoxes 21-25. How would I go about doing that? Thanks!! Ryan--- |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Data Validation for TextBoxes
You can't do what you are attempting to do. Normally, you would have to set
up the three event procedures for every TextBox you wanted to validate, but that would be somewhat painful to do. So, I have a kludge solution for you to try. Basically, the way it works is you add an extra TextBox to the UserForm (you are using a UserForm, right?) and move it on top of the TextBox that the user selects... that way only one triplet of event procedures is required to handle the validation. You will still need an Enter event procedure for each TextBox you want to have validation code, but each of these event procedures only needs one line of code in them. And you will also need to use an "OK" or "Done" type CommandButton to perform your final actions (you need this in order to get the text into the last TextBox the user visited (you need something that will run the code I have in the CommandButton's Click event which transfers the text from the MasterTBox to the last TextBox the user visited). Okay, first thing you have to do is add another TextBox to your UserForm and name it MasterTBox and set its Visible property to False. The following code is already set up to handle all of your 64 TextBoxes. Just Copy/Paste the following code into the UserForm's code window. If you have existing event procedures other than what I gave you (and how you attempted to modify them), you will have to preserve them when you add the code below to the UserForm. If any of your existing event procedures are the same as the event procedures below, you will have to integrate their code into my code (you can't have multiple procedures with exactly the same name). '*************** START OF CODE *************** Dim LastPosition As Long Dim ValidationString As String Dim LastTBoxWithFocus As String Sub GetMasterTBox() If Len(LastTBoxWithFocus) 0 Then Me.Controls(LastTBoxWithFocus).Text = MasterTBox.Text End If With ActiveControl If Right(.Name, 2) 29 And _ Right(.Name, 2) < 65 Then ValidationString = "*[!0-8]*" Else ValidationString = "*[!0-6]*" End If MasterTBox.Text = .Text MasterTBox.Move .Left, .Top, .Width, .Height LastTBoxWithFocus = .Name End With With MasterTBox .Visible = True .ZOrder .SetFocus End With End Sub Private Sub CommandButton1_Click() With MasterTBox Me.Controls(LastTBoxWithFocus).Text = .Text .Visible = False End With ' ' Your CommandButton code goes here ' End Sub Private Sub MasterTBox_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With MasterTBox If .Text Like ValidationString Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False End Sub Private Sub MasterTBox_MouseDown(ByVal Button As Integer, _ ByVal Shift As Integer, _ ByVal X As Single, ByVal Y As Single) With MasterTBox LastPosition = .SelStart 'Place any other MouseDown event code here End With End Sub Private Sub MasterTBox_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) With MasterTBox LastPosition = .SelStart 'Place any other KeyPress checking code here End With End Sub Private Sub TextBox1_Enter() GetMasterTBox End Sub Private Sub TextBox2_Enter() GetMasterTBox End Sub Private Sub TextBox3_Enter() GetMasterTBox End Sub Private Sub TextBox4_Enter() GetMasterTBox End Sub Private Sub TextBox5_Enter() GetMasterTBox End Sub Private Sub TextBox6_Enter() GetMasterTBox End Sub Private Sub TextBox7_Enter() GetMasterTBox End Sub Private Sub TextBox8_Enter() GetMasterTBox End Sub Private Sub TextBox9_Enter() GetMasterTBox End Sub Private Sub TextBox10_Enter() GetMasterTBox End Sub Private Sub TextBox11_Enter() GetMasterTBox End Sub Private Sub TextBox12_Enter() GetMasterTBox End Sub Private Sub TextBox13_Enter() GetMasterTBox End Sub Private Sub TextBox14_Enter() GetMasterTBox End Sub Private Sub TextBox15_Enter() GetMasterTBox End Sub Private Sub TextBox16_Enter() GetMasterTBox End Sub Private Sub TextBox17_Enter() GetMasterTBox End Sub Private Sub TextBox18_Enter() GetMasterTBox End Sub Private Sub TextBox19_Enter() GetMasterTBox End Sub Private Sub TextBox20_Enter() GetMasterTBox End Sub Private Sub TextBox21_Enter() GetMasterTBox End Sub Private Sub TextBox22_Enter() GetMasterTBox End Sub Private Sub TextBox23_Enter() GetMasterTBox End Sub Private Sub TextBox24_Enter() GetMasterTBox End Sub Private Sub TextBox25_Enter() GetMasterTBox End Sub Private Sub TextBox26_Enter() GetMasterTBox End Sub Private Sub TextBox27_Enter() GetMasterTBox End Sub Private Sub TextBox28_Enter() GetMasterTBox End Sub Private Sub TextBox29_Enter() GetMasterTBox End Sub Private Sub TextBox30_Enter() GetMasterTBox End Sub Private Sub TextBox31_Enter() GetMasterTBox End Sub Private Sub TextBox32_Enter() GetMasterTBox End Sub Private Sub TextBox33_Enter() GetMasterTBox End Sub Private Sub TextBox34_Enter() GetMasterTBox End Sub Private Sub TextBox35_Enter() GetMasterTBox End Sub Private Sub TextBox36_Enter() GetMasterTBox End Sub Private Sub TextBox37_Enter() GetMasterTBox End Sub Private Sub TextBox38_Enter() GetMasterTBox End Sub Private Sub TextBox39_Enter() GetMasterTBox End Sub Private Sub TextBox40_Enter() GetMasterTBox End Sub Private Sub TextBox41_Enter() GetMasterTBox End Sub Private Sub TextBox42_Enter() GetMasterTBox End Sub Private Sub TextBox43_Enter() GetMasterTBox End Sub Private Sub TextBox44_Enter() GetMasterTBox End Sub Private Sub TextBox45_Enter() GetMasterTBox End Sub Private Sub TextBox46_Enter() GetMasterTBox End Sub Private Sub TextBox47_Enter() GetMasterTBox End Sub Private Sub TextBox48_Enter() GetMasterTBox End Sub Private Sub TextBox49_Enter() GetMasterTBox End Sub Private Sub TextBox50_Enter() GetMasterTBox End Sub Private Sub TextBox51_Enter() GetMasterTBox End Sub Private Sub TextBox52_Enter() GetMasterTBox End Sub Private Sub TextBox53_Enter() GetMasterTBox End Sub Private Sub TextBox54_Enter() GetMasterTBox End Sub Private Sub TextBox55_Enter() GetMasterTBox End Sub Private Sub TextBox56_Enter() GetMasterTBox End Sub Private Sub TextBox57_Enter() GetMasterTBox End Sub Private Sub TextBox58_Enter() GetMasterTBox End Sub Private Sub TextBox59_Enter() GetMasterTBox End Sub Private Sub TextBox60_Enter() GetMasterTBox End Sub Private Sub TextBox61_Enter() GetMasterTBox End Sub Private Sub TextBox62_Enter() GetMasterTBox End Sub Private Sub TextBox63_Enter() GetMasterTBox End Sub Private Sub TextBox64_Enter() GetMasterTBox End Sub '*************** END OF CODE *************** -- Rick (MVP - Excel) "ryguy7272" wrote in message ... Sorry for the delayed response here; I was battling a virus the past couple days. For a while it prevented me from getting online, but finally I expunged it, and ultimately prevailed! Im getting a message that reads €˜Subscript out of range and this line is yellow: With Worksheets("Import") Below is my code: Private Sub cmdEnter2_Click() Dim LastPosition As Long Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-6]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If SecondTime = False Dim i As Integer With Worksheets("Import") lastrow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 .Cells(lastrow, 1).Value = txtLastName.Value .Cells(lastrow, 2).Value = txtFirstName.Value .Cells(lastrow, 3).Value = txtMR.Value .Cells(lastrow, 4).Value = txtDate.Value For i = 6 To 60 .Cells(lastrow, i).Value = Me.Controls("TextBox" & i - 6).Text Next i End With 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 '************* END OF CODE ************* I have 69 TextBoxes; four are named and for FName, LName, IDNumber, and Date. The rest, TextBox0 through TextBox64 are set up to receive numbers from users. TextBox0 through TextBox29 should be data-validated for numbers 1-6 and TextBox30 through TextBox64 should be data-validated 1-8. Is this possible with my current loop, or do I need to do away with the loop and code each TextBox separately? Thanks for the help! Ryan--- -- RyGuy "Rick Rothstein" wrote: Unfortunately, that method will not prevent users from pasting in "invalid" characters. Here is some code that I first posted to the compiled VB newsgroups, but have modified for Excel's VBA world... it will restrict the characters allowed in the TextBox (whether typed or pasted) to only the digits 0 thru 6 (the allowable characters are controlled by the list in the 2nd If..Then statement); also note that I have the routine Beep for invalid characters, although the display of a text message is doable if desired... '************* START OF CODE ************* Dim LastPosition As Long Private Sub TextBox1_Change() Static LastText As String Static SecondTime As Boolean If Not SecondTime Then With TextBox1 If .Text Like "*[!0-6]*" Then Beep SecondTime = True .Text = LastText .SelStart = LastPosition Else LastText = .Text End If End With End If 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 '************* END OF CODE ************* -- Rick (MVP - Excel) "Chip Pearson" wrote in message ... You can use the code below to restrict text entry in TextBox1 to the characters "0" to "6". Any other character is rejected and won't be placed in the text box. Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger) Select Case KeyAscii Case Asc("0") To Asc("6") ' OK Case Else KeyAscii = 0 Me.Label1.Caption = "Illegal character." End Select End Sub Cordially, Chip Pearson Microsoft Most Valuable Professional Excel Product Group, 1998 - 2009 Pearson Software Consulting, LLC www.cpearson.com (email on web site) On Sat, 3 Jan 2009 13:58:00 -0800, ryguy7272 wrote: I'm trying to figure out a way to add Data Validation for TextBoxes. I've used this technique befo If TextBox1 = "" Then MsgBox "Please enter a value for 'Name'!!" Exit Sub End If However, I'm not sure how to limit a TextBox to a range of numbers, such as 1-6. How can this be done? Also, how can I do it for many TextBoxes, without specifically adding code for each? A for Each.Next should do it, but I may need to add Data Validation for several TextBoxes, such as 1-20, and then apply a slightly different set of Data Validation rules for other TextBoxes, such as 1-4 for TextBoxes 21-25. How would I go about doing that? Thanks!! Ryan--- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
code for conditional validation to textboxes | Excel Programming | |||
Validation subroutine for textboxes | Excel Programming | |||
Delete Data from textboxes | Excel Programming | |||
Show Results in TextBoxes, ONLY if All TextBoxes have Data in them | Excel Programming | |||
format validation in UserForm textboxes | Excel Programming |