Excel VBA Userform Date issue

Hello Andreas

One thing I agree with you on is that we are going around in circles 

If your local settings are mm/dd/yyyy you have to enter the dates as mm/dd/yyyy into the form. If you do so, there are no issues. That is the basic point that you must accept / understand, Excel works that way.

I understand what you are saying - however as ossiemac mentioned to you about using Cdate- my regional and systems settings are UK - dd/mm/yyyy - this is what the relevant cells are also formatted to and all the time relevant cells are set s custom hh:mm ( i dont have any problem with the time issues by the way!) I dont know how this becomes text other than the formatting of the user form text boxes - and that is where my request for help is please

If you want to enter the dates in your Userform in a different format, e.g. dd/mm/yyyy, you have to write your own text/date parser to calculate the real date number from that text. We already said that 20 posts before.

I understand that and I tried that in the various formats - however Im not convinced I am writing the script under -

1/ the correct locatio ie module or form

2/ I get errors on the expressions etc

Your database already contains text and dates mixed up, there is no chance to solve that with your Userform.

The entries are dummy data from ongoing versions - dont worry about this i just didnt clear out the old entries as I changed the VBA code as per your guidance - so yes it is all mixed up but thats not relevant - I just need to reset the code correctly as per your help 

So please can you just tell me what I need to write and where to resolve the user form issues

Than you again

Adrian

version4.5

Execute the code below in your file, as you see most but not all of you dates / times are text and many of them are also invalid dates.

Andreas.

Sub InvalidDates()

  Dim R As Range

  For Each R In Intersect(Range("3:28"), Range("E:L,S:T,W:W"))

    If VarType(R.Value) <> vbDate Then

      With R.Borders(xlDiagonalDown)

        .LineStyle = xlContinuous

        .Color = vbRed

        .Weight = xlThick

      End With

    End If

  Next

End Sub

well thank you for the file with user forms - unfortunately in your userform 1 - you have the same issue - in the link above Ive attached some screen shots of your form to show what you what is happening

If you try Userform2 you can see the interpreted date from Textbox2 within Textbox3 on the form before the date is written into the sheet in your local system settings.

That real date is written into the sheet, and you get the real date back later. There is no difference or exchange in day / month or between the form and the sheet.

If your local settings are mm/dd/yyyy you have to enter the dates as mm/dd/yyyy into the form. If you do so, there are no issues. That is the basic point that you must accept / understand, Excel works that way.

If you want to enter the dates in your Userform in a different format, e.g. dd/mm/yyyy, you have to write your own text/date parser to calculate the real date number from that text. We already said that 20 posts before.

The point is you can not solve that in the way you try, this does never work, resp. has worked, it's an illusion:

.Cells(irow, 23) = [Text(Now(),"DD-MM-YYYY HH:MM:SS")]

Your database already contains text and dates mixed up, there is no chance to solve that with your Userform.

That's what I'm talking about all the time.

Andreas.

version4.5

Hi andreas - well thank you for the file with user forms - unfortunately in your userform 1 - you have the same issue - in the link above Ive attached some screen shots of your form to show what you what is happening 

it shows in the first screenshot that I inputted 03/11/2020 on the user form- you also see that on saving the worksheet records 11/03/2020 -  this format is then returned as 11/03/2020 when called back into the user form -i tried again with 05/12/2020 and you can see that the same issue exists - which has been always query - why does this happen and how do we fix it 

Ive also added screen shots from my user form showing the same problem - follow from the user input, the addition to the worksheet and user form list box and finally returning the data back into the user form for editing

I have also attached the latest version 4.5 of my code 

I hope this now demonstrates to you the problem

Thank you for your help - hopefully we can resolve this yet

Adrian

ongoing root problem - the fact that user input data is being saved onto a worksheet in what the user sees as the correct date format eg 03/10/2020 but on the worksheet it is saved as 10/03/2020 and that is what is returned to the user form should the entry be recalled for editing at any later point in time - If the worksheet is edited directly to change the date into the correct format - 03/10/2020 - that is exactly what is returned, in the correct format - simple

We are spinning in a circle.

Try Userform2 in this file to add dates:

https://www.dropbox.com/s/s2ox98stgxnc76j/1c095b61-b8e8-4395-9b15-5a5419832828.xlsm?dl=1

Andreas.