Date Format - US to English

I have created a form that requires the user to enter various dates. the
intent is the user will only have to enter day and month by typing d-m (e.g.
for 6th Jan 06 they need only type 6-1)

Using VB - this information is then transferred into the next available
blank record for future updating etc.

Problem is, I can not get the dates to appear in the required format of
dd-mmm-yy .

e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
06-Jan-06

Regional settings on the PC's are set as English-Australian so no problems
there. If I type directly into the cells on the target spreadsheet, the dates
format perfectly.

Below is the code in question -

Private Sub cmdOK_Click()

ActiveWorkbook.Sheets("Jobs").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Offset(0, 0) = txtProntoJobNumber
ActiveCell.Offset(0, 1) = txtDateRaised.Value
ActiveCell.Offset(0, 2) = txtSiteLocation
ActiveCell.Offset(0, 3) = txtJobDescription.Value
ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
ActiveCell.Offset(0, 6) = txtJobStartDate.Value
ActiveCell.Offset(0, 7) = cboTechnician
ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
ActiveCell.Offset(0, 10) = txtComments

If CheckBoxComplete = True Then
ActiveCell.Offset(0, 11) = "Yes"
End If

Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"

Range("A6").Select
End Sub

As you can probably tell, I am a beginner when it comes to VB!!
--
Many Thanks
EGGcel

Try casting it

ActiveCell.Offset(0, 4) = cdate(txtMaterialsOrderedDate.Value)

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Eggtavius" <[email protected]> wrote in message
news:[email protected]...
> I have created a form that requires the user to enter various dates. the
> intent is the user will only have to enter day and month by typing d-m
(e.g.
> for 6th Jan 06 they need only type 6-1)
>
> Using VB - this information is then transferred into the next available
> blank record for future updating etc.
>
> Problem is, I can not get the dates to appear in the required format of
> dd-mmm-yy .
>
> e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> 06-Jan-06
>
> Regional settings on the PC's are set as English-Australian so no problems
> there. If I type directly into the cells on the target spreadsheet, the
dates
> format perfectly.
>
> Below is the code in question -
>
> Private Sub cmdOK_Click()
>
> ActiveWorkbook.Sheets("Jobs").Activate
> Range("A6").Select
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
>
> ActiveCell.Offset(0, 0) = txtProntoJobNumber
> ActiveCell.Offset(0, 1) = txtDateRaised.Value
> ActiveCell.Offset(0, 2) = txtSiteLocation
> ActiveCell.Offset(0, 3) = txtJobDescription.Value
> ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> ActiveCell.Offset(0, 7) = cboTechnician
> ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> ActiveCell.Offset(0, 10) = txtComments
>
> If CheckBoxComplete = True Then
> ActiveCell.Offset(0, 11) = "Yes"
> End If
>
> Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
>
> Range("A6").Select
> End Sub
>
> As you can probably tell, I am a beginner when it comes to VB!!
> --
> Many Thanks
> EGGcel

As usual Bob - you have all the best answers!!
--
Many Thanks
EGGcel


"Bob Phillips" wrote:

> Try casting it
>
> ActiveCell.Offset(0, 4) = cdate(txtMaterialsOrderedDate.Value)
>
> --
>
> HTH
>
> Bob Phillips
>
> (remove nothere from the email address if mailing direct)
>
> "Eggtavius" <[email protected]> wrote in message
> news:[email protected]...
> > I have created a form that requires the user to enter various dates. the
> > intent is the user will only have to enter day and month by typing d-m
> (e.g.
> > for 6th Jan 06 they need only type 6-1)
> >
> > Using VB - this information is then transferred into the next available
> > blank record for future updating etc.
> >
> > Problem is, I can not get the dates to appear in the required format of
> > dd-mmm-yy .
> >
> > e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> > 06-Jan-06
> >
> > Regional settings on the PC's are set as English-Australian so no problems
> > there. If I type directly into the cells on the target spreadsheet, the
> dates
> > format perfectly.
> >
> > Below is the code in question -
> >
> > Private Sub cmdOK_Click()
> >
> > ActiveWorkbook.Sheets("Jobs").Activate
> > Range("A6").Select
> > Do
> > If IsEmpty(ActiveCell) = False Then
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop Until IsEmpty(ActiveCell) = True
> >
> > ActiveCell.Offset(0, 0) = txtProntoJobNumber
> > ActiveCell.Offset(0, 1) = txtDateRaised.Value
> > ActiveCell.Offset(0, 2) = txtSiteLocation
> > ActiveCell.Offset(0, 3) = txtJobDescription.Value
> > ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> > ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> > ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> > ActiveCell.Offset(0, 7) = cboTechnician
> > ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> > ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> > ActiveCell.Offset(0, 10) = txtComments
> >
> > If CheckBoxComplete = True Then
> > ActiveCell.Offset(0, 11) = "Yes"
> > End If
> >
> > Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
> >
> > Range("A6").Select
> > End Sub
> >
> > As you can probably tell, I am a beginner when it comes to VB!!
> > --
> > Many Thanks
> > EGGcel
>
>
>

