Can't get date formatting right when writing to worksheet

[JONeill]

I have a form that uses date picker controls for a beginning and ending date. I have a listbox that uses the rowsource property to display columns on the data. Whenever I write to the worksheet, the dates display as numbers. Even when I change the cell formatting to short date, then next write changes the format. Both date pickers are set to short date. The immediate window shows valid formats but not after writing to the cells. Here is some code.


The first set of code calls the module that actually writes the collection to the worksheet.

 Set mLog = Factory.CreateMaintLog(Trim(txtProjectName.Text), _
                                            cboLocation.Value, _
                                            dtStartDate.Value, _
                                            dtCompletionDate.Value, _
                                            chkCompleted.Value, _
                                            Trim(txtDescription.Text), _
                                            Trim(txtMaterials.Text))
                                            
'Add item to collection and write to Data worksheet.
colMaintLogData.Add mLog
IData.WriteMaintLogData maintLogParms, colMaintLogData



This code writes to the worksheet...



'Set first row
row = DRngPrms.firstRow
With Sheets(DRngPrms.ActiveDataSheet)
 'Delete current data in spreadsheet
 Range(DRngPrms.DataRange).Clear
 'Write data for each row
 For Each mLog In colData
  .cells(row, colNames.ProjectName).Value2 = mLog.ProjectName
  .cells(row, colNames.Location).Value2 = mLog.Location
  .cells(row, colNames.StartDate).Value2 = mLog.StartDate
  .cells(row, colNames.CompletionDate).Value2 = mLog.CompletionDate
  .cells(row, colNames.Completed).Value2 = mLog.Completed
  .cells(row, colNames.Description).Value2 = mLog.Description
  .cells(row, colNames.Materials).Value2 = mLog.Materials
  row = row + 1
 Next mLog
End With

 

[DanteAmor]

Try:

[COLOR=#333333].cells(row, colNames.StartDate).[/COLOR][COLOR=#0000ff]Value [/COLOR][COLOR=#333333]= [/COLOR][COLOR=#0000ff]cdate[/COLOR][COLOR=#333333](mLog.StartDate)[/COLOR]

 

[vcoolio]

Hello JONeill,

Try removing .Value2 from:-

.Cells(Row, colNames.StartDate).Value2 = mLog.StartDate

In brief, dates are stored as numbers so Value2 will find the underlying value of a cell. Value2 will not check the cell format and convert it to a date (as you would read it). Its pretty well much the same when using just Value. Just some minor differences.

I hope that this helps.

Cheerio,
vcoolio.

 

[JONeill]

Removing the Value2 seems to have done the trick. I had heard that Value2 was designed as a "patch", if you will, to Value and the preferred way was to use it. Seems work fine now. Thanks!

 

[vcoolio]

You're welcome. I'm glad to have been able to help.

Cheerio,
vcoolio.

 

[DanteAmor]

You're welcome