lifeson
12-10-2007, 07:11 AM
How do I format the contents of a list box
I have the following to poulate the listbox:
Private Sub LoadDocs()
Dim c As String ' column to use
Dim ilrow As Long
Dim aryDocs
Dim i As Long
Dim docFile As String
Dim docType As String
Dim docSize As String
Dim docDate As String
'which sheet to use
Sheets("BlrDoc").Select
ilrow = Cells(Rows.count, "A").End(xlUp).row
With lstDocs
ReDim aryDocs(1 To ilrow)
For i = 2 To ilrow
If Cells(i, "A").Value = sPrem Then
.AddItem Cells(i, "B").Value 'doc id
On Error Resume Next
docFile = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:B"), 2, False)
docType = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 3, False)
docDate = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 4, False)
docSize = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 5, False)
.List(.ListCount - 1, 1) = docType
.List(.ListCount - 1, 2) = docFile
.List(.ListCount - 1, 3) = docDate
.List(.ListCount - 1, 4) = docSize
End If
Next i
End With
End Sub
but i want
.List(.ListCount - 1, 3) = docDate
To display date in UK format dd/mm/yy
and
.List(.ListCount - 1, 4) = docSize
to display number to 2 signifcant places
I have the following to poulate the listbox:
Private Sub LoadDocs()
Dim c As String ' column to use
Dim ilrow As Long
Dim aryDocs
Dim i As Long
Dim docFile As String
Dim docType As String
Dim docSize As String
Dim docDate As String
'which sheet to use
Sheets("BlrDoc").Select
ilrow = Cells(Rows.count, "A").End(xlUp).row
With lstDocs
ReDim aryDocs(1 To ilrow)
For i = 2 To ilrow
If Cells(i, "A").Value = sPrem Then
.AddItem Cells(i, "B").Value 'doc id
On Error Resume Next
docFile = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:B"), 2, False)
docType = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 3, False)
docDate = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 4, False)
docSize = Application.VLookup(Cells(i, "B").Value, Worksheets("docs").Columns("A:E"), 5, False)
.List(.ListCount - 1, 1) = docType
.List(.ListCount - 1, 2) = docFile
.List(.ListCount - 1, 3) = docDate
.List(.ListCount - 1, 4) = docSize
End If
Next i
End With
End Sub
but i want
.List(.ListCount - 1, 3) = docDate
To display date in UK format dd/mm/yy
and
.List(.ListCount - 1, 4) = docSize
to display number to 2 signifcant places