Listbox to show dd/mm/yyyy & not mm/dd/yyyy

[ipbr21054]

I recently changed a value in the settings to show proper dates as opposed text dates.

Now after a fews days & using my other forms i see two which show dates like 9/24/2024 & so i need it to correctly show 24/09/2024

Below are the two Userform_Initialize codes in question but im unable to get the date shown correctly when usng the following code attempt.

ListBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")

FORM 1

Private Sub UserForm_Initialize()

Dim fndRng As Range
Dim firstAddress As String
Dim cnt As Long
Dim elapsedDays As Long

With Me.ListBox1
.ColumnCount = 9
.ColumnWidths = "220;170;110;170;120;30;130;0"
End With

With Sheets("POSTAGE").Range("G:G")
Set fndRng = .Find(What:="DELIVERED NO SIG", LookIn:=xlValues, LookAt:=xlWhole, _
SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False)
If Not fndRng Is Nothing Then
firstAddress = fndRng.Address
Do ' check the date
elapsedDays = Date - DateValue(fndRng.Offset(, -6))
If elapsedDays <= 80 And elapsedDays >= 30 Then

cnt = cnt + 1
With Me.ListBox1 ' ADD VALUES TO LISTBOX
.AddItem fndRng.Offset(, -5).Value 'CUSTOMER'S NAME
.List(.ListCount - 1, 1) = fndRng.Offset(, -4).Value 'ITEM
.List(.ListCount - 1, 2) = fndRng.Offset(, -6).Value 'DATE
.List(.ListCount - 1, 3) = fndRng.Offset(, -2).Value 'TRACKING NUMBER
.List(.ListCount - 1, 4) = fndRng.Offset(, 5).Value 'CLAIM
.List(.ListCount - 1, 6) = fndRng.Value 'RECEIVED NO SIG
.List(.ListCount - 1, 8) = fndRng.Row
End With
End If
Set fndRng = .FindNext(fndRng)
Loop While Not fndRng Is Nothing And fndRng.Address <> firstAddress
End If
End With

If cnt = 0 Then
MsgBox "THERE ARE " & cnt & " RECORDS FOR WITHIN THE LAST 80 DAYS", vbInformation, "DELIVERED BUT NO SIGNATURE MESSAGE"
End
End If
Me.StartUpPosition = 0
Me.Top = Application.Top + 100 ' MARGIN FROM TOP OF SCREEN
Me.Left = Application.Left + Application.Width - Me.Width - 70 ' LEFT / RIGHT OF SCREEN
End Sub

FORM2

Private Sub UserForm_Initialize()

Application.ScreenUpdating = False

On Error GoTo End_here

Call add_val("LOST")
Call add_val("DELIVERED NO SIG")
Call add_val("RETURNED")
Call add_val("UNKNOWN")

End_here: Application.ScreenUpdating = True
End Sub

 

[Jack George]

May I make a suggestion here?

Use a worksheet as the backend for ListBox control.

Doing this would provide you with the following benefits:

• You can have headers in ListBox naturally, by using the RowSource property of ListBox.
• You can sort column(s) of a ListBox real fast by using the sorting functionality of worksheet.
• You can filter a ListBox (for lookups in a column, for example.)
• You can autofit the columns of a multi-column ListBox.

I want to emphasis the last point here. The ListBox column width is using a unit of measurement exactly the same as a worksheet column, that is, measured by the width of a single character. And, with a maximum of 250 characters of course.

For a quick "How-To", you can read this:Automatically adapt listbox column width

For your question of this post, I'm not sure if by using RowSource property of ListBox can bring the worksheet date format of a cell into ListBox. You may try it and let us know.

 

[Alex Blakenburg]

I am little confused. The 4 things you tried all refer to .List(i, 2)
but the code you are showing refers to .List(i, 3)

I would expect this line to work. You have 2 date lines so you will need to change both of them.

              .List(i, 3) = Format(f.Offset(, -6).Value, "dd/mm/yyyy") 'DATE

If that still doesn't work put the cursor in the date cell for John Williams and show us a screen shot that has both the Cell and the Formula bar showing.

 

[ipbr21054]

Sorry i copied it from a post.
I did change it to a 3

What you advised worked.

 

[Alex Blakenburg]

It's the same code as suggested by @abdelfattah in his first option just adapted to the code you provided in post 10.
But glad you've got it working now.