Excel VBA Userform Date issue

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.

latest version to review

Hi Ozzie

Well I tried your options as best as I could and some it works when saving to the worksheet from the userform- the list box also mirrors the correct input

However while the [ hh] works in the cell 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 

ps the firefighting time is from 'Arrival time' to finished date and time - not from the call time

Also Im still having to swap the dd/mm/yyyy to mm/dd/yyyy to get that to show correctly on the user form ( in edit) and worksheet as dd/mm/yyyy - the formular bar continues to show mm/dd/yyyy but maybe Ill just have to live with that as i just cant work out what to do when the user form date data is passed back to the spreadsheet

Ive added my latest attempt as above and changed the labels to english so you can see - I cant really re translate all the other stuff and to be honest its not that much relevant to the issue - Ive added a line in the list box though so you can see both arabic and english

If your not seeing the arabic text you will need to change the unicode to arabic uae - which is under administrator tab in the regional time date settings and doesnt affect your other settings - dont tick the beta unicode box if its there though or it will not work - sorry if you already know this 

1/from window key- type region, choose admin tab, under language for non unicode - select change system locale- select arabic uae - restart 

can you please just run your eye over everthing in the module and user form scripts to check if theres anything ive done wrong 

Thank you 

Adrian

I need to be able to identify the date and Time TextBoxes so I know which TextBoxes to enter the data for testing purposes. Possibly I also need to be able to read the ListBox but not sure.

Hi Ozzie

In process doing exacting what you suggest

regarding an english version - I dont have that as only the text labels and drop downs - which arnt really relevant areand the msg box content are in arabic - the spread sheet is in both - which bit do you need in english?