ListView Mod!

Originally Posted by 6StringJazzer

This isn't hard, but your explanation has a few holes. What data from the sheet do you want load into the ListBox? If it's more than one column, what columns do you want displayed in the ListBox?

My Mistake :-(
I would like to display the data from sheet 1 columns A,B,C,D rows 2-500 for each column it is easy to just display this data but I want to make sure that it still works as it is now so that I can load data from 1 listbox to another ect.
Thank You
Dan

Change the GetInfoFromSomewhere routine to:

Public Sub GetInfoFromSomewhere(ByRef CompanyInfo)
    'You can also read from your xl sheet - this is just to illustrate

    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        CompanyInfo = .Offset(1).Resize(.Rows.Count - 1).Value
    End With

End Sub

and then adapt the code in the form as follows:

Public Sub FillListView()
    Dim CompanyInfo As Variant
    Dim column_header As ColumnHeader
    Dim Astr As String
    Dim Along As Long
    With LvTest
        .ListItems.Clear
        .ColumnHeaders.Add , , "Report No", 120
        .ColumnHeaders.Add , , "Date Reised", 120
        .ColumnHeaders.Add , , "Report Type", 100
        .ColumnHeaders.Add , , "Recipient Name", 100
        .HideColumnHeaders = False
    End With
    Call GetInfoFromSomewhere(CompanyInfo)
    Dim Avar
    With LvTest
        .View = 3 'determines style of the listview
        For i = 1 To UBound(CompanyInfo)
           Set Avar = .ListItems.Add(i, , CompanyInfo(i, 1))
           Avar.SubItems(1) = CompanyInfo(i, 2)
           Avar.SubItems(2) = CompanyInfo(i, 3)
           Avar.SubItems(3) = CompanyInfo(i, 4)
        Next i
    End With
End Sub

Thanks it works well in the same workbook but it doesn't work in my other workbook
this is the error:
User defined type not defined

with this line of code:

Private Sub LvTest_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)

This is a whole code:

Private Sub LvTest_ColumnClick(ByVal ColumnHeader As MSComctlLib.ColumnHeader)
    With LvTest
      If .SortKey = ColumnHeader.Index - 1 Then
         If .SortOrder = lvwAscending Then
            .SortOrder = lvwDescending
         Else
            .SortOrder = lvwAscending
         End If
      Else
         .SortKey = ColumnHeader.Index - 1
      End If
       .Sorted = True
  End With
End Sub

Any idea why this has happened when I have changed the workbooks? and how to fix it?
Thanks

Add a reference to the Listview control in the 2nd workbook... (Menu: Tools/References)

Thanks
What is the reference name as I can't find it???

You need to add the listview into the controls toolbox I suspect

Grate it works
THANK YOU!!! :-)
One more question....
Previously I have used the standard listbox which was populated with data from sheet 1 but only if the WORD in column Y was "OPEN" THIS MEAN THAT THE REPORT IS STILL OPEN.

Would it be possible to do with my new ListView??

Here is the code:

Sub PopulateLb()
Dim Y As Integer, X As Integer, ColNum As Integer
    Me.ListBox1.Clear
    Me.ListBox2.Clear
    
    Me.ListBox2.AddItem
    ColNum = 0
    For X = 1 To 19
        Select Case X
            Case 1, 2, 19, 7, 13, 14, 17, 18
            Me.ListBox2.List(0, ColNum) = Cells(2, X).Value
            ColNum = ColNum + 1
        End Select
    Next X
    
    For Y = 3 To EndRow
    If Range("A" & Y).Value = "" Then Exit For
        If Range("Y" & Y).Value = "OPEN" Then
            Me.ListBox1.AddItem
            ColNum = 0
            For X = 1 To 19
                Select Case X
                    Case 1, 2, 19, 7, 13, 14, 17, 18
                    Me.ListBox1.List(Me.ListBox1.ListCount - 1, ColNum) = Cells(Y, X).Value
                    ColNum = ColNum + 1
                End Select
            Next X
        End If
    Next Y
End Sub

Originally Posted by Kyle123

You need to add the listview into the controls toolbox I suspect

Hi Kyle123
Would you be so kind and take a look at my code above please?

Originally Posted by Kyle123

Like this, as an example in your sample workbook, change some of the Person Names to OPEN

Public Sub GetInfoFromSomewhere(Optional ByRef CompanyInfo)
    

    Const keyWord As String = "OPEN" 'Word to check
    Const columnNo As Long = 4 'Column number to check
    
    Dim temp
    Dim temp2()
    
    Dim x As Long, y As Long
    Dim colOpens As Object
    
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        temp = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Set colOpens = CreateObject("Scripting.dictionary")
    
    ReDim temp2(0 To UBound(temp, 2))
    
    
    For x = LBound(temp) To UBound(temp)
        If temp(x, 4) = keyWord Then
            For y = LBound(temp, 2) - 1 To UBound(temp, 2) - 1
                temp2(y) = temp(x, y + 1)
            Next y
             colOpens(colOpens.Count) = temp2
        End If
    Next x

    CompanyInfo = colOpens.items
    
