How to Populate ListView With Data Instead of ListBox

[JarekM]

Hi, the code below is working, but there is a small problem with it. I have the data populate a ListBox, but I want the data to populate a ListView. Can someone help with this?
I tried to change this code to ListView, but it would not work. So I need someones help with this VBA.

Private Sub CommandButton1_Click()
Call Display
End Sub

Private Sub UserForm_Initialize()

  rTABLE6 = "Table6"
 
  TblDB = Range(rTABLE6).Value
  ColNR = UBound(TblDB, 2)
 
  Set d = CreateObject("scripting.dictionary")
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 4)) = ""
    Next i
 
  Me.ChoiceListBox1.List = d.keys
  Set d = CreateObject("scripting.dictionary")
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 6)) = ""
    Next i
 
  Me.ChoiceListBox2.List = d.keys
  Set d = CreateObject("scripting.dictionary")
  d.comparemode = vbTextCompare
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 7)) = ""
    Next i
 
  Me.ChoiceListBox3.List = d.keys
  Me.ListBox1.ColumnCount = ColNR
 
  Call Display
End Sub

Sub Display()
  Dim Liste(), ok(1 To 3)
 
  n = 0
  For i = LBound(TblDB) To UBound(TblDB)
        For o = 1 To 3: ok(o) = False: Next o
        p = 0
        For Each c In Array(4, 6, 7)
            p = p + 1
            s = 0
           
                For j = 0 To Me("ChoiceListBox" & p).ListCount - 1
                  If Me("ChoiceListBox" & p).Selected(j) Then s = s + 1
                Next j
               
            If s = 0 Then
              ok(p) = True
            Else
           
                For j = 0 To Me("ChoiceListBox" & p).ListCount - 1
                   If Me("ChoiceListBox" & p).Selected(j) Then
                     If TblDB(i, c) = Me("ChoiceListBox" & p).List(j) Then ok(p) = True
                   End If
                Next j
               
             End If
            
        Next c
       
        If ok(1) And ok(2) And ok(3) Then
            n = n + 1
            ReDim Preserve Liste(1 To ColNR, 1 To n)
                For k = 1 To ColNR
                   Liste(k, n) = TblDB(i, k)
                Next k
        End If
  Next i
 
  If n > 0 Then Me.ListBox1.Column = Liste Else Me.ListBox1.Clear
End Sub

Sorry again, I am a beginner to ListView, and I like it a lot, so it would be very appreciative if someone could help me.
Thank you.

 

[Domenic]

Try to adapt the following code to meet your needs. Let's assume that your listview control is named ListView1. Also, let's assume that the first item of your dictionary contains the column header. In the initialize event handler, first set the properties for the listview...

    'Set some of the properties for the ListView (change as desired)
    With Me.ListView1
        .Gridlines = True
        .HideColumnHeaders = False
        .View = lvwReport
    End With

Then, you can add the column header for your listview...

    'Add column header (first item from keys)
    Me.ListView1.ColumnHeaders.Add Text:=d.keys()(0), Width:=90

Then, you can transfer the keys from your dictionary to your listview...

    'Transfer keys to listview (excluding header)
    For i = 1 To d.Count - 1
        Me.ListView1.ListItems.Add Text:=d.keys()(i)
    Next i

Then, in Display(), you can loop through the listview items...

    'Loop through listview items
    With Me.ListView1.ListItems
        For i = 1 To .Count
            If .Item(i).Selected Then
                If .Item(i).Text = "x" Then
                    'do something here
                End If
            End If
        Next i
    End With

Also, here's an example that uses a multi-column listview...

http://xl-central.com/fill-a-listview-on-a-userform.html

Hope this helps!

 

[JarekM]

Hi, I tried it out the column headers and the ListView items, but I keep getting errors from it.
Again, I'm sorry, but I am a beginner and do not understand thoroughly.

For my UserForm1, I use ListBox and the code works fine, but for my UserForm2 I use ListView, and my code does not want to work and shows a lot of errors.



