Format date in Listbox

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,915
Office Version
  1. 2007
Platform
  1. Windows
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.

Rich (BB code):
    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
 

Some videos you may like

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,490
Office Version
  1. 2019
Platform
  1. Windows
Hi,

change this line

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

to this

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

and see if solves your issue

Dave
 

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
6,490
Office Version
  1. 2019
Platform
  1. Windows
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

Rich (BB code):
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
 
Solution

Watch MrExcel Video

Forum statistics

Threads
1,128,086
Messages
5,628,591
Members
416,326
Latest member
NinaChristal

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top