Data Validation for TextBoxes

R

ryguy7272

I’m trying to figure out a way to add Data Validation for TextBoxes. I’ve
used this technique before:
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---
 
C

Chip Pearson

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)
 
R

Rick Rothstein

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 *************
 
R

ryguy7272

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!

I’m 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 said:
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 said:
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)
Click to expand...
Click to expand...
 
R

Rick Rothstein

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 said:
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!

I’m 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 said:
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 said:
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

I'm trying to figure out a way to add Data Validation for TextBoxes.
I've
used this technique before:
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---
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Looping through TextBoxes 0
Pass Parameter from Excel to SharePoint 1
Comparing Apples and Oranges 0
get Data Validation to stop, list supplied, duplicate data entries 1
xlFillDefault, based on values in ColA to ColZ 5
Arbitrary data to Textboxes in UserForm 4
Cell index? 5
Validations with messages in userform 0

Top