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

--
Regards,
Tom Ogilvy

"Michael Beckinsale" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>

[Martin Fishlock]

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.

--
HTHs Martin


"Michael Beckinsale" wrote:

> 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
>
>
>

[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
"Martin Fishlock" <[email protected]> wrote in
message news:[email protected]...
> 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.
>
> --
> HTHs Martin
>
>
> "Michael Beckinsale" wrote:
>
>> 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]

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" <[email protected]> wrote in message
news:[email protected]...
> 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
> "Martin Fishlock" <[email protected]> wrote in
> message news:[email protected]...
> > 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.
> >
> > --
> > HTHs Martin
> >
> >
> > "Michael Beckinsale" wrote:
> >
> >> 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
> >>
> >>
> >>
>
>

[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

"Tom Ogilvy" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>> "Martin Fishlock" <[email protected]> wrote in
>> message news:[email protected]...
>> > 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.
>> >
>> > --
>> > HTHs Martin
>> >
>> >
>> > "Michael Beckinsale" wrote:
>> >
>> >> 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
>> >>
>> >>
>> >>
>>
>>
>
>