PDA

View Full Version : [SOLVED] ListView Run-time erro "13"; Type mismatch



PerryBeatty
03-23-2014, 09:12 AM
I've created a listview in one of my userforms and I've got 2 different sub routines to fill the listview. Both routines are accessing the same data set and userofrm, "ListRun" works but "ListFail" generates a type mismatch error. Can anyone help me in resolving this, I want to use the code in the "ListFail" sub routine.


Sub ListRun()
Dim LR As Long
Dim LC As Long
Dim i As Long
Dim cell As Range
Dim lsheet As Worksheet
Set lsheet = ThisWorkbook.Worksheets("List2")
lsheet.Select
With lsheet
LR = .Range("A" & .Rows.Count).End(xlUp).Row
LC = .Cells(1, .Columns.Count).End(xlToLeft).Column
form1.ListView1.ListItems.Clear
i = 1
With ListView1
For i = 1 To LR
Set cell = Cells(i, 1)
.ListItems.Add , , cell
.ListItems.item(i).ListSubItems.Add , , Cells(i, 2).Text
.ListItems.item(i).ListSubItems.Add , , Cells(i, 3).Text
.ListItems.item(i).ListSubItems.Add , , Cells(i, 4).Text
Next i
End With
End With
End Sub


Sub ListFail()
Dim item As ListItem
Dim LR As Integer
Dim i As Integer
Dim lsheet As Worksheet
Set lsheet = ThisWorkbook.Worksheets("List2")
ListView1.ListItems.Clear
LR = lsheet.Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To LR
Set item = ListView1.ListItems.Add(Text:=lsheet.Cells(i, 1)) <- THIS IS WHERE THE ERROR CODE HAPPENS
item.SubItems(1) = lsheet.Cells(i, 2)
item.SubItems(2) = lsheet.Cells(i, 3)
item.SubItems(3) = lsheet.Cells(i, 4)
Next
End Sub

SamT
03-23-2014, 10:18 AM
First try forcing the types

Set item = ListView1.ListItems.Add(Text:=lsheet.Cells(i, 1).Text)

snb
03-23-2014, 10:45 AM
Sub M_snb()
sn = Sheet2.Cells(1).CurrentRegion

For j = 1 To UBound(sn)
With ListView1
With .ListItems.Add(, , sn(j, 1))
.ListSubItems.Add , , sn(j, 2)
.ListSubItems.Add , , sn(j, 3)
.ListSubItems.Add , , sn(j, 4)
End With
End With
Next
End Sub

PerryBeatty
03-23-2014, 03:41 PM
First try forcing the types

Set item = ListView1.ListItems.Add(Text:=lsheet.Cells(i, 1).Text)

PerryBeatty
03-23-2014, 03:47 PM
First try forcing the types

Set item = ListView1.ListItems.Add(Text:=lsheet.Cells(i, 1).Text)

Hi SamT,

I tried forcing the type and still got the type mismatch error. The reason I thought it would be better to use the "ListFail" vba code routine was I need to format the some of the data in the listview, change row colors, format columns with "$ ##,###,###.00", etc. I've tried to do this with "ListRun" code, but have not been successful. Any suggestions would be much appreciated.

Kenneth Hobs
03-23-2014, 04:05 PM
Can you post a short example workbook to make it easier to help you?

PerryBeatty
03-23-2014, 06:00 PM
i've stripped out all the code and worksheets except what is needed to load the listview. Hope this helps...

Kenneth Hobs
03-23-2014, 07:48 PM
Dim item As Object

PerryBeatty
03-24-2014, 04:07 AM
Thanks Kenneth, it worked!!!

snb
03-24-2014, 06:49 AM
In think a listbox would suffice as well