Link to home
Start Free TrialLog in
Avatar of Sandra Smith
Sandra SmithFlag for United States of America

asked on

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

Avatar of Sandra Smith
Sandra Smith
Flag of United States of America image

ASKER

User generated image
May I see the Excel File?

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

Sid
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
clicks Edit on another form

I see only one form?

Sid
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.
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.  
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.
Sorry I was working on another thread... Let me go through all your posts :)

Sid
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
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?
No :)

Is this what you want? See snapshot?

Sid
Untitled.jpg
ASKER CERTIFIED SOLUTION
Avatar of SiddharthRout
SiddharthRout
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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!
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.
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