Excel VBA Userform Date issue

Hello Andreas

Thank you again but again you are missing my point - I know that the value in the text box is text - what im askinis where and how do i make the date conversion ?

In my mind there are two options

1/ either a script that runs as the data (text) is entered into the text box ie the txtdate box by a code attached to an operation to the textbox eg before/after update or

2/ when the user presses the save button ( Sub_Submit) and the user form data is passed over to the worksheet and into the relevant cells

So that is question - the worksheet formatting is correct for the appropriate date format - so where is the most appropriate place to carry out the conversion ??

I have tried a number of different ways including adding FORMAT to the entry which swaps the data to the worksheet - taken from the above extrat i sent you

   .Cells(irow, 18) = frmincident.txtdate.Value = Format( ..........

This didnt work

Can you advise me on this as it is sending around the bend

Adrian

So now all data on the worksheet is saved in UK format dd/mm/yyyy as you pointed out - so now there are no text formatting of dates or times in the worksheet

The problem is that it seems that user form txtdate value, on saving to the worksheet is being saved in mm/dd/yyyyy format -

Adrian,

the only problem is that you do not understand that you can not save a date in a special format.

A date in Excel is a number, not a text!

Make a new file

Apply the formula  =TODAY()  into A1

Format A1 as General

And you'll see this number for 20 May 2020:

You have to write that number into the sheet, not a text, in whatever format!

(Technical details: If you write a text into a cell that looks like a date, Excel tries to convert the text into a date / into a number. That's the reason why you think Excel stores the date into a different format, but that's a wrong conclusion.

If the conversation fails, Excel stores a text into the cell. And it doesn't matter which format the cell has before or after. That's the reason for all that wrong dates / errors in your database.

Means: Your form will never work if you continue to write text.)

Again:

Convert the text from your Textbox into a date variable and write that variable into the sheet.

After that you can format the cell with any date format you like, doesn't matter.

To get the date from the sheet:

Read the value from the cell and use FormatDateTime to convert it into a text

I already showed you an example how that works and how it looks like on systems with different languages.

That works all around the world, even if you share the file.

Andreas.

Hi Andreas

Thank you very much for your assistance - seems to be working really well for what I wanted 

But :-( going back to my original problem that we discussed - that being the date issue - even though I added the relevant code to the form as below for editing the data already saved in the work sheet

    Me.txtdate.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17), "dd/mm/yyyy")

which is an entry in the code controlled by the command button as per the code below

Private Sub cmdedit_Click()

    If Selected_List = 0 Then

        MsgBox "No Row is Selected.", vbOKOnly + vbInformation, "Edit"

        Exit Sub

    End If

    'Code to update the value to respective controls

    Me.txtRowNumber.Value = Application.WorksheetFunction.Match(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 0), _

    ThisWorkbook.Sheets("Database").Range("A:A"), 0) 'Selected_List + 1

So this resolved the issue of when the data is returned to the user form - where the txtdate box was showing a text number 

So now all data on the worksheet is saved in UK format dd/mm/yyyy as you pointed out - so now there are no text formatting of dates or times in the worksheet

The problem is that it seems that user form txtdate value, on saving to the worksheet is being saved in mm/dd/yyyyy format - ive double checked all the settings to make sure regional and excel formatting is correct but I cant resolve this issue. One thing however is that if the date is changed by editing the worksheet directly, the format stays correct - when returning the data back to the user form for editing - so to me it seems that the format of the txtdate value on the user form is not being saved from the user form to the worksheet - would you agree?

this is the relevant code

Sub Submit()

    Dim sh As Worksheet

    Dim irow As Long

    Set sh = ThisWorkbook.Sheets("Database")

   If frmincident.txtRowNumber.Value = "" Then

        irow = [Counta(Database!A:A)] + 1

   Else

        irow = frmincident.txtRowNumber.Value

   End If

    With sh

        .Cells(irow, 1) = "=Row()-2" 'Dynamic Serial Number

        .Cells(irow, 2) = frmincident.txtcomment.Value

        .Cells(irow, 3) = frmincident.cmbdelay.Value

        .Cells(irow, 5) = frmincident.txtfinished.Value

        .Cells(irow, 7) = frmincident.txtarrival.Value

        .Cells(irow, 9) = frmincident.txtturnout.Value

        .Cells(irow, 11) = frmincident.txtdispatch.Value

        .Cells(irow, 12) = frmincident.cmbproperty.Value

        .Cells(irow, 13) = frmincident.cmbincident.Value

        .Cells(irow, 14) = frmincident.cmbcommunity.Value

        .Cells(irow, 15) = frmincident.cmbstation.Value

        .Cells(irow, 16) = frmincident.cmbcall.Value

        .Cells(irow, 17) = frmincident.txttime.Value

        .Cells(irow, 18) = frmincident.txtdate.Value

        .Cells(irow, 19) = frmincident.txtautomax.Value

        .Cells(irow, 20) = Application.UserName

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

    End With

End Sub

Can you help me with this please? can I add some formatting within the lines irow5 and 18 or do we do it by adding code to the textbox?

PS if you are getting errors because of the arabic text you should change the regional setting for the non unicode to arabic - sorry if you already know that 

Thank you

Adrian

That sounds very good but how do I do that in code - can you give an example please

Downlod this sample file, a bit quick and dirty but works.

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

Show the form

write some data into the textbox, click Add

Click new to create a new record

write some data into the textbox, click Add

You can use the combobox to recall added records

Click Store to write the data into the sheet

Andreas.

Hi Andreas

That sounds very good but how do I do that in code - can you give an example please