Solved

Datatype mismatch when using ListView

Posted on 2011-02-14
17
409 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

 
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 500 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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

As with any other System Center product, the installation for the Authoring Tool can be quite a pain sometimes. This article serves to help you avoid making these mistakes and hopefully save you a ton of time on troubleshooting :)  Step 1: Make sur…
Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

739 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