multicolumn listbox with formatted fields

[leonv]

Greetings,
I am fairly new to Excel VBA and need some help. I have an excel form that inlcudes a multicolumn listbox that displays item number, item description, and price. It is populated from a master spreadsheet which includes an amount field. The amount field is a float that is formated as currency. The master sheet also needs to be filtered based on other selections in the form.

If I use rowsource to populate the listbox, it picks up the currency formatting, but it displays all rows regardless of any data filtering.

If I copy the range to an array and then copy the array to the listbox, I get the filtered rows, but I get unformatted numbers (floats) insead of formatted currency.

How can I populate a multicolumn listbox with formatted values?

Your help will be greatly appreciated!
Leonv

 

[BrianB]

It becomes clearer when we rememember that data in List Boxes, Text Boxes etc. is always Text which often needs to be converted to and from numbers. So something like :-

Private Sub UserForm_Initialize()
    Dim DataSheet As Worksheet
    Dim MyList As Range
    Dim MyCurrency As String
    '-------------------------------------------------------------
    Set DataSheet = ThisWorkbook.Worksheets("data")
    Set MyList = DataSheet.Range("NumberList")  ' 2 column range
    Rw = 1
    '-------------------------------------------------------------
    '- Clear existing data
    ListBox1.Clear
    '-------------------------------------------------------------
    '- data to listbox
    While MyList.Cells(Rw, 1).Value <> ""
        ListBox1.AddItem
        '- column 1 - ordinary data
        ListBox1.List(Rw - 1, 0) = MyList.Cells(Rw, 1).Value ' column 1
        '- column 2 numbers format as currency
        MyCurrency = Format(MyList.Cells(Rw, 2).Value, "$#,##0.00")
        ListBox1.List(Rw - 1, 1) = MyCurrency 'column 2
        Rw = Rw + 1
    Wend
    '-------------------------------------------------------------
End Sub

 

[jindon]

leonv
1) delete Rowsource setting
2) paste the code onto UserForm module

Private Sub UserForm_Initialize()
Dim i As Long, ii As Long, rng As Range
Set rng = Sheets("YourSheetNameHere").Range("YourRangeHere")
With Me.ListBox1
    .ColumnCount = rng.Columns.Count
    For i = 1 To rng.Rows.Count
        For ii = 1 To rng.Columns.Count
            .AddItem
            .List(i-1,ii-1) = rng.Cells(i,ii).Text
        Next
    Next
End With
Set rng = Nothing
End Sub

This should display the data Exactly how you see in the cell.

 

[leonv]

solved

Thanks for the help and support

I ended up using an array and formatting the currency in the array:

    Dim rngData As Range
    Dim vntData As Variant
    
    'read currently selected items data into array vntData
    With Workbooks(WorkbookStr).Worksheets(SheetName)
        With .UsedRange
            '.AutoFilter Field:=5, Criteria1:="<>*S*gle Wall*", Operator:=xlAnd, Criteria2:="<>*SW*"
            .AutoFilter Field:=5, Criteria1:="<>*SW*", Operator:=xlAnd ', Criteria2:="<>*SW*"

        'Selection.AutoFilter Field:=5, Criteria1:="<>*sw*", Operator:= _
        'xlAnd, Criteria2:="<>*SW*"

            '.AutoFilter Field:=5, Criteria1:="Double Wall Tank"
            Set rngData = .Range(.Cells(2, 1), .Cells(.rows.Count, 3)).SpecialCells(xlCellTypeVisible)
            If Not rngData Is Nothing Then
                vntData = rngData.Value
            End If
            .AutoFilter
        End With
    End With

    'find the last row in the array
    Dim arrayRowCnt As Long
    arrayRowCnt = UBound(vntData, 1)
    
    'format the currency column in the array
    Dim rowcnt As Long
    Dim MyCurrency As String
    For rowcnt = 1 To arrayRowCnt
        MyCurrency = Format(vntData(rowcnt, 3), "$#,##0.00")
        vntData(rowcnt, 3) = MyCurrency
    Next
    
    ListItemsListBox.List = vntData

 

[bs0d]

Re: solved

Old thread, but same topic so I figured I'd ask here:

Is there a way to format the field if the data is sourced from an ADO query?

For example:

Dim c As Long
c = 0
Do Until myResults.EOF
    Sheets("Sheet1").MultiPage.Pages(0).lstComments.List(c, 0) = Format(myResults.Fields(0).Value, "MM/DD/YY")
    Sheets("Sheet1").MultiPage.Pages(0).lstComments.List(c, 1) = myResults.Fields(1).Value
c = c + 1
Loop

This code doesn't work, but seems intuitive. Can you do it this way? How might I need to revise the code? It errors out on "Invalid Property Array Index".