This solution works in my arrangement as long as the user enters dates in
each of the Date fields.

If the user leaves a date field blank - a Run Time error '13' appears

Have been trying to come up with a work around using If statements but no
luck so far - any ideas?
--
Many Thanks
EGGcel


"Eggtavius" wrote:

> As usual Bob - you have all the best answers!!
> --
> Many Thanks
> EGGcel
>
>
> "Bob Phillips" wrote:
>
> > Try casting it
> >
> > ActiveCell.Offset(0, 4) = cdate(txtMaterialsOrderedDate.Value)
> >
> > --
> >
> > HTH
> >
> > Bob Phillips
> >
> > (remove nothere from the email address if mailing direct)
> >
> > "Eggtavius" <[email protected]> wrote in message
> > news:[email protected]...
> > > I have created a form that requires the user to enter various dates. the
> > > intent is the user will only have to enter day and month by typing d-m
> > (e.g.
> > > for 6th Jan 06 they need only type 6-1)
> > >
> > > Using VB - this information is then transferred into the next available
> > > blank record for future updating etc.
> > >
> > > Problem is, I can not get the dates to appear in the required format of
> > > dd-mmm-yy .
> > >
> > > e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> > > 06-Jan-06
> > >
> > > Regional settings on the PC's are set as English-Australian so no problems
> > > there. If I type directly into the cells on the target spreadsheet, the
> > dates
> > > format perfectly.
> > >
> > > Below is the code in question -
> > >
> > > Private Sub cmdOK_Click()
> > >
> > > ActiveWorkbook.Sheets("Jobs").Activate
> > > Range("A6").Select
> > > Do
> > > If IsEmpty(ActiveCell) = False Then
> > > ActiveCell.Offset(1, 0).Select
> > > End If
> > > Loop Until IsEmpty(ActiveCell) = True
> > >
> > > ActiveCell.Offset(0, 0) = txtProntoJobNumber
> > > ActiveCell.Offset(0, 1) = txtDateRaised.Value
> > > ActiveCell.Offset(0, 2) = txtSiteLocation
> > > ActiveCell.Offset(0, 3) = txtJobDescription.Value
> > > ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> > > ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> > > ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> > > ActiveCell.Offset(0, 7) = cboTechnician
> > > ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> > > ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> > > ActiveCell.Offset(0, 10) = txtComments
> > >
> > > If CheckBoxComplete = True Then
> > > ActiveCell.Offset(0, 11) = "Yes"
> > > End If
> > >
> > > Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> > > Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> > > Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> > > Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> > > Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> > > Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
> > >
> > > Range("A6").Select
> > > End Sub
> > >
> > > As you can probably tell, I am a beginner when it comes to VB!!
> > > --
> > > Many Thanks
> > > EGGcel
> >
> >
> >

I have found a solution to the Run Time error - seems messy - is there a
better way?


Private Sub cmdOK_Click()

ActiveWorkbook.Sheets("Jobs").Activate
Range("A6").Select
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True

ActiveCell.Offset(0, 0) = txtProntoJobNumber
ActiveCell.Offset(0, 1) = CDate(txtDateRaised.Value)
ActiveCell.Offset(0, 2) = txtSiteLocation
ActiveCell.Offset(0, 3) = txtJobDescription.Value

If txtMaterialsOrderedDate.Value <> "" Then
ActiveCell.Offset(0, 4) = CDate(txtMaterialsOrderedDate.Value)
End If

If txtMaterialsAvailableDate.Value <> "" Then
ActiveCell.Offset(0, 5) = CDate(txtMaterialsAvailableDate.Value)
End If

If txtJobStartDate.Value <> "" Then
ActiveCell.Offset(0, 6) = CDate(txtJobStartDate.Value)
End If

ActiveCell.Offset(0, 7) = cboTechnician

If txtScheduledCompletionDate.Value <> "" Then
ActiveCell.Offset(0, 8) = CDate(txtScheduledCompletionDate.Value)
End If

If txtActualCompletionDate.Value <> "" Then
ActiveCell.Offset(0, 9) = CDate(txtActualCompletionDate.Value)
End If

ActiveCell.Offset(0, 10) = txtComments

If CheckBoxComplete = True Then
ActiveCell.Offset(0, 11) = "Yes"
End If

Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"

Range("A6").Select
End Sub

--
Many Thanks
EGGcel


"Eggtavius" wrote:

