Forms - Verify date format

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Michael Beckinsale - 23 Jan 2006 15:55 GMT

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

Reply to this Message

Tom Ogilvy - 23 Jan 2006 16:20 GMT

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

Signature

Regards,
Tom Ogilvy

> Hi All,
>
[quoted text clipped - 38 lines]
>
> Michael beckinsale

Reply to this Message

Martin Fishlock - 23 Jan 2006 16:22 GMT

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.

Signature

HTHs Martin

> Hi All,
>
[quoted text clipped - 38 lines]
>
> Michael beckinsale

Reply to this Message

Michael Beckinsale - 23 Jan 2006 17:01 GMT

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

> Michael:
>
[quoted text clipped - 50 lines]
>>
>> Michael beckinsale

Reply to this Message

Tom Ogilvy - 23 Jan 2006 17:20 GMT

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/

Signature

Regards,
Tom Ogilvy

> Tom,
>
[quoted text clipped - 94 lines]
> >>
> >> Michael beckinsale

Reply to this Message

Michael Beckinsale - 23 Jan 2006 17:30 GMT

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

> the reason I used cdate is that it understands the regional date setting.
>
[quoted text clipped - 149 lines]
>> >>
>> >> Michael beckinsale

Reply to this Message