Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Discussion (Misc queries) > Date Format - US to English
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
Eggtavius
 
Posts: n/a
Default 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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Date Format - US to English
Try casting it

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

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Eggtavius" wrote in message
...
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


Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.misc
Eggtavius
 
Posts: n/a
Default Date Format - US to English
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" wrote in message
...
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



Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.misc
Eggtavius
 
Posts: n/a
Default Date Format - US to English
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" wrote in message
...
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



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.misc
Eggtavius
 
Posts: n/a
Default Date Format - US to English
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
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.misc
Dave Peterson
 
Posts: n/a
Default Date Format - US to English
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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.misc
Bob Phillips
 
Posts: n/a
Default Date Format - US to English
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" wrote in message
...
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


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
How do I keep the date from changing format in a mail merge? Brynn Wilson Excel Discussion (Misc queries) 2 February 1st 07 05:19 PM
I cannot change the date format to English canada DStanfield Excel Discussion (Misc queries) 2 September 28th 05 06:23 PM
format date in excel Nigel Excel Discussion (Misc queries) 2 September 15th 05 09:52 PM
How do I keep the date from changing format in a mail merge? Brynn Wilson Excel Discussion (Misc queries) 1 June 9th 05 06:44 PM
USING THE DATE FORMAT IN EXCEL teach Excel Discussion (Misc queries) 3 December 14th 04 11:55 PM


All times are GMT +1. The time now is 03:33 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement