Date formatting in a multi column listbox

[Nigel Brown]

I am struggling to find a method to display dates correctly in a
listbox. I populate the listbox with the following code.

Private Sub testlist()
Dim rng As Range
Dim cw
Dim colcnt As Integer
Dim rowcnt As Integer

colcnt = ActiveSheet.UsedRange.Columns.Count
rowcnt = ActiveSheet.UsedRange.Rows.Count
str = "a2:g" & rowcnt
Set rng = Range(str)
With ListBox1
.ColumnCount = colcnt
.ColumnHeads = True
.RowSource = rng.Address
cw = ""
For i = 1 To .ColumnCount
cw = cw & rng.Columns(i).Width & ";"
Next
.ColumnWidths = cw
.ListIndex = 0
End With
End Sub

The problem is that column g on the worksheet contains dates and these
are being displayed as values in the listbox. The format I want to
used is "DD/MM/YYYY". Any help will be greatly appriciated.

Nigel Brown
Theisen Securities.

 

[Dick Kusleika]

Nigel

When you use .RowSource to populate the list box, you don't have a lot of
control over how it looks. You can use the .AddItem method to populate the
lb, but I don't know how to get column heads in there when you do. Here's
an example of how to populate with .AddItem, and formatting dates as you go.

Sub FillLb()

Dim Rng As Range
Dim Cell As Range
Dim i As Long
Dim NewText As String

Set Rng = Sheet1.Range("A1:B10")

With Sheet1.ListBox1
.ColumnCount = Rng.Columns.Count
.ColumnHeads = True

For Each Cell In Rng.Columns(1).Cells
If IsDate(Cell.Value) Then
NewText = Format(Cell.Value, "DD/MM/YYYY")
Else
NewText = Cell.Value
End If

.AddItem NewText

For i = 1 To Rng.Columns.Count - 1
If IsDate(Cell.Offset(0, i).Value) Then
NewText = Format(Cell.Offset(0, i).Value, "DD/MM/YYYY")
Else
NewText = Cell.Offset(0, i).Value
End If

.List(.ListCount - 1, i) = NewText
Next i
Next Cell
End With

End Sub

 

[Nigel Brown]

Dick,
Thanks for your reply. Column headers are taken from the row above the
selected range. So in my case they are in row 1 and I begin my data
selection in row2.

Nigel
Theisen Securities