Format Listview

[kalle82]

Hi!

Im working in excel 2003.

I have managed to get VBA, to fill my listview control with
all the values in my file. I also managed to format the colors. Now here's my question how can I make my listview to skip certain rows based on a value in them.

Ive tried with the following but it does not cut it.

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim lngRow As Long
    Dim lvwItem As ListItem
    Dim lngEndCol As Long
    Dim lngCol As Long
    Dim lngEndRow As Long
    Dim lngItemIndex As Long
    Dim lvwItem2 As ListItem
        
    Set ws = Worksheets("Ärenden")
    lngEndCol = ws.Range("A1:K1").End(xlToRight).Column
    lngEndRow = ws.Range("A1:K1").End(xlDown).Row
    
    lngRow = 1
    With ListView1
        .View = lvwReport
        For lngCol = 1 To lngEndCol
            .ColumnHeaders.Add , , ws.Cells(lngRow, lngCol).Value
        Next
        For lngRow = 2 To lngEndRow
            lngCol = 1
            lngItemIndex = 0
            Set lvwItem = .ListItems.Add(, , ws.Cells(lngRow, lngCol).Value)
            For lngCol = 2 To lngEndCol
                lngItemIndex = lngItemIndex + 1
                
                
                
                lvwItem.SubItems(lngItemIndex) = ws.Cells(lngRow, lngCol).Value 'Adds Value from Current Row and Column 1
                
                
                
                If lvwItem.SubItems(3) = "22 Kronofogden" Then
                lvwItem.ForeColor = RGB(100, 200, 50)
                lvwItem.ListSubItems.Item(lngItemIndex - 2).ForeColor = RGB(100, 200, 50)
                
                ElseIf lvwItem.SubItems(3) = "26 CSN" Then

                lvwItem.ForeColor = RGB(255, 165, 0)
                lvwItem.ListSubItems.Item(lngItemIndex - 2).ForeColor = RGB(255, 165, 0)
                
                ElseIf lvwItem.SubItems(3) = "21 Försäkringskassan" Then

                lvwItem.ForeColor = RGB(255, 0, 0)
                lvwItem.ListSubItems.Item(lngItemIndex - 2).ForeColor = RGB(255, 0, 0)
                
                End If
            
///HERE'S the TROUBLE...                
If lvwItem.SubItems(7) = Null Then
                
                Set lvwItem2 = .ListItems.Remove(lngItemIndex).Value
                
                lvwItem2.SubItems(lngItemIndex) = ws.Cells(lngRow, lngCol).Value
                
                End If

I dont know if i even can do that with an IF statment?!

Thanks

 

[PHV]

Replace this:
If lvwItem.SubItems(7) = Null Then
with this:
If IsNull(lvwItem.SubItems(7)) Then

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886