> I have created a form that requires the user to enter various dates. the
> intent is the user will only have to enter day and month by typing d-m (e.g.
> for 6th Jan 06 they need only type 6-1)
>
> Using VB - this information is then transferred into the next available
> blank record for future updating etc.
>
> Problem is, I can not get the dates to appear in the required format of
> dd-mmm-yy .
>
> e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> 06-Jan-06
>
> Regional settings on the PC's are set as English-Australian so no problems
> there. If I type directly into the cells on the target spreadsheet, the dates
> format perfectly.
>
> Below is the code in question -
>
> Private Sub cmdOK_Click()
>
> ActiveWorkbook.Sheets("Jobs").Activate
> Range("A6").Select
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
>
> ActiveCell.Offset(0, 0) = txtProntoJobNumber
> ActiveCell.Offset(0, 1) = txtDateRaised.Value
> ActiveCell.Offset(0, 2) = txtSiteLocation
> ActiveCell.Offset(0, 3) = txtJobDescription.Value
> ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> ActiveCell.Offset(0, 7) = cboTechnician
> ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> ActiveCell.Offset(0, 10) = txtComments
>
> If CheckBoxComplete = True Then
> ActiveCell.Offset(0, 11) = "Yes"
> End If
>
> Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
>
> Range("A6").Select
> End Sub
>
> As you can probably tell, I am a beginner when it comes to VB!!
> --
> Many Thanks
> EGGcel

You may want to try a calendar control:

Ron de Bruin has some tips/links at:
http://www.rondebruin.nl/calendar.htm

It'll remove any ambiguity on data entry: What is this date: 01/02/03?


Eggtavius wrote:
>
> I have found a solution to the Run Time error - seems messy - is there a
> better way?
>
> Private Sub cmdOK_Click()
>
> ActiveWorkbook.Sheets("Jobs").Activate
> Range("A6").Select
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
>
> ActiveCell.Offset(0, 0) = txtProntoJobNumber
> ActiveCell.Offset(0, 1) = CDate(txtDateRaised.Value)
> ActiveCell.Offset(0, 2) = txtSiteLocation
> ActiveCell.Offset(0, 3) = txtJobDescription.Value
>
> If txtMaterialsOrderedDate.Value <> "" Then
> ActiveCell.Offset(0, 4) = CDate(txtMaterialsOrderedDate.Value)
> End If
>
> If txtMaterialsAvailableDate.Value <> "" Then
> ActiveCell.Offset(0, 5) = CDate(txtMaterialsAvailableDate.Value)
> End If
>
> If txtJobStartDate.Value <> "" Then
> ActiveCell.Offset(0, 6) = CDate(txtJobStartDate.Value)
> End If
>
> ActiveCell.Offset(0, 7) = cboTechnician
>
> If txtScheduledCompletionDate.Value <> "" Then
> ActiveCell.Offset(0, 8) = CDate(txtScheduledCompletionDate.Value)
> End If
>
> If txtActualCompletionDate.Value <> "" Then
> ActiveCell.Offset(0, 9) = CDate(txtActualCompletionDate.Value)
> End If
>
> ActiveCell.Offset(0, 10) = txtComments
>
> If CheckBoxComplete = True Then
> ActiveCell.Offset(0, 11) = "Yes"
> End If
>
> Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
>
> Range("A6").Select
> End Sub
>
> --
> Many Thanks
> EGGcel
>
> "Eggtavius" wrote:
>
> > I have created a form that requires the user to enter various dates. the
> > intent is the user will only have to enter day and month by typing d-m (e.g.
> > for 6th Jan 06 they need only type 6-1)
> >
> > Using VB - this information is then transferred into the next available
> > blank record for future updating etc.
> >
> > Problem is, I can not get the dates to appear in the required format of
> > dd-mmm-yy .
> >
> > e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> > 06-Jan-06
> >
> > Regional settings on the PC's are set as English-Australian so no problems
> > there. If I type directly into the cells on the target spreadsheet, the dates
> > format perfectly.
> >
> > Below is the code in question -
> >
> > Private Sub cmdOK_Click()
> >
> > ActiveWorkbook.Sheets("Jobs").Activate
> > Range("A6").Select
> > Do
> > If IsEmpty(ActiveCell) = False Then
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop Until IsEmpty(ActiveCell) = True
> >
> > ActiveCell.Offset(0, 0) = txtProntoJobNumber
> > ActiveCell.Offset(0, 1) = txtDateRaised.Value
> > ActiveCell.Offset(0, 2) = txtSiteLocation
> > ActiveCell.Offset(0, 3) = txtJobDescription.Value
> > ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> > ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> > ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> > ActiveCell.Offset(0, 7) = cboTechnician
> > ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> > ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> > ActiveCell.Offset(0, 10) = txtComments
> >
> > If CheckBoxComplete = True Then
> > ActiveCell.Offset(0, 11) = "Yes"
> > End If
> >
> > Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
> >
> > Range("A6").Select
> > End Sub
> >
> > As you can probably tell, I am a beginner when it comes to VB!!
> > --
> > Many Thanks
> > EGGcel

