Change date format within a column(s) of a listbox

[JonnyAngelo]

Hello,

So I've been at work on a userform to brush up my skills on vba and i've
managed to create a userform with a listbox that displays data from a
spreadsheet and gets filtered by some combo-boxes when options are selected.
Everything runs well but the only issue i'm having is setting a date format in
two of the columns. Dates show up like this:

The image above shows the first column with the dates.
The format in which i want the date to be in two columns is (dd/mm/yyyy).
Is there an easy code that i can add into my UserForm_Initialize() sub
to make this possible for both columns? The columns that need the format
are "A" and "S" and the dates start from row 3 from my "Landlord" worksheet.
Feel free to share ideas and to ask for more information.

Thanks, Jonny.

 

[JonnyAngelo]

i want them formatted as dd/mm/yyyy even with 0 figures shown for example:

the first of january 2018 would be = 01/01/2018, not 1/1/2018

 

[Norie]

Try this.

Dim arrList As Variant
Dim I As Long

    ' other code

    tempSheet.Cells.Clear
 
    filteredRange.Copy Destination:=tempSheet.Range("A1")
    arrList = tempSheet.Range("A1").CurrentRegion.Resize(, 20).Value
    
    For I = LBound(arrList) To UBound(arrList)
        arrList(I, 1) = Format(arrList(I, 1), "mm/dd/yyyy")
        arrList(I, 19) = Format(arrList(I, 19), "mm/dd/yyyy")
    Next I
    
    .List = arrList

 

[JonnyAngelo]

Hey Norie,

Tried running your code just but an error came up saying:
"invalid or unqualified reference" and highlights ".List ="

Is there something i'm doing wrong?

 

[Norie]

Where did you put the code I suggested?

It should basically replace this in the code from post #9 .

 tempSheet.Cells.Clear
    filteredRange.Copy Destination:=tempSheet.Range("A1")
    .List = tempSheet.Range("A1").CurrentRegion.Resize(, 20).Value

 

[JonnyAngelo]

Ahh, thank you for the reply. Helped me figure out the problem. Ran this final code which works perfectly:

Private Sub PopulateListBox()


Dim thisRow As Long
Dim filteredRange As Range
Dim arrList As Variant
Dim I As Long


On Error Resume Next


With Me.lstData
    'Determine number of columns
    .ColumnCount = landlordData.Columns.Count
    'Set column widths
    .ColumnWidths = "50;50;60;60;100;60;75;40;40;40;50;40;50;50;65;20;75;20;50;40;"
    'Insert the range of data supplied
    .Clear
    For thisRow = 2 To landlordData.Rows.Count
        If landlordData.Rows(thisRow).Hidden = False Then
            If filteredRange Is Nothing Then
                Set filteredRange = landlordData.Rows(thisRow)
            Else
                Set filteredRange = Application.Union(filteredRange, landlordData.Rows(thisRow))
            End If
        End If
    Next thisRow


    tempSheet.Cells.Clear
 
    filteredRange.Copy Destination:=tempSheet.Range("A1")
    arrList = tempSheet.Range("A1").CurrentRegion.Resize(, 20).Value
    
    For I = LBound(arrList) To UBound(arrList)
        arrList(I, 1) = Format(arrList(I, 1), "mm/dd/yyyy")
        arrList(I, 19) = Format(arrList(I, 19), "mm/dd/yyyy")
    Next I
    
    .List = arrList


End With


End Sub

Thanks again for the help!

 

[willsnake]

Good day,

May I ask if how can I insert this code

Dim arrList As VariantDim I As Long

    ' other code

    tempSheet.Cells.Clear
 
    filteredRange.Copy Destination:=tempSheet.Range("A1")
    arrList = tempSheet.Range("A1").CurrentRegion.Resize(, 20).Value
    
    For I = LBound(arrList) To UBound(arrList)
        arrList(I, 1) = Format(arrList(I, 1), "mm/dd/yyyy")
        arrList(I, 19) = Format(arrList(I, 19), "mm/dd/yyyy")
    Next I
         .List = arrList

to my code?

Private Sub fPRNumber_AfterUpdate()
Dim ws As Worksheet
Dim x, dict
Dim i As Long
Dim j As Long
Dim wht As String
Dim arrList As Variant
wht = Worksheets("PR").Range("c11").Value
Set ws = Sheets("PRData")
x = ws.Range("A1").CurrentRegion.Value
Set dict = CreateObject("Scripting.Dictionary")
ListBox1.Value = ""
ListBox1.ColumnCount = 4
ListBox1.ColumnWidths = "20;300;40;20"
For i = 2 To UBound(x, 1)
    If x(i, 2) = wht Then
        dict.Item(x(i, 6)) = Array(x(i, 5), x(i, 6), x(i, 7), x(i, 8))
    End If
Next i
ListBox1.List = Application.Index(dict.Items, 0, 0)
On Error Resume Next
UserForm3.fName.Value = Application.WorksheetFunction.VLookup(Worksheets("PR").Range("c11").Value, Worksheets("PRData").Range("B:P"), 2, False)
UserForm3.fPurpose.Value = Application.WorksheetFunction.VLookup(Worksheets("PR").Range("c11").Value, Worksheets("PRData").Range("B:P"), 3, False)
If UserForm2.fName.Value = "" Then
UserForm2.fPRNumber.Value = ""
UserForm2.fPRNumber.SetFocus
End If
End Sub

column 7 & 8 will be formatted as "#,##0.00"

Thank you...

 

[willsnake]

Found the solution

        dict.Item(x(i, 6)) = Array(x(i, 5), x(i, 6), Format(x(i, 7), "#,##0.00"), Format(x(i, 8), "#,##0.00"))