Solved: How to validate data in a userform prior to the data being transferred to a worksheet

[bananatang]

HI,

Need this forums wisdom once again.

I am working on creating a userform that once completed will first check all text boxes and combo box are populated with data. The textbox and combo box has a default message, therefore, the data must be something else apart from the default message.

If data is missing, i would like a msgbox with a generic message informing users that they need to populate the userform with pupils details.

If all data is present, i would like the data to be inserted into a new worksheet. The template i would be using is called Attendance and finally i would like the name of the new worksheet to be called the name of the pupil as per the value of the first text box.

I have included the file for ease of understanding my problem.

i would appreciate all assistance.

Thanks

BT

[Bob Phillips]

[vba]

Private Sub CommandButton1_Click()
ActiveWorkbook.Sheets("Feedbackentry").Activate
Range("A2").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

End Sub


Private Sub cmdCancel_Click()
Unload Me

End Sub

Private Sub cmdClearForm_Click()

Txtq1.Value = Null
Txtq2.Value = Null
cboq3.Value = Null
cboq4.Value = Null
Cboq5.Value = Null
Txtcomments.Value = Null
Optfixed.Value = Null
Optperm.Value = Null
Optmt.Value = Null
Optother.Value = Null
Call UserForm_Initialize

End Sub

Private Sub cmdok_Click()

If Txtq1.Text = vbNullString Or _
Cboq5.ListIndex < 0 Or _
cboq3.ListIndex < 0 Or _
cboq4.ListIndex < 0 Or _
Txtq2.Text = vbNullString Or _
(Not Optfixed.Value And Not Optperm.Value And _
Not Optmt.Value And Not Optother.Value) Then

MsgBox "Please input all values"
Exit Sub
End If

Worksheets("Attendance").Copy Before:=Worksheets(Worksheets.Count)
Set sh = ActiveSheet
sh.Name = Txtq1.Text

sh.Range("H4").Value = Txtq1.Value
sh.Range("X6").Value = Txtq2.Value
sh.Range("X4").Value = cboq3.Value ' NCY Input Box
sh.Range("X5").Value = cboq4.Value ' SEN Level Input Boxs
sh.Range("H5").Value = Cboq5.Value ' Name of School input box
sh.Range("A10").Value = Txtcomments.Value

If Optfixed = True Then

sh.Range("H6").Value = "Fixed Exclusion"
ElseIf Optperm = True Then

sh.Range("H6").Value = "Perm Exclusion"
ElseIf Optmt = True Then

sh.Range("H6").Value = "Managed Transfer"
ElseIf Optother = True Then

sh.Range("H6").Value = "Other"
End If

Txtq1.Value = Null
Txtq2.Value = Null
cboq3.Value = Null
cboq4.Value = Null
Cboq5.Value = Null
Txtcomments.Value = Null
Optfixed.Value = Null
Optperm.Value = Null
Optmt.Value = Null
Optother.Value = Null
cboq3.Value = "Please Select Yr Group"
cboq4.Value = "Please Select SEN Status"
Cboq5.Value = "Please Select School name"
End Sub

Private Sub UserForm_Initialize()

Txtq1.Value = ""

cboq3.List = Array(7, 8, 9, 10, 11, "12+")
cboq3.Value = "Please Select Yr Group"

cboq4.List = Array("N = No Provision", "A = School Action", "P = School Action +", _
"Q = School Action + Under Assessment", "S = Statement")
cboq4.Value = "Please Select SEN Status"

With Cboq5
.AddItem "Brentford School For Girls"
.AddItem "Chiswick CS"
.AddItem "Cranford CC"
.AddItem "Feltham CC"
.AddItem "The Green School"
.AddItem "Gumley House"
.AddItem "Gunnersbury CS"
.AddItem "The Heathland School"
.AddItem "Heston CS"
.AddItem "Hounslow Manor"
.AddItem "Isleworth & Syon"
.AddItem "Lampton School"
.AddItem "Longford CS"
.AddItem "St Mark's Catholic School"
End With
Cboq5.Value = "Please Select School name"
End Sub
[/vba]

[bananatang]

Xld,

You are my world when it comes to excel vba.

Thank you so much for your code and all your efforts. It works a treat.

Thanks

BT