Forms - Verify date format

[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

 

[Tom Ogilvy]

Dim DOB As Date
Dim Nino As String
Dim Surname As String

Private Sub UserForm_Initialize
tbDOB.Text = ActiveCell.Offset(0, 3).Text
End Sub

Private Sub cmbHREnter_Click()
if isdate(tbDob.Value) then
DOB = cdate(tbDOB.Value)
else
DOB = 0
end if
If Nino = "" Or _
Surname = "" Or _
DOB = 0 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

 

[Guest]

Michael:

I think that it is because you are testing DOB as a string and not a date.

DOB = "" Or _

Try testing the value in the cell ie:

len(tbDOB.Value) = 0 ' no text in the box.

 

[Michael Beckinsale]

Tom,

Many thanks. That solved the problem of the IF, OR, THEN statement crashing
however l still get the wrong date format.

For instance if the original date displays in the Excel sheet as 08/09/1955
when the form launches it is in the same format. If the user then enters
11/12/1955
it is written to Excel as 12/11/1955.

I have tried modifying the code using FORMAT ie the code now reads as
follows:

Dim DOB As Date
Dim Nino As String
Dim Surname As String

Private Sub UserForm_Initialize
tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy")
End Sub

Private Sub cmbHREnter_Click()
if isdate(tbDob.Value) then
DOB = cdate(tbDOB.Value)
else
DOB = 0
end if
If Nino = "" Or _
Surname = "" Or _
DOB = 0 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 = Format(DOB, "dd/mm/yyyy")
End If
End Sub

Any ideas ? This is proving to be a right so and so......................

Regards

Michael

 

[Tom Ogilvy]

the reason I used cdate is that it understands the regional date setting.

when you use format and assign the cell the value as a string, you cancel
that out and ask vba to interpret the date which it does using US Centric
interpretation.

If you use
ActiveCell.Offset(0, 3).Value = DOB

as I suggested, where DOB was set with

DOB = cdate(tbDOB.Value)


then it should work for you.

I don't know the point of using format with ActiveCell.Offset(0,3).Text

tbDOB.Text = Format(ActiveCell.Offset(0, 3).Text, "dd/mm/yyyy")

Text would place the date as displayed in the cell.

demo'd from the immediate window:

Activecell.NumberFormat = "mmm dd, yyyy"
? activeCell.Text
Jan 15, 2006
? activeCell.Value
01/15/2006


if you insist on using format (perhaps you are changing the way it is
displayed), then use
tbDOB.Text = Format(ActiveCell.Offset(0, 3).Value, "dd/mm/yyyy")

Avoid using strings to represent dates. Use a date variable or a date serial
number. Use Cdate to convert a string to a date/date serial.


Stephen Bullen has made his chapter on international issues available on the
web: From one of his past posts

There's much more about these types of issue in my 'International
Issues' chapter of John Green's "Excel 2002 VBA Programmer's
Reference", which can also be read online on my web site, at:
http://www.oaltd.co.uk/ExcelProgRef/ch22/

--
Regards,
Tom Ogilvy

 

[Michael Beckinsale]

Tom,

Once again many thanks. Too much "give it a try" coding. Have taken out all
coding that refers to FORMAT and now all appears OK.

Now l need a beer........................

Regards

Michael