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.

 

[steve the fish]

Something like this?

Private Sub UserForm_Initialize()

For Each c In Range("A1:A10")
    ListBox1.AddItem Format(c, "DD/MM/YYYY")
Next

End Sub

Bear in mind when you come to use the dates VBA will see them as american dates so use CDate. Heres an example:

Private Sub ListBox1_Click()

Range("C1") = CDate(ListBox1.Value)
    
End Sub

 

[JonnyAngelo]

Hey Steve, Quick question,

As i'll be adding more entries to the worksheetsheet, would
this code still format the new entries i add?

 

[steve the fish]

What i gave was an example to show you how you could get it to work. So no this isnt going to do that. You will need to use something more like the below:

Private Sub UserForm_Initialize()

Set sh = Sheets("Sheet1")
With sh
    lr = .Range("A" & Rows.Count).End(xlUp).Row
    For Each c In .Range("A1:A" & lr)
        ListBox1.AddItem Format(c, "DD/MM/YYYY")
    Next
End With

End Sub

 

[JonnyAngelo]

When I test your code with some changes such as sheet name, it comes up with
an error saying "variable not defined". It highlights "sh =".

Any thoughts?

 

[steve the fish]

Put this line in at the start. It was me being lazy.

Dim sh As Worksheet, lr As Long, c As Range

 

[JonnyAngelo]

The code runs without any issues however it still
hasn't fixed my problem, for example, the date still
shows 01/03/2018 as 1/3/2018.
Apologies for being picky, any fixes for this?

 

[Norie]

How are you currently populating the listbox?

Which columns in the listbox need formatting?

 

[JonnyAngelo]

Here's what i use to populate my box.
It has 20 columns. Columns A and S need formatting:

Private Sub PopulateListBox()

Dim thisRow As Long
Dim filteredRange As Range

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")
    .List = tempSheet.Range("A1").CurrentRegion.Resize(, 20).Value
End With

End Sub

 

[Norie]

How do you want the dates formatted?