--

Dave Peterson

That's how I would do it, with just a small readability change

With txtMaterialsOrderedDate
If .Value <> "" Then
ActiveCell.Offset(0, 4) = CDate(.Value)
End If
End With

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Eggtavius" <[email protected]> wrote in message
news:[email protected]...
> I have found a solution to the Run Time error - seems messy - is there a
> better way?
>
>
> Private Sub cmdOK_Click()
>
> ActiveWorkbook.Sheets("Jobs").Activate
> Range("A6").Select
> Do
> If IsEmpty(ActiveCell) = False Then
> ActiveCell.Offset(1, 0).Select
> End If
> Loop Until IsEmpty(ActiveCell) = True
>
> ActiveCell.Offset(0, 0) = txtProntoJobNumber
> ActiveCell.Offset(0, 1) = CDate(txtDateRaised.Value)
> ActiveCell.Offset(0, 2) = txtSiteLocation
> ActiveCell.Offset(0, 3) = txtJobDescription.Value
>
> If txtMaterialsOrderedDate.Value <> "" Then
> ActiveCell.Offset(0, 4) = CDate(txtMaterialsOrderedDate.Value)
> End If
>
> If txtMaterialsAvailableDate.Value <> "" Then
> ActiveCell.Offset(0, 5) =
CDate(txtMaterialsAvailableDate.Value)
> End If
>
> If txtJobStartDate.Value <> "" Then
> ActiveCell.Offset(0, 6) = CDate(txtJobStartDate.Value)
> End If
>
> ActiveCell.Offset(0, 7) = cboTechnician
>
> If txtScheduledCompletionDate.Value <> "" Then
> ActiveCell.Offset(0, 8) =
CDate(txtScheduledCompletionDate.Value)
> End If
>
> If txtActualCompletionDate.Value <> "" Then
> ActiveCell.Offset(0, 9) = CDate(txtActualCompletionDate.Value)
> End If
>
> ActiveCell.Offset(0, 10) = txtComments
>
> If CheckBoxComplete = True Then
> ActiveCell.Offset(0, 11) = "Yes"
> End If
>
> Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
>
> Range("A6").Select
> End Sub
>
> --
> Many Thanks
> EGGcel
>
>
> "Eggtavius" wrote:
>
> > I have created a form that requires the user to enter various dates. the
> > intent is the user will only have to enter day and month by typing d-m
(e.g.
> > for 6th Jan 06 they need only type 6-1)
> >
> > Using VB - this information is then transferred into the next available
> > blank record for future updating etc.
> >
> > Problem is, I can not get the dates to appear in the required format of
> > dd-mmm-yy .
> >
> > e.g. entering 6-1 on the form will come out as 01-Jun-06 when we want
> > 06-Jan-06
> >
> > Regional settings on the PC's are set as English-Australian so no
problems
> > there. If I type directly into the cells on the target spreadsheet, the
dates
> > format perfectly.
> >
> > Below is the code in question -
> >
> > Private Sub cmdOK_Click()
> >
> > ActiveWorkbook.Sheets("Jobs").Activate
> > Range("A6").Select
> > Do
> > If IsEmpty(ActiveCell) = False Then
> > ActiveCell.Offset(1, 0).Select
> > End If
> > Loop Until IsEmpty(ActiveCell) = True
> >
> > ActiveCell.Offset(0, 0) = txtProntoJobNumber
> > ActiveCell.Offset(0, 1) = txtDateRaised.Value
> > ActiveCell.Offset(0, 2) = txtSiteLocation
> > ActiveCell.Offset(0, 3) = txtJobDescription.Value
> > ActiveCell.Offset(0, 4) = txtMaterialsOrderedDate.Value
> > ActiveCell.Offset(0, 5) = txtMaterialsAvailableDate.Value
> > ActiveCell.Offset(0, 6) = txtJobStartDate.Value
> > ActiveCell.Offset(0, 7) = cboTechnician
> > ActiveCell.Offset(0, 8) = txtScheduledCompletionDate.Value
> > ActiveCell.Offset(0, 9) = txtActualCompletionDate.Value
> > ActiveCell.Offset(0, 10) = txtComments
> >
> > If CheckBoxComplete = True Then
> > ActiveCell.Offset(0, 11) = "Yes"
> > End If
> >
> > Worksheets("Jobs").Columns("B").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("E").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("F").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("G").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("I").NumberFormat = "dd-mmm-yy"
> > Worksheets("Jobs").Columns("J").NumberFormat = "dd-mmm-yy"
> >
> > Range("A6").Select
> > End Sub
> >
> > As you can probably tell, I am a beginner when it comes to VB!!
> > --
> > Many Thanks
> > EGGcel