How to format Listbox Columns as Currency in Excel

[Kheff]

Hi I've just started using a ListBox in Excel to view a table from one sheet on another.
I've been able to do this using very simple code as

Private Sub UserForm_Initialize()
  Me.ListBox1.List = Worksheets("Cashflows").Range("A4:H26").Value
End Sub

However, as this does not include formatting, the original data which may look like this:

is displayed as:

I'm wondering if it's possible to format the data displayed on the listbox as it is in the original sheet (column A as an integer and columns B to E as currency separated by commas with no decimals.
How would I go about this ?

Thank you so much

 

[dmt32]

Hi,
welcome to forum

To return what you see in a cell rather than its underlying value, you used the Range.Text property.

This does though, mean that you cannot use that simple line of code approach as you need to step through each cell in specified range.

Try this code & see if returns result you want

Private Sub UserForm_Initialize()
    Dim r           As Long, c As Long
    Dim ColWidths   As String
    Dim rng         As Range
    Dim arr()       As Variant
   
    Set rng = ThisWorkbook.Worksheets("Cashflows").Range("A4:H26")

    ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
   
    For r = 1 To rng.Rows.Count
        For c = 1 To rng.Columns.Count
            arr(r, c) = rng.Cells(r, c).Text
        If r = 1 Then ColWidths = ColWidths & rng.Cells(r, c).ColumnWidth * 5.6 & ";"
        Next c
    Next r
   
    With Me.ListBox1
        .RowSource = ""
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
        .ColumnWidths = ColWidths
        .List = arr
    End With
   
End Sub

As you are using Accounting Format in you range, likely that the column widths in your listbox will need to be adjusted to accommodate the currency spacing so I have included some code that hopefully, will do this for you.

Dave

 

[Kheff]

Hi,
welcome to forum

To return what you see in a cell rather than its underlying value, you used the Range.Text property.

This does though, mean that you cannot use that simple line of code approach as you need to step through each cell in specified range.

Try this code & see if returns result you want

Private Sub UserForm_Initialize()
    Dim r           As Long, c As Long
    Dim ColWidths   As String
    Dim rng         As Range
    Dim arr()       As Variant
  
    Set rng = ThisWorkbook.Worksheets("Cashflows").Range("A4:H26")

    ReDim arr(1 To rng.Rows.Count, 1 To rng.Columns.Count)
  
    For r = 1 To rng.Rows.Count
        For c = 1 To rng.Columns.Count
            arr(r, c) = rng.Cells(r, c).Text
        If r = 1 Then ColWidths = ColWidths & rng.Cells(r, c).ColumnWidth * 5.6 & ";"
        Next c
    Next r
  
    With Me.ListBox1
        .RowSource = ""
        .ColumnHeads = False
        .ColumnCount = rng.Columns.Count
        .ColumnWidths = ColWidths
        .List = arr
    End With
  
End Sub

As you are using Accounting Format in you range, likely that the column widths in your listbox will need to be adjusted to accommodate the currency spacing so I have included some code that hopefully, will do this for you.

Dave

Hey Dave,
Thank you so much for your reply, it works fantastically !
Just wondering, is it possible to be able to carry strings across as well?
Appreciate the help