Date Formatting Issues

[Pacman52]

Hi all I have a workbook with various Userforms on it for a user to input the relevant data into. I am currently entering some test data and have stumbled across an issue with some date formatting not being written to the worksheet correctly.

The odd thing is this seems to only happen on some rows and not all of them which to me makes me think the problem is with the worksheet formatting not the code behind the Userform. The Worksheet isn't storing the data as a table.

I've tried formatting the relevant columns on the worksheet by clicking on both the column reference letter and the individual cell and even changing the cell format to something other than a date and then back again but nothing seem to work.

Has anyone got any idea what's going with this and how to sort it out. I'm happy to provide more info and show the relevant code from the Userform if need be but as said the fact some rows work and some don't I'm pretty sure its not a code related problem.

Thanks for any responses Paul

 

[hagia_sofia]

Hello, it is hard to tell without seeing a sample of the data. But you could e.g. test the data that is not formatted properly against the same date that is typed properly, i.e. if the following returns some FALSE:

=MID(A1,SEQUENCE(LEN(A1)),1)=MID(B1,SEQUENCE(LEN(B1)),1)

 

[Pacman52]

Thanks for the reply the code below is behind one of the date fields on the Userform

Private Sub txbDatePaid_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If Not IsDate(txbDatePaid) Then
msg "It looks like you've either entered a text value or a blank space, please only enter a date in the correct format e.g. 'DD/MM/YYYY'", vbExclamation, "Invalid Entry"
Cancel = True
txbDatePaid.SetFocus
txbDatePaid = ""
txbDatePaid.BackColor = RGB(204, 255, 255)
Else
txbDatePaid.BackColor = RGB(255, 255, 255)
txbDatePaid = Format(CDate(txbDatePaid), "DD/MM/YYYY")
End If

It is written to the sheet using

.Cells(newRow, 9).Value = txbDatePaid.Value

The relevant columns on the sheet currently look like this..

As mentioned I don't think there is anything wrong with the code although I'm not sure if using BeforeUpdate is the best option but I did it this was so the user sees the date as expected on the Userform as they enter the data.

Do you know if I also added the 'CDate' format code to the line that writes the form data to the worksheet would be of any benefit although again I'm lossed as to why some rows on the worksheet are ok and others are not.

 

[Fluff]

Try

.Cells(newRow, 9).Value = Clng(txbDatePaid.Value)

 

[dmt32]

I also added the 'CDate' format code to the line that writes the form data to the worksheet

Try updating the line that writes to the range

With .Cells(newRow, 9)
    .Value = CDate(txbDatePaid.Value)
    .NumberFormat = "DD/MM/YYYY"
 End With

Dave

 

[Pacman52]

Thanks all, I'll give both options a try and see what happens although do you have any ideas as to why some rows on the worksheet are formatted correctly and others are not?

 

[Joe4]

Thanks all, I'll give both options a try and see what happens although do you have any ideas as to why some rows on the worksheet are formatted correctly and others are not?

I think the issue is that VBA always puts dates in the use format (month/day/year), so if you enter them in the European format (day/month/year), it could lead to some invalid dates (i.e. there is no month 16, so it puts it in text format).

But then you need to be careful about the dates it does accept, as it might be switching around the day and month portion (i.e. 1/5 becomes 5/1).

 

[Pacman52]

I think the issue is that VBA always puts dates in the use format (month/day/year), so if you enter them in the European format (day/month/year), it could lead to some invalid dates (i.e. there is no month 16, so it puts it in text format).

But then you need to be careful about the dates it does accept, as it might be switching around the day and month portion (i.e. 1/5 becomes 5/1).

Thanks Joe funnily enough I've just noticed there are some dates that have switched the days and months around but I thought by formatted to relevant columns using the '*' prefix ensured it formatted to the selected location and resolved this or are you saying as VBA is writing the data to the worksheet is overrides the sheet formatting?

 

[Joe4]

Sheet formatting does not change the value of the cells, only the content.
I think you will need to incorporate one of the solutions from Fluff or dmt32 to ensure the values get entered properly.
I was just explaining why you are seeing what you are seeing, under your original method.

 

[Pacman52]

Hi all thanks as always for the support and advice. I've adjusted all of the code references to dates and made the changes as per dmt32's suggestion added the formatting option to all of the various procedures that write the data to the worksheets entered some more test data and so far so good.

Thanks again Paul