End Sub

Public Sub FillListView()
    Dim CompanyInfo As Variant
    Dim column_header As ColumnHeader
    Dim Astr As String
    Dim Along As Long
    Dim avar As ListItem
    
    With LvTest
        .ListItems.Clear
        .ColumnHeaders.Add , , "Report No", 120
        .ColumnHeaders.Add , , "Date Reised", 120
        .ColumnHeaders.Add , , "Report Type", 100
        .ColumnHeaders.Add , , "Recipient Name", 100
        .HideColumnHeaders = False
    End With
    Call GetInfoFromSomewhere(CompanyInfo)

    With LvTest
        .View = 3 'determines style of the listview
        For i = LBound(CompanyInfo) To UBound(CompanyInfo)
           Set avar = .ListItems.Add(i + 1, , CompanyInfo(i)(0))
           For ii = LBound(CompanyInfo(i)) + 1 To UBound(CompanyInfo(i)) - 1
                avar.SubItems(ii) = CompanyInfo(i)(ii)
           Next ii
           ii = 1
        Next i
    End With
    
End Sub

Hi Kyle123 :-)
Thank you so much for replaying !
I think it will work but curently I am getting this error: Invalid Property Value with this line of code: avar.SubItems(ii) = CompanyInfo(i)(ii)
Any Idea what may be wrong.
Thank Yoy again!
Dan

Dunno, check the code you're actually using as I made an edit after the post.

Here's a working example

Originally Posted by Kyle123

Dunno, check the code you're actually using as I made an edit after the post.

Here's a working example

Forgot to mention that this was a test workbook and on the original workbook the OPEN is in column Y and I cannot change it due to layout.
It does work with the sample book so it could be a question of layout of my workbook??? I'ts still Sheet 1 but has lot more columns with data..... 35 being more specific

AS per the comments in my code, you need to amend the column that you are checking for the word in. Without a more accurate mock-up I'm struggling to help any further really, just set break points in your code and inspect the locals to try and suss it out.

Originally Posted by Kyle123

AS per the comments in my code, you need to amend the column that you are checking for the word in. Without a more accurate mock-up I'm struggling to help any further really, just set break points in your code and inspect the locals to try and suss it out.

Ok Let me upload amy workbook then :-)

Originally Posted by Kyle123

AS per the comments in my code, you need to amend the column that you are checking for the word in. Without a more accurate mock-up I'm struggling to help any further really, just set break points in your code and inspect the locals to try and suss it out.

It's not best looking workbook but this is exacty how I have it in my orginal book the one I was getting an error.
So what I would like to see is 4 columns displayed when text in column Y=OPEN
ColumnA
ColumnB
ColumnN
ColumnR
Column Y doesn't need display status but if it can be done then it's even better....

Thanks You I really mean it!

Off the bat you don't have enough headers, you've only got 4, you need one for each column. Your formatting is also a bit of a mess and so needs allowing for, the below isn't as dynamic as I'd like, but works:

Public Sub FillListView()
    Dim CompanyInfo As Variant
    Dim column_header As ColumnHeader
    Dim Astr As String
    Dim Along As Long
    Dim avar As ListItem
    Dim v
    
    With LvTest
        .ListItems.Clear
        For Each v In Sheets("sheet1").Range("A2:Y2").Value
            .ColumnHeaders.Add , , v
        Next v
        .HideColumnHeaders = False
    End With
    Call GetInfoFromSomewhere(CompanyInfo)

    With LvTest
        .View = 3 'determines style of the listview
        For i = LBound(CompanyInfo) To UBound(CompanyInfo)
           Set avar = .ListItems.Add(i + 1, , CompanyInfo(i)(0))
           For ii = LBound(CompanyInfo(i)) + 1 To 24
                avar.SubItems(ii) = CompanyInfo(i)(ii)
           Next ii
           ii = 1
        Next i
    End With
    
End Sub

Public Sub GetInfoFromSomewhere(Optional ByRef CompanyInfo)
    

    Const keyWord As String = "OPEN" 'Word to check
    Const columnNo As Long = 25 'Column number to check
    
    Dim temp
    Dim temp2()
    
    Dim x As Long, y As Long
    Dim colOpens As Object
    
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        temp = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Set colOpens = CreateObject("Scripting.dictionary")
    
    ReDim temp2(0 To UBound(temp, 2))
    
    
    For x = LBound(temp) To UBound(temp)
        If temp(x, columnNo) = keyWord Then
            For y = LBound(temp, 2) - 1 To UBound(temp, 2) - 1
                temp2(y) = temp(x, y + 1)
            Next y
             colOpens(colOpens.Count) = temp2
        End If
    Next x

    CompanyInfo = colOpens.items
    
