Validaing Userform Input

[james5478]

]Hello all
I am relatively new to the forum and was searching for some discussion on Excel userforms when I came across the thread below. I wanted to know if someone could help me
I have a Userform that I created for users in the HR group to use to enter information on accepted offers of individuals joining the firm, but haven't been entered in to the system yet. The form works, but as you know, one needs the validation piece to ensure data integrity.
Below is my code for this userform with red text denoting the txtboxes I need validation on. Could you help me with showing me where to insert the validation code? Thanks!

Option Explicit
Private Sub cmdAdd_Click()
Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("AcceptedOffers")
'find first empty row in spreadsheet
iRow = ws.Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row
'check for a name
If Trim(Me.txtName.Value) = "" Then
Me.txtName.SetFocus
MsgBox "Please Enter the Employee Name"
Exit Sub
End If
'copy the data to the database
ws.Cells(iRow, 1).Value = Me.txtName.Value (Needs to be text, LastName,FirstName with no spaces)

ws.Cells(iRow, 4).Value = Me.cmbDept.Value
ws.Cells(iRow, 5).Value = Me.cmbJobCode.Value
ws.Cells(iRow, 7).Value = Me.cmbSup.Value
ws.Cells(iRow, 8).Value = Me.cmbReg.Value
ws.Cells(iRow, 10).Value = Me.cmbCode.Value
ws.Cells(iRow, 11).Value = Me.cmbEMP.Value
ws.Cells(iRow, 12).Value = Me.cmbRep.Value
ws.Cells(iRow, 13).Value = Me.cmbRec.Value
ws.Cells(iRow, 14).Value = Me.cmbHire.Value
ws.Cells(iRow, 15).Value = Me.cmbPosted.Value
ws.Cells(iRow, 16).Value = Me.txtDatePosted.Value (Needs to be a Date in the form of mm/dd/yyyy)
ws.Cells(iRow, 17).Value = Me.txtAcceptedDate.Value (Needs to be a Date in the form of mm/dd/yyyy)
ws.Cells(iRow, 18).Value = Me.txtEffDate.Value (Needs to be a Date in the form of mm/dd/yyyy)

ws.Cells(iRow, 23).Value = Me.txtComments.Value
ws.Cells(iRow, 24).Value = Me.txtSysDate.Value
ws.Cells(iRow, 25).Value = Me.txtUserID.Value
'clear the data
Me.txtName.Value = ""
Me.cmbDept.Value = ""
Me.cmbJobCode.Value = ""
Me.cmbSup.Value = ""
Me.cmbReg.Value = ""
Me.cmbCode.Value = ""
Me.cmbEMP.Value = ""
Me.cmbRep.Value = ""
Me.cmbRec.Value = ""
Me.cmbHire.Value = ""
Me.cmbPosted.Value = ""
Me.txtDatePosted.Value = ""
Me.txtAcceptedDate.Value = ""
Me.txtEffDate.Value = ""
Me.txtComments.Value = ""
'Me.txtSysDate.Value = ""
'Me.txtUserID.Value = ""
Me.txtName.SetFocus
Unload Me
frmOfferEntry.Show
End Sub
Private Sub cmdClose_Click()
Unload Me
End Sub
Private Sub UserForm_Initialize()
txtSysDate.Value = Now()
End Sub
Private Sub UserForm_Activate()
txtUserID.Value = Environ("USERNAME")
End Sub

[Jan Karel Pieterse]

What I usually do is enable each control's Change event and direct that to a central validation routine which might look like this:

Option Explicit
Private Sub txtAcceptedDate_Change()
ValidateInput
End Sub
Private Sub txtDatePosted_Change()
ValidateInput
End Sub
Sub ValidateInput()
Dim bOK As Boolean
If IsDate(txtDatePosted) Then
txtDatePosted.BackColor = vbWindowBackground
bOK = True
Else
txtDatePosted.BackColor = vbYellow
bOK = False
End If
If IsDate(txtAcceptedDate) Then
txtAcceptedDate.BackColor = vbWindowBackground
bOK = bOK And True
Else
txtAcceptedDate.BackColor = vbYellow
bOK = False
End If
If bOK Then
cmbOK.Enabled = True
Else
cmbOK.Enabled = False
End If
End Sub

[Jan Karel Pieterse]

Forgot to mention: I start out by disabling the button that initiates writing to the sheet, so you simply cannot hit OK before everything validates.

[mikerickson]

As to the first, just correcte invalid entries by adding one line to the OP routine.

Me.txtName.Value = Replace(Me.txtName.Text, ",", vbNullString)
ws.Cells(iRow, 1).Value = Me.txtName.Value

[Rob342]

James
4 ways
1 validate the data as Jan suggested put the whole chk routine into 1 module.
2 Change event
3 Afterupdate event
4 before update event, its your choice.

you can also specify the textbox for the date:

Me.txtDatePosted.Text = Format(Me.TxtDatePosted.Text, "mm/dd/yyyy")
Then check the value with IsDate
Or use a calender for the date input much safer.

Just my bit
Rob

[Jan Karel Pieterse]

Or use a calender for the date input much safer.
The calendar control is nice, but not available on many Office setups and even deprecated in Office 2010.

[Rob342]

Jan

Have you seen the one on KbASE, I have used it on a couple of forms now its very good, and does it without having addins or having to update with mscal.ocx onto every PC.

Rob

[Jan Karel Pieterse]

kbAse???

[Rob342]

Jan
Should be KBase from the Vbax menu. sticking keys!
http://www.vbaexpress.com/kb

Rob

[Jan Karel Pieterse]

No, have not tried that one yet, thanks!