The UserForm Initialize is what loads the Columns in the UserForm1 and the Sub Display is what loads the ListBoxs in the Userorm1[/IMG]

I would like to have this work for ListView as well.

I am sorry again and Thank You for your time.

 

[JarekM]

[/URL][/IMG]

Here is a better screenshot

 

[My Aswer Is This]

I'm trying to learn more about Listview myself. So I'm monitoring this thread myself.

 

[Domenic]

Can you post the code that you tried for one of your listboxes?

 

[JarekM]

Here is the Code:

Dim f, ColNR, rTABLE6, TblDB()

Private Sub CommandButton1_Click()
Call Display
End Sub


Private Sub UserForm_Initialize()

  rTABLE6 = "Table6"
  
  TblDB = Range(rTABLE6).Value
  ColNR = UBound(TblDB, 2)
  
  Set d = CreateObject("scripting.dictionary")
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 4)) = ""
    Next i

  Me.ListBox_Choice1.List = d.keys
  Set d = CreateObject("scripting.dictionary")
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 6)) = ""
    Next i
  
  Me.ListBox_Choice2.List = d.keys
  Set d = CreateObject("scripting.dictionary")
  d.comparemode = vbTextCompare
    For i = LBound(TblDB) To UBound(TblDB)
      d(TblDB(i, 7)) = ""
    Next i
  
  Me.ListBox_Choice3.List = d.keys
  Me.ListBox4.ColumnCount = ColNR
  
  Call Display
  
End Sub






Sub Display()
  Dim Liste(), ok(1 To 3)
  
  n = 0
  For i = LBound(TblDB) To UBound(TblDB)
        For o = 1 To 3: ok(o) = False: Next o
        p = 0
        For Each c In Array(4, 6, 7)
            p = p + 1
            s = 0
            
                For j = 0 To Me("ListBox_Choice" & p).ListCount - 1
                  If Me("ListBox_Choice" & p).Selected(j) Then s = s + 1
                Next j
                
            If s = 0 Then
              ok(p) = True
            Else
            
                For j = 0 To Me("ListBox_Choice" & p).ListCount - 1
                   If Me("ListBox_Choice" & p).Selected(j) Then
                     If TblDB(i, c) = Me("ListBox_Choice" & p).List(j) Then ok(p) = True
                   End If
                Next j
                
             End If
             
        Next c
        
        If ok(1) And ok(2) And ok(3) Then
            n = n + 1
            ReDim Preserve Liste(1 To ColNR, 1 To n)
                For k = 1 To ColNR
                   Liste(k, n) = TblDB(i, k)
                Next k
        End If
  Next i
  
  If n > 0 Then Me.ListBox4.Column = Liste Else Me.ListBox4.Clear
End Sub

Here is the code, the UserForm_Initialize populates the selected columns and the Display is for the ListBox and the filtering.

Thank you for the quick response, and hopefully you can adapt this code to the ListView code.

 

[Domenic]

Sorry, I said listboxes, but I meant listview. You said you tried to adapt the code for your listview. Can you post the code that you tried for one of your listview controls?

 

[JarekM]

Unfortunately, I think I deleted the code. Usually the codes that don't work I delete, because it frustrates me when I have to look at long codes.
But anyway, when I tried to add the code to the first three ListViews, the column header shows item from the table, not the title header.
So I didn't even try to adapt the code in Sub Routine called Display, to change ListBox control to ListView4 control that populate filtered data.

I am sorry, but I don't get any of this, I don't know VBA and it's too complicated for me. I learned some coding, but this is beyond my ability, that is why I came here to ask for someones help to change to code I posted above.

Please can you help me or anyone.
Thank you very much.

 

[My Aswer Is This]

After seeing this posting and another this week dealing with Listview I have learned some about listview.

Would you please explain in words what you want to load into Listview.

Please tell me in words and do not say look at my code for Listbox

I may be able to help.

I have seen how Listview may be a great tool to use with Userforms.