Excel VBA Userform Date issue

Hi Ozzie

Just an update - after some pretty rigorous testing it looks like the solution you helped with is stable and everything is working well - thank you so much!!

One last thing please

I want to try and add to the worksheet at what date and time the user closed the worksheet  or excel its self - in a similar way to the entry for saving the user form data entry

Any ideas on how to do that please?

Thanks again

Adrian

Hi Ozzie

Just an update - after some pretty rigorous testing it looks like the solution you helped with is stable and everything is working well - thank you so much!!

One last thing please

I want to try and add to the worksheet at what date and time the user closed the worksheet  or excel its self - in a similar way to the entry for saving the user form data entry

Any ideas on how to do that please?

Thanks again

Adrian

Hi Ozzie

Sorry for the delay in responding - well after a good run through of testing certainly looks like you have got to the bottom of the issue - still not sure why VBA still prefers to revert to m/d/y when the day is < 12 irrespective of the regional formatting etc -and as highlighted in the test code ( I knew about for a while which is what I tried to explain to Andreas ! - there is a video on you tube what really highlights the issue in excel and touches on the same point for UK DD/MM/YYYY issues - note now the worksheet is also correct in the formula bar after I changed the scipt to include your suggestions which then allowed me to change the .FORMAT(XXXXXXiijij), to include "dd/mm/yyyy"

Heres the link

https://www.youtube.com/watch?v=rZScXs8tfFM

Also regarding the last version I posted so you can see it working with your suggestions- still not sure where you are having the issues because of the Arabic ? the words in the script are either MSG messages which are not related - eg are you sure you want to delete? etc or lists of places or communities only which again are not critically part of any vba script and theres no english translation - if you are seeing  just strange characters or ????????? then please change the sub regional unicode setting to Arabic UAE - it doesnt affect anything else on your PC - please see my previous posting or also you may need to change the VBA language under the TOOLs tab to new courier ( Arabic) 

The above notwithstanding Ill just run through a few more examples to see if everything is running smoothly

Thank you so much!

Adrian

Hi again Adrian,

My apologies for the delay in answering but I have been quite busy with other stuff.

I am not able to use your example workbook. Too many problems with the Arabic language.

Your comment: formatting and shows times over 24 hours when the data comes back to the form - even if I try and add a format to the 'Edit' script the format is lost an the value if over 24 hours shows zero in this format 00:00

Edit the following line and use the Worksheet Text Function to convert in lieu of the VBA Format function as per my previous advice in my example workbook.

From Private Sub cmdedit_Click()

Me.txtfirefighting.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 3), "'hh:mm'")

Replace above line with following line.

 Me.txtfirefighting = WorksheetFunction.Text(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 3), "[hh]:mm")

See the following code examples between the asterisk lines (in two places in the code) for saving a TextBox d/m/y date to the worksheet. Code example is extracted from your Sub Submit and only one TextBox edited to provide an example of converting a text box value in d/m/y format to a date value on the worksheet.

I don't know why your dates show as m/d/y in your formula bar. On my computer, irrespective of the date NumberFormatting in the cells, the date in my formula bar still displays in d/m/y.

Sub Submit()

    Dim sh As Worksheet

    Dim irow As Long

    '***************************************************************************************************

    Dim arrSplit As Variant     'Add this line to dimension the array variable for the Split function

    Dim dteFinishDate As Date   'Add this line to dimension the Date Variable

    '***************************************************************************************************

    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, 4) = frmincident.txtfirefighting.Value

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

 '********************************************************************************************************************

        .Cells(irow, 6) = frmincident.txtdatefinished.Value 'Replace Date lines like this with code like the following.

        'Split the text finish date at the slashes and assign each value (Day, Month, Year) to a zero base array

        arrSplit = Split(frmincident.txtdatefinished, "/")

        'Assign Finish Date to a date variable using DateSerial function and the array elements

        dteFinishDate = DateSerial(arrSplit(2), arrSplit(1), arrSplit(0))

        'Assign the Date Variable to the cell on the worksheet.

        .Cells(irow, 6) = dteFinishDate

        'If column on the worksheet is preformatted with NumberFormat "dd/mm/yyyy" then following line should not be required

        .Cells(irow, 6).NumberFormat = "dd/mm/yyyy"

'***********************************************************************************************************************

As a little extra interesting information, irrespective of the regional date format, VBA code still appears to use m/d/y format for dates. Test the following example for hard coding a date into VBA code and assigning to a date variable. Note the date is entered in d/m/y format and observe what occurs with the date immediately after entering it into the VBA editor.

Sub TestDates()

    Dim dteExample As Date

    dteExample = #15/4/2020#

End Sub

Hi Adrian,

Just a progress (or no progress) report.

Haven't had time to do much on this today but hopefully I will have more time tomorrow.