Format date in Listbox

[ipbr21054]

Evening,
Im getting confused a touch if you could avise please.

My worksheet shows the correct date in the cells, 17/12/2020
When i open up my userform the Listbox shows 12/17/2020 for the cell in question.

Below is the code in use but cant get the format part correct.

    Private Sub TextBox8_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)
    If KeyCode = vbKeyReturn Then
      Dim r As Range, f As Range, cell As String, added As Boolean
      Dim sh As Worksheet
      
      Set sh = Sheets("POSTAGE")
      sh.Select
      With ListBox1
        .Clear
        .ColumnCount = 4
        .ColumnWidths = "220;110;130;10"
        If TextBox8.Value = "" Then Exit Sub
        Set r = Range("B8", Range("B" & Rows.Count).End(xlUp))
        Set f = r.Find(TextBox8.Value, LookIn:=xlValues, LookAt:=xlPart)
        If Not f Is Nothing Then
          cell = f.Address
          Do
            added = False
            For i = 0 To .ListCount - 1
              Select Case StrComp(.List(i), f.Value, vbTextCompare)
                Case 0, 1
                  .AddItem f.Value, i                 'col B
                  .List(i, 1) = f.Offset(, 2).Value   'col D
                  .List(i, 2) = f.Offset(, 5).Value = Format "dd-mm-yyyy"  'col G
                  .List(i, 3) = f.Row                 'row
                  added = True
                  Exit For
              End Select
            Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Value  'col G
              .List(.ListCount - 1, 3) = f.Row                 'row
            End If
            Set f = r.FindNext(f)
          Loop While Not f Is Nothing And f.Address <> cell
          TextBox8 = UCase(TextBox8)
          .TopIndex = 0
        Else
          MsgBox "NO CUSTOMER WAS FOUND USING THAT INFORMATION", vbCritical, "POSTAGE SHEET CUSTOMER NAME SEARCH"
          TextBox8.Value = ""
          TextBox8.SetFocus
        End If
      End With
      End If
    End Sub

 

[dmt32]

Hi,

change this line

.List(i, 2) = f.Offset(, 5).Value = Format "dd-mm-yyyy"  'col G

to this

.List(i, 2) = f.Offset(, 5).Text

and see if solves your issue

Dave

 

[ipbr21054]

Hi,
Its the same.

 

[dmt32]

Hi,
Its the same.

only glance at that part of your code & did not note it repeated

You need to change this line as well

Next
            If added = False Then
              .AddItem f.Value                                 'col B
              .List(.ListCount - 1, 1) = f.Offset(, 2).Value   'col D
              .List(.ListCount - 1, 2) = f.Offset(, 5).Text 'col G

Hopefully, will resolve

Dave

 

[ipbr21054]

Perfect thanks very much.