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