?
Solved

Datatype mismatch when using ListView

Posted on 2011-02-14
17
Medium Priority
?
415 Views
Last Modified: 2012-05-11
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

0
Comment
Question by:ssmith94015
  • 9
  • 8
17 Comments
 

Author Comment

by:ssmith94015
ID: 34890862
Properties of ListView
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34890983
May I see the Excel File?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34891248
Ok, will have to clean it out a bit as it has confidential data.  Should not be too long.
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34891256
Fair enough. :)

Sid
0
 

Author Comment

by:ssmith94015
ID: 34891311
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34891360
clicks Edit on another form

I see only one form?

Sid
0
 

Author Comment

by:ssmith94015
ID: 34891435
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
 

Author Comment

by:ssmith94015
ID: 34892008
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
 

Author Comment

by:ssmith94015
ID: 34892064
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34892095
Sorry I was working on another thread... Let me go through all your posts :)

Sid
0
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34892120
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
 

Author Comment

by:ssmith94015
ID: 34892129
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34892144
No :)

Is this what you want? See snapshot?

Sid
Untitled.jpg
0
 
LVL 30

Accepted Solution

by:
SiddharthRout earned 2000 total points
ID: 34892165
Or this to be precise?

Sid
Untitled.jpg
0
 

Author Closing Comment

by:ssmith94015
ID: 34892180
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
 

Author Comment

by:ssmith94015
ID: 34892203
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
 
LVL 30

Expert Comment

by:SiddharthRout
ID: 34892204
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question