Adding currency format to ListView columns

Hi all

I have a Userfrom with a List View, below code loads list view items from an excel sheet, it has 24 columns

how do I add currency format below code that List view columns 7,8,9,10,11,12,13,14 shows as currency

any help will be apricated

Private Sub LoadListView()

    'Declare the variables
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    
    'Set the source worksheet
    Set wksSource = Worksheets("Sheet1")
    
    'Set the source range
    Set rngData = wksSource.Range("A1").CurrentRegion
    
    'Add the column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
    'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
             
        Next j
    Next i
   
      
End Sub

many thanks in advance

Format the value before adding it.

Set LstItem = Me.ListView1.ListItems.Add(Text:=Format(rngData(i, 1).Value, "currency"))

or

Set LstItem = Me.ListView1.ListItems.Add(Text:=Format(rngData(i, 1).Value, "#,##0.00"))

Same with the ListSubItems

thank you for your reply, I have added your suggestion, but it hasn't worked

Private Sub LoadListView()

    'Declare the variables
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    
    'Set the source worksheet
    Set wksSource = Worksheets("Sheet1")
    
    'Set the source range
    Set rngData = wksSource.Range("A1").CurrentRegion
   
    'Add the column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
     
    'Fill the ListView
    For i = 2 To RowCount
    Set LstItem = Me.ListView1.ListItems.Add(Text:=Format(rngData(i, 1).Value, "#,##0.00"))
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
      
        For j = 2 To ColCount
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
             
        Next j
    Next i
   
      
End Sub

If data in those columns is formatted as currency, try change all .Value to .Text

Originally Posted by jindon

If data in those columns is formatted as currency, try change all .Value to .Text

only yellow highlighted should have currency values the others date or some references

Capture.PNG

I tries this one and it didn't work either, sorry to be a pain

Private Sub LoadListView()

    'Declare the variables
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long
    
    'Set the source worksheet
    Set wksSource = Worksheets("Sheet1")
    
    'Set the source range
    Set rngData = wksSource.Range("A1").CurrentRegion
    
    'Add the column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
    'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        
        Set LstItem = Me.ListView1.ListItems.Add(Text:=Format(rngData(i, 1).Value, "#,##0.00"))
        
        For j = 2 To ColCount
             
            LstItem.ListSubItems.Add Text:=rngData(i, j).Value
             
        Next j
    Next i
   
      
End Sub

I meant to say the data in those columns in the worksheet.

If the columns are formatted as you want to see on list view, .Text property should appear as what you see on the worksheet.

Originally Posted by jindon

I meant to say the data in those columns in the worksheet.

If the columns are formatted as you want to see on list view, .Text property should appear as what you see on the worksheet.

This is how the excel sheet look behind the user form

Attachment 805564

Your attachement is invalid, so can not open.

Originally Posted by jindon

Your attachement is invalid, so can not open.

sorry here it is again

Capture2.PNG

Did you change all the .Value property to .Text in the code posted in your initial post #1?

If so and doesn't work, I need to see your workbook.

here is the workbook

thanks

ListView control doen't seem to accept special characters.
So, if you can change it to ListBox, it is lot easier.

example

Private Sub UserForm_Initialize()
    With Sheets("sheet1").ListObjects(1)
        Me.ListBox1.ColumnCount = .ListColumns.Count
        Me.ListBox1.ColumnHeads = True
        Me.ListBox1.RowSource = .DataBodyRange.Address
    End With
End Sub

this code worked on ListView

thank you very much for your time nad Thank you to ByteMarks too

Private Sub LoadListView()

   'Declare the variables
    Dim wksSource As Worksheet
    Dim rngData As Range
    Dim rngCell As Range
    Dim LstItem As ListItem
    Dim RowCount As Long
    Dim ColCount As Long
    Dim i As Long
    Dim j As Long, s: s = ActiveCell
    
    'Set the source worksheet
    Set wksSource = Worksheets("Sheet1")
    
    'Set the source range
    Set rngData = wksSource.Range("A1").CurrentRegion
    
    'Add the column headers
    For Each rngCell In rngData.Rows(1).Cells
        Me.ListView1.ColumnHeaders.Add Text:=rngCell.Value, Width:=90
    Next rngCell
    
    'Count the number of rows in the source range
    RowCount = rngData.Rows.Count
    
    'Count the number of columns in the source range
    ColCount = rngData.Columns.Count
    
    'Fill the ListView
    For i = 2 To RowCount
        Set LstItem = Me.ListView1.ListItems.Add(Text:=rngData(i, 1).Value)
        
     
        
        For j = 2 To ColCount
            If (j > 6) * (j < 15) Then
                LstItem.ListSubItems.Add Text:=VBA.Format$(rngData(i, j).Value, """" & VBA.ChrW(163) & """#,##0.00")
            Else
                LstItem.ListSubItems.Add Text:=rngData(i, j).Value
            End If
        Next j
    Next i