View Full Version : Solved: How to validate data in a userform prior to the data being transferred to a worksheet
bananatang
08-05-2009, 03:04 AM
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
08-05-2009, 05:06 AM
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
bananatang
08-06-2009, 05:37 AM
Xld,
You are my world when it comes to excel vba.:friends:
Thank you so much for your code and all your efforts. It works a treat.
Thanks
BT
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.