End Sub

Just re-read that you only want certain columns, try this:

Public Sub FillListView()
    Dim CompanyInfo As Variant
    Dim column_header As ColumnHeader
    Dim Astr As String
    Dim Along As Long
    Dim avar As ListItem
    Dim v
    
    With LvTest
        .ListItems.Clear
        For Each v In Sheets("sheet1").Range("A2,B2,N2,R2,Y2")
            .ColumnHeaders.Add , , v.Value
        Next v
        .HideColumnHeaders = False
    End With
    Call GetInfoFromSomewhere(CompanyInfo)

    With LvTest
        .View = 3 'determines style of the listview
        For i = LBound(CompanyInfo) To UBound(CompanyInfo)
           Set avar = .ListItems.Add(i + 1, , CompanyInfo(i)(0))
           For ii = LBound(CompanyInfo(i)) + 1 To 4
                avar.SubItems(ii) = CompanyInfo(i)(ii)
           Next ii
           ii = 1
        Next i
    End With
    
End Sub

Public Sub GetInfoFromSomewhere(Optional ByRef CompanyInfo)
    

    Const keyWord As String = "OPEN" 'Word to check
    Const columnNo As Long = 25 'Column number to check
    Dim columns: columns = Array(1, 2, 14, 18, 25)
    
    Dim temp
    Dim temp2()
    
    Dim x As Long, y As Long
    Dim colOpens As Object
    
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        temp = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Set colOpens = CreateObject("Scripting.dictionary")
    
    ReDim temp2(0 To UBound(columns))
    
    
    For x = LBound(temp) To UBound(temp)
        If temp(x, columnNo) = keyWord Then
            For y = LBound(temp2) To UBound(temp2)
                temp2(y) = temp(x, columns(y))
            Next y
             colOpens(colOpens.Count) = temp2
        End If
    Next x

    CompanyInfo = colOpens.items
    
End Sub

Originally Posted by Kyle123

Just re-read that you only want certain columns, try this:

Public Sub FillListView()
    Dim CompanyInfo As Variant
    Dim column_header As ColumnHeader
    Dim Astr As String
    Dim Along As Long
    Dim avar As ListItem
    Dim v
    
    With LvTest
        .ListItems.Clear
        For Each v In Sheets("sheet1").Range("A2,B2,N2,R2,Y2")
            .ColumnHeaders.Add , , v.Value
        Next v
        .HideColumnHeaders = False
    End With
    Call GetInfoFromSomewhere(CompanyInfo)

    With LvTest
        .View = 3 'determines style of the listview
        For i = LBound(CompanyInfo) To UBound(CompanyInfo)
           Set avar = .ListItems.Add(i + 1, , CompanyInfo(i)(0))
           For ii = LBound(CompanyInfo(i)) + 1 To 4
                avar.SubItems(ii) = CompanyInfo(i)(ii)
           Next ii
           ii = 1
        Next i
    End With
    
End Sub

Public Sub GetInfoFromSomewhere(Optional ByRef CompanyInfo)
    

    Const keyWord As String = "OPEN" 'Word to check
    Const columnNo As Long = 25 'Column number to check
    Dim columns: columns = Array(1, 2, 14, 18, 25)
    
    Dim temp
    Dim temp2()
    
    Dim x As Long, y As Long
    Dim colOpens As Object
    
    With Sheets("Sheet1").Cells(1, 1).CurrentRegion
        temp = .Offset(1).Resize(.Rows.Count - 1).Value
    End With
    
    Set colOpens = CreateObject("Scripting.dictionary")
    
    ReDim temp2(0 To UBound(columns))
    
    
    For x = LBound(temp) To UBound(temp)
        If temp(x, columnNo) = keyWord Then
            For y = LBound(temp2) To UBound(temp2)
                temp2(y) = temp(x, columns(y))
            Next y
             colOpens(colOpens.Count) = temp2
        End If
    Next x

    CompanyInfo = colOpens.items
    
End Sub

Kyle you're are amazing!!
Everything works as it should apart from I cannot copy selected row into second listbox...
and also how do I limit the amount of column displayed in listview?
Thanks you so much for your help.

That code does limit the number of columns? There should only be 5?

The listbox works fine for me, what issue are you having?

Kyle I can't thank you enugh!!
It's all working now
Tahnk you for your help :-)

No probs