Display Listview data in Userform

[van0912]

Hello,

I would like to display data from an excel sheet ("Test") in an UserForm listview. I have 12 columns and 500 rows in my sheet table. In the code, message error keep pop-in as "Run-time error 13. Type mismatch". Could you please help me with that issue?

Thank you

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim rg As Range
Dim i As Integer
Dim j As Integer

Set ws = ThisWorkbook.Sheets("Test")
Set rg = ws.Range("refTab")

With UserForm3.ListView1

For i = 1 To 12
.ColumnHeaders.Add , , rg.Offset(0, i - 1)
Next i

For i = 1 To 500

.ListItems.Add , , rg.Offset(i, 0)
Next i

For i = 1 To 500
For j = 1 To 11

.ListItems(i).ListSubItems.Add , , rg.Offset(i, j)
Next j
Next i

.View = lvwReport

End With

End Sub

 

[Domenic]

The reason you're getting that error is that rg.Offset(0, i - 1), rg.Offset(i, 0), and rg.Offset(i, j) each return a range, not a single value. Hence the type mismatch error. Try the following instead...

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim rg As Range
    Dim li As ListItem
    Dim i As Integer
    Dim j As Integer
    
    Set ws = ThisWorkbook.Sheets("Test")
    Set rg = ws.Range("refTab")
    
    With rg
        For i = 1 To .Columns.Count
            Me.ListView1.ColumnHeaders.Add , , rg.Cells(1, i).Text
        Next i
    End With
    
    With rg
        For i = 2 To .Rows.Count
            Set li = Me.ListView1.ListItems.Add(, , rg.Cells(i, 1).Text)
            For j = 2 To .Columns.Count
                li.ListSubItems.Add , , rg.Cells(i, j).Text
            Next j
        Next i
    End With
    
    Me.ListView1.View = lvwReport

End Sub

Hope this helps!

 

[van0912]

The reason you're getting that error is that rg.Offset(0, i - 1), rg.Offset(i, 0), and rg.Offset(i, j) each return a range, not a single value. Hence the type mismatch error. Try the following instead...

Private Sub UserForm_Initialize()

    Dim ws As Worksheet
    Dim rg As Range
    Dim li As ListItem
    Dim i As Integer
    Dim j As Integer
   
    Set ws = ThisWorkbook.Sheets("Test")
    Set rg = ws.Range("refTab")
   
    With rg
        For i = 1 To .Columns.Count
            Me.ListView1.ColumnHeaders.Add , , rg.Cells(1, i).Text
        Next i
    End With
   
    With rg
        For i = 2 To .Rows.Count
            Set li = Me.ListView1.ListItems.Add(, , rg.Cells(i, 1).Text)
            For j = 2 To .Columns.Count
                li.ListSubItems.Add , , rg.Cells(i, j).Text
            Next j
        Next i
    End With
   
    Me.ListView1.View = lvwReport

End Sub

Hope this helps!

Hello Domenic,
Thank you for your help on this! I managed to make it works with the following code and added a combobox that displays value of the second column (range I). Any chance you know how I can do to make it filter on the listview? Fo example if I select a value in the combobox?
Thanks again!

Private Sub UserForm_Initialize()

Dim ws As Worksheet
Dim rg As Range
Dim i As Long
Dim j As Long

Set ws = ThisWorkbook.Sheets("Test")  
Set rg = ws.Range("ReferenceTab")            

With UserForm3.ListView1             

For i = 1 To 12         
        .ColumnHeaders.Add , , rg.Offset(0, i - 1)
Next i

For i = 1 To 500
        .ListItems.Add , , rg.Offset(i, 0)
Next i

For i = 1 To 500   
For j = 1 To 11    
            .ListItems(i).ListSubItems.Add , , rg.Offset(i, j)
Next j
Next i

    .View = lvwReport

End With

Dim v, e
With Sheets("Test").Range("I4:I500")
v = .Value
End With

With CreateObject("scripting.dictionary")
.comparemode = 1

For Each e In v
If Not .exists(e) Then .Add e, Nothing
Next
If .Count Then Me.ComboBox1.List = Application.Transpose(.Keys)

End With
       
End Sub

 

[Domenic]

You're very welcome.

For your new question, though, please start a new a thread.

Cheers!

 

[van0912]

You're very welcome.

For your new question, though, please start a new a thread.

Cheers!

Sure! Here's the new thread: Filter a listview with combobox
Thank you for helping !

 

[smozgur]

I managed to make it works with the following code

The code claimed to be working:

.ColumnHeaders.Add , , rg.Offset(0, i - 1)
...
.ListItems.Add , , rg.Offset(i, 0)
...
.ListItems(i).ListSubItems.Add , , rg.Offset(i, j)

However, it shouldn't work as it was posted as the solution. Actually, I am sure about it but still tested the provided code to verify that.

As @Domenic also explained, rg.Offset(rows, cols) will return a range object, not a string value that each Add method requires in the provided implementation. Although what I am going to suggest is not the best way to do that and I would surely stick with the perfect dynamic list view implementation that @Domenic provided that also is not limited to a certain number of columns and rows in the provided range, in the best scenario, .Cells(1) should be added after each Offset property to make the current code work as shown in the sample line below.

rg.Offset(0, i - 1).Cells(1)

@van0912 - We'd like to make sure that this question's marked solution actually works to help future readers. So, if you could post your working code then it is perfectly fine to mark your own post as the solution - assuming it is using a different method other than the provided answer. Or, if it works as you posted, then it means the ListView control that comes with the Excel 2016 version should be working differently than the Excel 365 version that any future reader (including myself) would also like to know about this difference as a reference.

Otherwise, I will switch the marked solution post to the second post in this thread to help future readers.

 

[van0912]

Hello @smozgur thank you for your input! I've tried to add .cells(1) in Domenic's code but could'nt get the data in the listview (only RefTab header appears)... Should I add .cells(1) in my code?

 

[smozgur]

Hello @smozgur thank you for your input! I've tried to add .cells(1) in Domenic's code but could'nt get the data in the listview (only RefTab header appears)...

Domenic's code works without any change if the correct range is provided (in fact, it is already using the Cells property).

Should I add .cells(1) in my code?

Yes. What I suggested is supposed to be added to the last code you posted.

 

[van0912]

Domenic's code works without any change if the correct range is provided (in fact, it is already using the Cells property).


Yes. What I suggested is supposed to be added to the last code you posted.

Ok I understand better, thank you! I've added .cells(1)

 

[smozgur]

Ok I understand better, thank you! I've added .cells(1)

You're welcome.

I marked post#2 as the solution to this question as I explained above.