Datatype mismatch when using ListView

I have had very good help in getting this far in getting the ListView in an Excel form to work.  Iknow it does work as the sample I was sent worked, but when I put the code into my workbook, I am getting this error.  Attached is the code, the range of data is A5:A7 on a worksheet called "Datasource".  The columns are a mix of text and numbers.  This is NOT my original own code, Sid was kind enough to provide it (his works), but with the changes I need to make, I have done something to it.  Error I am getting is DataType Mismatch.  It errors out at the line


Set lvItem = ListView1.ListItems.Add()
Private Sub UserForm_Initialize()
    Dim lvItem As ListItem
    
    ListView1.ColumnHeaders.Add , , "Col A"
    ListView1.ColumnHeaders.Add , , "Col B"
    ListView1.ColumnHeaders.Add , , "Col C"
    ListView1.ColumnHeaders.Add , , "Col D"
    ListView1.ColumnHeaders.Add , , "Col E"
    ListView1.ColumnHeaders.Add , , "Col F"
    
    ListView1.View = lvwReport
    ListView1.FullRowSelect = True
    ListView1.ListItems.Clear
    
    For i = 5 To 7
        Set lvItem = ListView1.ListItems.Add()
        lvItem.text = Sheets("DataSource").Range("A" & i).Value
        
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("B" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("C" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("D" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("E" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("F" & i).Value)
    Next i
End Sub

Private Sub ListView1_Click()
    TextBox1.text = ListView1.SelectedItem.text
    TextBox2.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(1).text
    TextBox3.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(2).text
    TextBox4.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(3).text
    TextBox5.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(4).text
    TextBox6.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(5).text
End Sub

Open in new window

Sandra SmithRetiredAsked:
Who is Participating?
 
SiddharthRoutConnect With a Mentor Commented:
Or this to be precise?

Sid
Untitled.jpg
0
 
Sandra SmithRetiredAuthor Commented:
Properties of ListView
0
 
SiddharthRoutCommented:
May I see the Excel File?

Sid
0
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
Sandra SmithRetiredAuthor Commented:
Ok, will have to clean it out a bit as it has confidential data.  Should not be too long.
0
 
SiddharthRoutCommented:
Fair enough. :)

Sid
0
 
Sandra SmithRetiredAuthor Commented:
Attached is the DataSource tab.  What eventually I want is when a user clicks Edit on another form, this form appears with the relevant data and then edits in the text box.  
TEST-VBADEVELOPMENTVERSION.xls
0
 
SiddharthRoutCommented:
clicks Edit on another form

I see only one form?

Sid
0
 
Sandra SmithRetiredAuthor Commented:
That form is not attached yet, sorry I was not clear.  RIght now, I just want to get the Market Data in there and then go from there.
0
 
Sandra SmithRetiredAuthor Commented:
Sid, I am going through the library references and we have a SalesLogicControl.  When I deslected that, I could get past the Set lvItem = ListVIew1.ListItems.Add() line, but then when it hits the next line, lvItem.text= Sheets("Sheet3").Range("A" & i.Value I get a subscript out of range.  I changed the name of the sheet reference from DataSource to Sheet3 hoping to test if that made a difference.  
0
 
Sandra SmithRetiredAuthor Commented:
Ok, I got it to work when I changed the sheet's Name  to Sheet3 - that is, both (Name) and Name are Sheet3 - but this will not work as I need to use this type of code for different sheets and they are not always the same (Name) Sheet1, Sheet2, etc.  I need to use the Name (i.,e DataSource) in the code.
0
 
SiddharthRoutCommented:
Sorry I was working on another thread... Let me go through all your posts :)

Sid
0
 
SiddharthRoutCommented:
When I removed the references to the SalesLogicControl I was able to launch the userform with no problems. There was only one problem That on clicking the Listview I am telling the code to assign values to TextBox1- TextBox6 but there are no such textboxes on the form...

Sid
0
 
Sandra SmithRetiredAuthor Commented:
I finaly got it to work!  BUT it only seems to work if the range starts at A1 - I need the data to start at A6:A7.  I have been able to pass the Name to the code, but it simply does not like starting at row 6 rather than row 1 - does LIstVIew always assume A1 is the starting point?
0
 
SiddharthRoutCommented:
No :)

Is this what you want? See snapshot?

Sid
Untitled.jpg
0
 
Sandra SmithRetiredAuthor Commented:
Ok, once I got rid of the SalesLogic library, I went back over your code and yes, that is what I want and it now works.  I can change the span of rows as well as the number of columns as the underlying source changes.  THANKYOU!   This was very painful to get through and I wish I coud award double points!
0
 
Sandra SmithRetiredAuthor Commented:
The last step (and am saving that for tomorrow), is when the user changes the text boxes that the change fees back to the worksheet.  If I have trouble, will probably see another question!  But hopefully that should be fairly straight forward on change event.  Again, thank you.
0
 
SiddharthRoutCommented:
BTW this is the code :)

Private Sub UserForm_Initialize()
    Dim lvItem As ListItem
    Dim i As Integer  'Me
    
    ListView1.ColumnHeaders.Add , , "MarketData"
    ListView1.ColumnHeaders.Add , , "Lookup Value"
    ListView1.ColumnHeaders.Add
    ListView1.ColumnHeaders.Add
    
    ListView1.View = lvwReport
    ListView1.FullRowSelect = True
    ListView1.ListItems.Clear
    
    For i = 6 To 7
        Set lvItem = ListView1.ListItems.Add()
        lvItem.text = Sheets("DataSource").Range("A" & i).Value
        
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("B" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("C" & i).Value)
        ListView1.ListItems(ListView1.ListItems.Count).ListSubItems.Add , , (Sheets("DataSource").Range("D" & i).Value)
    Next i
End Sub

Private Sub ListView1_Click()
    txtMarketData.text = ListView1.SelectedItem.text
    txtLookupValue.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(1).text
    txtColumnNumber.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(2).text
    txtRangeName.text = ListView1.ListItems(ListView1.SelectedItem.Index).ListSubItems(3).text
End Sub

Open in new window


Sid
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.