M
Michael Beckinsale
Hi All,
I have a form to input staff details and l am having trouble verifying the
date format. The staff details exist on 1 Excel sheet in a "database" and
when the form is lauched the existing details are displayed in the form. The
DOB column is formatted as dd/mm/yyyy as is the short date in the systems
regional settings.
The code below is a snippet of the full code but is flawed in that:
1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click
2) If l dont declare the variable type it runs fine but if the user
overwrites the date it is written to Excel in the incorrect format
Can anybody please tell me where l am going wrong as l have spent hours
trying to correct this problem. All comments gratefully received.
Dim DOB As Date
Dim Nino As String
Dim Surname As String
Private Sub UserForm_Initialize
tbDOB.Text = ActiveCell.Offset(0, 3).Value
End Sub
Private Sub cmbHREnter_Click()
DOB = tbDOB.Value
If Nino = "" Or _
Surname = "" Or _
DOB = "" Or _
Then
MsgBox ("One of the compulsory fields is blank. Please re-enter")
Else
ActiveCell.Offset(0, -1).Value = Nino
ActiveCell.Value = Surname
ActiveCell.Offset(0, 3).Value = DOB
End If
End Sub
Regards
Michael beckinsale
I have a form to input staff details and l am having trouble verifying the
date format. The staff details exist on 1 Excel sheet in a "database" and
when the form is lauched the existing details are displayed in the form. The
DOB column is formatted as dd/mm/yyyy as is the short date in the systems
regional settings.
The code below is a snippet of the full code but is flawed in that:
1) As it is it crashes on the IF, OR, Then statement for cmbHREnter_Click
2) If l dont declare the variable type it runs fine but if the user
overwrites the date it is written to Excel in the incorrect format
Can anybody please tell me where l am going wrong as l have spent hours
trying to correct this problem. All comments gratefully received.
Dim DOB As Date
Dim Nino As String
Dim Surname As String
Private Sub UserForm_Initialize
tbDOB.Text = ActiveCell.Offset(0, 3).Value
End Sub
Private Sub cmbHREnter_Click()
DOB = tbDOB.Value
If Nino = "" Or _
Surname = "" Or _
DOB = "" Or _
Then
MsgBox ("One of the compulsory fields is blank. Please re-enter")
Else
ActiveCell.Offset(0, -1).Value = Nino
ActiveCell.Value = Surname
ActiveCell.Offset(0, 3).Value = DOB
End If
End Sub
Regards
Michael beckinsale