Solved: Dates Screwing up!

[thomas.szwed]

Hi there,

When using my userform, I enter the date 08/07/2008 into my textbox. the code behind then writes this to an excel sheet. However when i look at the excel sheet it appears in the wrong way round 07/08/2008? and then when i view the record on my userform it appears as 39637? Can anyone explain whats going on and how to fix this?


thanks in advance!

[mdmackillop]

B#**$$ Yanks!
Try something like

Private Sub CommandButton1_Click()
Range("A1") = DateValue(TextBox1)
End Sub

Private Sub CommandButton2_Click()
TextBox2 = Format(Range("A1"), "d-mmmm-yy")
End Sub

[RonMcK]

[...] and then when i view the record on my userform it appears as 39637? Can anyone explain whats going on ... ?
Malcolm covered the how to fix it, the 39637 you see is the serial number (MS's idea of a Julian Number) for the date in question, this is how MS Excel stores dates and what it uses for date arithmetic. A trailing decimal value stores the time of day.

HTH,

[Bob Phillips]

I just cast it



If IsDate(TextBox1.Text) then
myDate=cdate(TextBox1.Text)
End If

[thomas.szwed]

Thanks for your responses. Say if i had just once column (J) full of dates in my spreadsheet. Would there be a way to write in the workbook open feature for it to format them into the correct format???

So therefore everytime the workbook was opened, the date would always appear in the correct format dd/mm/yyy in both the spreadsheet and the userform?

Thanks in advance..

[Bob Phillips]

Private Sub Workbook_Open()

With ThisWorkbook.Worksheets(1)
.Columns("J").NumberFormat = "dd/mm/yyyy"
End With

End Sub


This is workbook event code.
To input this code, right click on the Excel icon on the worksheet
(or next to the File menu if you maximise your workbooks),
select View Code from the menu, and paste the code

In your userform, use the Text property of the cell, not Value.

[thomas.szwed]

Thanks for this XLD, but i am unsure what you mean when you say use the text property of a cell? Do i need to define something on my userform aswell, for them to stop appearing as numbers?

Thanks

[Bob Phillips]

Normally, when you get the value from a cell you use say

Range("A1").Value

this returns the cell's value, which is not necessarily what you see. If you use the text property

Range("A1").Text

You get what you see.

[thomas.szwed]

Arr i see. Could you have a look at the following.

See attached workbook. Password = pastille

I have changed the code to .text on the functions that
a) UPDATE the userform (see Userform AddNewStarter Line 47)
b) Write to the userform (see Userform AddNewStarter Line 664)

But I am still having problems when

i) user enters in 07/07/2008 or 08/07/2008 etc - seems to automatically change to 5 digit number??

ii) column 7's dates also are displaying incorrectly despite entering in the code in Workbook Open to change this that you recommended??

Would appreciate if you could have a look at this? To open the userform click on the pencil icon.

Thanks

[Bob Phillips]

664 seems right, but 64 seems back to front to me, I read this as updating the cells from the form.

You need to change the worksheet range property when loading the form such as line 160 becomes



.ddlStartDate.Value = c.Offset(0, 4).Text


and similalrly for 161, and 165

[thomas.szwed]

Thats correct though. This is an add function. Therefore it adds the values in the textboxes on the userform into the spreadsheet.

Try adding in a new record, with random data, but try adding in the date 07/07/2008 for 'VCS Received' and you will see the error I mean. I cant seem to find away around this.

Thanks in advance.

[Bob Phillips]

I don't see the problem, I entered 07/07/2008 and it put it on the worksheet.

[thomas.szwed]

or 08/07/2008 then?

OK but if you view a current record in the userform then the date displayed in the VCS textbox is in digits?

[Bob Phillips]

Yes, it is, but I showed you how to correct that earlier, it is line 165.

[thomas.szwed]

I can see what you mean XLD, but this is the not the function that loads the userform, because you have to click Search button for that.

If you just type in a row number into the bottom hand corner box of the userform it will skip and display that records results and the date displayed there on VCS is still incorrect? Thats not using the 'btnSearch_click' method, its using Private Sub UpdateDetails() which is line 658....

you see?

[thomas.szwed]

Private Sub Workbook_Open()

With ThisWorkbook.Worksheets(1)
.Columns("J").NumberFormat = "dd/mm/yyyy"
End With

End Sub


XLD - how would i say format columns E F and J for all the sheets in my workbook in the workbook open event?

thanks in advance

[Aussiebear]

Change the line

.Columns("J").NumberFormat = "dd/mm/yyyy"

to


.Columns ("E","F","J").NumberFormat = "dd/mm/yyyy"

and see what happens