Hi all,

can somebody please help me?

i have a userform that asks uses to type in the results to
the questions, but for each answer there is 4 textboxes,
and in each one there can only be a number between 1 and 4
in them, also the number cannot be repeated in the
following textboxes.

if this is possible please could i also have the piece of
code that will select the textbox information if it is not
valid and then allow the user to retype the information.

thank you,

Robert Couchman

Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Function ValidateTextBoxes() As Boolean
ValidateTextBoxes = Not ( _
(Len(TextBox1.Text) 0 And _
(TextBox1.Text = TextBox2.Text Or _
TextBox1.Text = TextBox3.Text Or _
TextBox1.Text = TextBox4.Text)) Or _
(Len(TextBox2.Text) 0 And _
(TextBox2.Text = TextBox3.Text Or _
TextBox2.Text = TextBox4.Text)) Or _
(Len(TextBox3.Text) 0 And _
TextBox3.Text = TextBox4.Text))
End Function


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Robert Couchman" wrote in message
...
Hi all,

can somebody please help me?

i have a userform that asks uses to type in the results to
the questions, but for each answer there is 4 textboxes,
and in each one there can only be a number between 1 and 4
in them, also the number cannot be repeated in the
following textboxes.

if this is possible please could i also have the piece of
code that will select the textbox information if it is not
valid and then allow the user to retype the information.

thank you,

Robert Couchman

Robert,

Here's an improved (G) version of Rob's code. It only allows a 1 to 4 to be
keyed in, and if Rob's code traps an error, it highlights the field to
facilitate easy change (which I think is what you were originally asking
for)
Option Explicit

Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 49 Or KeyAscii 52 Or Len(Me.TextBox1.Text) = 1 Then
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
End If
End Sub


Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ValidateTextBoxes Then
With Me.TextBox1
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Cancel = True
End If
End Sub

Private Sub TextBox2_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 49 Or KeyAscii 52 Or Len(Me.TextBox2.Text) = 1 Then
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
End If
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ValidateTextBoxes Then
With Me.TextBox2
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Cancel = True
End If
End Sub

Private Sub TextBox3_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 49 Or KeyAscii 52 Or Len(Me.TextBox3.Text) = 1 Then
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
End If
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ValidateTextBoxes Then
With Me.TextBox3
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Cancel = True
End If
End Sub

Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
If KeyAscii < 49 Or KeyAscii 52 Or Len(Me.TextBox4.Text) = 1 Then
Application.EnableEvents = False
KeyAscii = 0
Application.EnableEvents = True
End If
End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not ValidateTextBoxes Then
With Me.TextBox4
.SelStart = 0
.SelLength = Len(.Text)
.SetFocus
End With
Cancel = True
End If
End Sub

Private Function ValidateTextBoxes() As Boolean
ValidateTextBoxes = Not ( _
(Len(TextBox1.Text) 0 And _
(TextBox1.Text = TextBox2.Text Or _
TextBox1.Text = TextBox3.Text Or _
TextBox1.Text = TextBox4.Text)) Or _
(Len(TextBox2.Text) 0 And _
(TextBox2.Text = TextBox3.Text Or _
TextBox2.Text = TextBox4.Text)) Or _
(Len(TextBox3.Text) 0 And _
TextBox3.Text = TextBox4.Text))
End Function


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Rob van Gelder" wrote in message
...
Private Sub TextBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox3_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Cancel = Not ValidateTextBoxes
End Sub

Private Function ValidateTextBoxes() As Boolean
ValidateTextBoxes = Not ( _
(Len(TextBox1.Text) 0 And _
(TextBox1.Text = TextBox2.Text Or _
TextBox1.Text = TextBox3.Text Or _
TextBox1.Text = TextBox4.Text)) Or _
(Len(TextBox2.Text) 0 And _
(TextBox2.Text = TextBox3.Text Or _
TextBox2.Text = TextBox4.Text)) Or _
(Len(TextBox3.Text) 0 And _
TextBox3.Text = TextBox4.Text))
End Function


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"Robert Couchman" wrote in message
...
Hi all,

can somebody please help me?

i have a userform that asks uses to type in the results to
the questions, but for each answer there is 4 textboxes,
and in each one there can only be a number between 1 and 4
in them, also the number cannot be repeated in the
following textboxes.

if this is possible please could i also have the piece of
code that will select the textbox information if it is not
valid and then allow the user to retype the information.

thank you,

Robert Couchman