Excel VBA Userform Date issue

Question

I am creating an excel database populated by a userform - the userform can also be re-populated with the data from the spreadsheet for later editing, deletion printing etc.Te issue is that the textbox populates the relevant cells in the correct dd/mm/yyyy UK regional format - however if the data contains a leading xero eg-02/08/2020 the value on the user form is returned as a numerical string - any other dates with a leading number of 1,2 or 3 such 21/09/2020 works fine The spreadsheet cells are all formatted correctly with a data format as above - Ive tried everything I can find and think of - can you advice pleaseABrown

Answer 1

Without a copy of your workbook I can't be absolutely sure but I think the code should be something like the following when assiging the time and dates to a TextBox.

'For Time test each of the following

Me.txttime.Value = Format(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16), "hh:mm:ss")

Me.txttime.Value = Format(TimeValue(Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)), "hh:mm:ss")

'For Date Test each of the following

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

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

It might even work if you reference the TextBoxes for time and date as follows without using .Value

Me.txttime = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)

Me.txtdate = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17)

If you can't get it to work can you upload a copy of your workbook to OneDrive. If you have sensitive data then make a copy of the workbook and replace the sensitive data with dummy data. I sometimes use the following link to generate a random list of dummy names for test workbooks.

http://listofrandomnames.com/

Guidelines to upload a workbook on OneDrive. (If you already use OneDrive and your process for saving to it is different then you can probably start at step 8 to get the link but please zip the file before uploading.)

1. Zip your workbooks. Do not just save an unzipped workbook to OneDrive because the workbooks open with On-Line Excel and the limited functionality with the On-Line version causes problems.
2. To Zip a file: In Windows Explorer Right click on the selected file and select Send to -> Compressed (zipped) folder). By holding the Ctrl key and left click once on each file, you can select multiple workbooks before right clicking over one of the selections to send to a compressed file and they will all be included into the one Zip file.
3. Do not use 3rd party compression applications because I cannot unzip them. I do not clog up my computer with 3rd party apps when there are perfectly good apps supplied with windows.
4. Go to this link.  https://onedrive.live.com
5. Use the same login Id and Password that you use for this forum.
6. Select Upload under the blue bar across the top and browse to the zipped folder to be uploaded.
7. Select Open (or just double click). (Be patient and give it time to display the file after initially seeing the popup indicating it is done.)
8. Right click the file name in OneDrive.
9. Select Share.
10. Click the link icon (Looks like chain links) at the bottom left of the dialog (Just above "Copy link").
11. Click Copy button.
12. Change back to this forum and click the "Insert Hyperlink" icon at top of the posting editor (Icon looks like chain links).
13. Right click in the Web address field and right click and paste (or just Ctrl V to paste).
14. Click "Insert" Button.

Answer 2

hi ossie

it worked with this one

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

it was still changing the d and m around 'usa' style so 09/08/2020 was 08/09/2020 but I changed the format to mm/dd/yyyy and it seems to work is that correct?

One last thing though please - in the worksheet there is a calculation in cell/column D which subtracts the incident arrival time from the completion time - thats fine except if the arrival time is 23:45 and the completion time is after midnight so it would be actually in the followiing days 24 hour period - im obvious getting an error/no time - any thoughts how I can do that in vba rather than on the sheet ?

I guess im going to need an additional txtbox on the user form to confirm the date of completion but then I guess Ill need to concatenate the first day of the incident and the time of arrival and then subtract the data and time from the first value - ive no idea what either the excel code or the vba would look like for that

Thank you Again

Answer 3

I wonder what code you are using to populate the relevant TextBox or Worksheet Cell.

The below code example loops through dates in column A and inserts the date in the correct Regional format in the TextBox and then copies the TextBox date back to the to the adjacent column B cell on the worksheet and sets the number format for column B. (You can pre set the number format for the entire column B so that the number format is not required for each loop).

You may not need to do this but ensure that you have the correct Regional Settings for the dates in windows 10. See the following link for more information on how to do this.

https://support.office.com/en-us/article/change-the-windows-regional-settings-to-modify-the-appearance-of-some-data-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

Private Sub CommandButton1_Click()

    Dim r As Long

    For r = 2 To 32

        'Populate a Userform TextBox from a date cell on worksheet

        Me.TextBox1 = Format(Worksheets("Sheet1").Cells(r, "A"), "dd/mm/yyyy")

        'Populate a worksheet Date Cell from a Userform TextBox date

        Worksheets("Sheet1").Cells(r, "B") = DateValue(Me.TextBox1.Value)

        Worksheets("Sheet1").Cells(r, "B").NumberFormat = "dd/mm/yyyy"

    Next r

End Sub

Answer 4

Hi Ossie

Thank you for that fast response !!

Ill give your suggestions a go and I'll forward the file - theres nothing sensitive in there - its all dummy test data Ive been adding randomly

However the text is in arabic !!! in some areas so you need to select times roman (arabic) from the option in vba tools menu first before opening the file - the form is intuitive so doesnt matter what you select from the drop downs but please use the tab key to move through the sheet

the last entry in the worksheet is now playing up - i entered 12/09/2020 and the form on saving changed it to 09/12/2020

cheers

Adrian

Answer 5

Hi Ossie

Thanks for that

I guess I didnt explain to well

so on my user form  frmincident - data is inputted as per the form questions - the first question is incident data - which is in a normal text box - the user in puts the data as dd/mm/yyyy - on completion of the entries the user presses save and the data is transferred on to the worksheet - thats all fine - im using this vba

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

That works and the Date format is correct at that point

Then also within a list box on the user form the inputted data is also visualised as per

      .LstDatabase.ColumnCount = 21

.LstDatabase.ColumnHeads = True

.LstDatabase.ColumnWidths = "20,80,120,92,100,90,95,120,90,110,120,90,85,90,90,70,60,70,110,80,80,80,80"

If irow > 1 Then

.LstDatabase.RowSource = "Database!A2:T" & irow

Else

.LstDatabase.RowSource = "Database!A2:T2"

End If

End With

End Sub

Now this is when the issue occurs - if you select any of the entries from the list box for editing by selecting the list box entry and pressing a cmd button 

and this runs

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

Me.txtcomment.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 1)

Me.cmbdelay.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 2)

Me.txtfinished.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 4)

Me.txtarrival.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 6)

Me.txtturnout.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 8)

Me.txtdispatch.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 10)

Me.cmbproperty.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 11)

Me.cmbincident.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 12)

Me.cmbcommunity.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 13)

Me.cmbstation.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 14)

Me.cmbcall.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 15)

Me.txttime.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 16)

Me.txtdate.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 17)

Me.txtautomax.Value = Me.LstDatabase.List(Me.LstDatabase.ListIndex, 18)

MsgBox "Please make the required changes and click 'Save' button to update.", vbOKOnly + vbInformation, "Edit"

End Sub

so this is when the problem occurrs so if there is a date such 06/12/2020 the entry ti the txtdate box comes back as number eg 09/01/2020 now shows as 43839

Can you offer any further guidance please ?

I have a further problem which is also similar with time - if the excel works sheet is not formatted as a text cell the value returns - as a decimal  - so if the cells are formated as 'TIME' hh:mm it will not return a time format from the user form list box into the relevant txtboxes

Thank you