Listbox Formating

[Guest]

I am populating a listbox with .additem and the columns with .list from cells
in a worksheet. Population works fine however the columns in the listbox do
not display then currency values correctly.

Spreadsheet Information:

Filename, Record Count, Dollar Amount
Test0.csv, 300, $20,000.00
Test1.csv, 250, $18,597.18

Listbox Information:

Filename,Record Count, Dollar Amount
Test0.csv, 300, 20000
Test1.csv, 250, 18597.18

I would like to keep the formatting as the listbox is used in a userform to
report the final information on files that have been processed.

Below is a copy of the code used to populate the listbox:

For Fnum = LBound(TxtFileNames) To UBound(TxtFileNames) + 1
lst_Report.AddItem Cells(Fnum, "A")
For Cnum = 2 To 5
With lst_Report
.List(.ListCount - 1, Cnum - 1) = Cells(Fnum, Cnum)
End With
Next Cnum
Next Fnum

Thanks for your help

 

[Dave Peterson]

If the cells are formatted nicely, you could use:

For Fnum = LBound(TxtFileNames) To UBound(TxtFileNames) + 1
lst_Report.AddItem Cells(Fnum, "A").Text
For Cnum = 2 To 5
With lst_Report
.List(.ListCount - 1, Cnum - 1) = Cells(Fnum, Cnum).Text
End With
Next Cnum
Next Fnum

(.text added a couple of times)

If the cells aren't formatted nicely, you'd have to format each the way you
want:

For Fnum = LBound(TxtFileNames) To UBound(TxtFileNames) + 1
lst_Report.AddItem Cells(Fnum, "A").Text
With lst_Report
.List(.ListCount - 1, 2) _
= format(Cells(Fnum, 2).value, "#,##0")
.List(.ListCount - 1, 3) _
= format(Cells(Fnum, 3).value, "$#0.00")
'and so forth
End With
next fnum

Next Fnum