james5478
11-21-2011, 10:43 PM
]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
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