Excel VBA Userform Date issue

Adrian,

I don't know how I can help you there.

I thought I made it clear and showed you the errors that are already in your code and in your database. I showed you how you can use and evaluate a real date instead of a text.

You have declined this and want to use text and the solution that Ossie suggested. That's ok for me.

But, IMHO, this solution will never work for this purposes. So I can't give you any suggestions, I'm sorry.

Andreas.

Lastly can I create a list on a user form which adds items each a new item is added from a combobox list without creating a complete new record every time a new item is added- thinking of a way to explain

• it would be like shopping online where your online shopping basket acts like a list box and then copies all those items when your finished onto the worksheet when you press SAVE

That is a complex problem, but solvable.

You need to create a database in memory, I suggest to use a Scripting.Dictionary as container. (You can also use an array or collection or temporary sheet, but IMHO that is much more complicated).

Inside that container, each record has to be an array, for your case you can use a simple one dimensional array, because that can be written very easy into a row in a sheet.

All your routines inside the form must use this array (record) instead of cells to store the values.

If you're done with the input in the form, simply add the record to the container. Update the Listbox with all keys that are currently inside the container.

Finally if you want to save the data to the sheet, you have to get all data from the container and write each array into a row.

I am not sure if this guide will help you, in any case it means that you have to discard your previous code.

Andreas.

Hi Andreas

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

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

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