Cell validation

R

Robert Couchman

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
 
R

Rob van Gelder

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
 
B

Bob Phillips

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