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

JonnyAngelo

New Member
Joined
Dec 11, 2017
Messages
35
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:

Q2fIkwu
Q2fIkwum.png


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.
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Something like this?

Code:
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:

Code:
Private Sub ListBox1_Click()

Range("C1") = CDate(ListBox1.Value)
    
End Sub
 
Upvote 0
Hey Steve, Quick question,

As i'll be adding more entries to the worksheetsheet, would
this code still format the new entries i add?
 
Upvote 0
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:

Code:
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
 
Upvote 0
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?
 
Last edited:
Upvote 0
Put this line in at the start. It was me being lazy.

Code:
Dim sh As Worksheet, lr As Long, c As Range
 
Upvote 0
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?
 
Upvote 0
How are you currently populating the listbox?

Which columns in the listbox need formatting?
 
Upvote 0
Here's what i use to populate my box.
It has 20 columns. Columns A and S need formatting:

Code:
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
 
Last edited:
Upvote 0

Forum statistics

Threads
1,224,820
Messages
6,181,162
Members
453,021
Latest member
Justyna P

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
Back
Top