Format a Multiple Range Listbox with Currency Format

Question

I am trying to format the second column of my listbox with currency format ("$#,##0.00"), but running into some trouble. Any help with be greatly appreciated!

Here is some test Data:

Dim ws1 As Worksheet
Set ws1 = ThisWorkbook.Sheets("PivotTable")

Dim rng1 As Range
Dim LR1 As Long
LR1 = Range("A" & Rows.Count).End(xlUp).Row
Set rng1 = ws1.Range("A1:A" & LR1).SpecialCells(xlCellTypeVisible)

With Me.ListBox1
    .ColumnCount = 2
    .ColumnWidths = "120,100"

    For Each Cell In rng1
        .AddItem Format(Cell.Value, "$#,##0.00")
        .List(.ListCount - 1, 1) = Cell.Offset(0, 1).Value
        .List(.ListCount - 1, 2) = Cell.Offset(0, 2).Value 'Format this column        
    Next Cell
End With

This is the result I am getting now:

What's the issue? What's your question? Why don't you use the `Format(…)` function as you did at `.AddItem`? — Pᴇʜ, Jun 17 '19 at 13:33
I tried that earlier and had no luck with it: .AddItem Format(Cell.Value, "$#,##0.00") .List(.ListCount - 1, 1) = Cell.Offset(0, 1).Value .List(.ListCount - 1, 2) = Format(Cell.Offset(0, 2).Value, "$#,##0.00") — Andrew, Jun 17 '19 at 13:40
My issues/question is that I have a range of number in my second column of my textbox that I want to display in currency format — Andrew, Jun 17 '19 at 13:41
Well `.List(.ListCount - 1, 2) = Format(Cell.Offset(0, 2).Value, "$#,##0.00") ` should work if `Cell.Offset(0, 2).Value` contains a numeric value. What is the data in that cell. Also note that *"had no luck with it"* is no useful error description to help you. — Pᴇʜ, Jun 17 '19 at 13:43
I tried implementing your code and it did not work and they do contain numeric values. Do you think it could be something in my code that is over righting the format? Also sorry about the "No luck" still new to forumns. — Andrew, Jun 17 '19 at 13:49
`Format` only works on numeric values not on `Strings`. You cannot format strings you need to cut them into pieces to get the numeric values which you can format then. — Pᴇʜ, Jun 17 '19 at 13:50
How would I do that? I figured that is what the problem was but could not find a way to covert a filtered range of numbers from string to numberic. — Andrew, Jun 17 '19 at 13:54
Please provide a full example (including data) otherwise we cannot help you. See [mcve]. Add them to your original question. — Pᴇʜ, Jun 17 '19 at 13:55
I just changed the original question, is that what you were looking for? I tried to attach a test workbook but could not. — Andrew, Jun 17 '19 at 14:37
Actually the question is where do the `$4,575.00` come from and why are they text and not numbers? — Pᴇʜ, Jun 17 '19 at 14:46
Think your issue is that `.List(.ListCount - 1, 2)` doesn't refer to the *second* listbox column, but to a possible *third* one, as list Indices are zero bound and that you cannot format an added list row as a whole set (of 10 columns by Default using `AddItem`), you can only assign single formatted strings to each list column :-) — T.M., Jun 17 '19 at 14:48
The $4,575.00 comes from Column B, so I set a range from to get the filtered data from column A then just set .ColumnCount = 2 so the second column would just be column B. Would I need to set a second range? — Andrew, Jun 17 '19 at 14:49
@Andrew, no you have to format each element starting from `List(.Listcount - 1, 0)` as first element, `List(.Listcount - 1, 2)` as 2nd. `.List(.ListCount - 1, 3)` would even refer to the fourth list column. - See Peh's answer. :-) — T.M., Jun 17 '19 at 15:00
@Andrew note that you should specify the sheet in `LR1 = Range("A" & Rows.Count)` like `LR1 = ws1.Range("A" & ws1.Rows.Count)` or it might chose the wrong sheet. — Pᴇʜ, Jun 17 '19 at 15:14

Pᴇʜ · Accepted Answer · 2019-06-17T15:08:07.150

2

I think @T.M. is right. Try the following:

For Each Cell In rng1

    .AddItem Cell.Value 'this is your first column
    .List(.ListCount - 1, 1) = Format(Cell.Offset(0, 1).Value, "$#,##0.00") 'this is your second one
    'you tried to format the 3rd one (which was not visible because of .ColumnCount = 2:
    '.List(.ListCount - 1, 2) = Cell.Offset(0, 2).Value 'Format this column
Next Cell

Explanation:

.AddItem fills the first column. Column counting in .List(row, column) starts with 0 so .AddItem filled column 0 which means .List(.ListCount - 1, 1) is your second column (not the first one).

edited Jun 17 '19 at 15:08

answered Jun 17 '19 at 14:54

Pᴇʜ

56,719
10
49
73

2

@PEH - just as a side note: `.AddItem` even adds **10** empty column elements by default (whereas the array assignment method using e.g. a whole data set isn't restricted to that fixed number limitation). – T.M. Jun 17 '19 at 15:05
2

@T.M. Yes, correct! I will change "*adds the first column"* into "fills the first column". – Pᴇʜ Jun 17 '19 at 15:07
2

@Andrew FYI :-) possible further reading regarding the mentioned array method: [Populate Listbox with multiple columns](https://stackoverflow.com/questions/47528558/vba-excel-populate-listbox-with-multiple-columns/47531440#47531440) – T.M. Jun 18 '19 at 13:10

Format a Multiple Range Listbox with Currency Format

1 Answers1