format date list box

**waltron** · 08-16-2009, 09:17 PM

how do i format a list box to read SAT-25-MAR-09 instead of the defult 3/25/09 the list box has 14 columns, the second column is the column with the date in it. below is the code im using to poulate the list
the date is in column b on sheet.

Sub LoadlistBox()
    
    Dim lbtarget As MSForms.ListBox
    Dim rngsource As Range
    

    Set rngsource = Worksheets("Scedtest").Range("A2:N50")
    'Fill the listbox
    Set lbtarget = Me.ListBox1
    
    With lbtarget
        .ColumnCount = 14
        .ColumnWidths = "60;80;130;60;30;100;150;250;1;1;1;1;1;1"
        .List = rngsource.Cells.Value
    End With

**Andy Pope** · 08-17-2009, 03:58 AM

You need to format the date and update the list entry
Assumes date is in column B of range.

    Dim lngIndex As Long
    
    With Me.ListBox1
        .ColumnCount = 14
        .ColumnWidths = "60;80;130;60;30;100;150;250;1;1;1;1;1;1"
        .List = Range("A2:N12").Value
        For lngIndex = 0 To .ListCount - 1
            .List(lngIndex, 1) = UCase(Format(CDate(.List(lngIndex, 1)), "ddd-dd-mmm-yy"))
        Next
    End With