Solved

Datatype mismatch when using ListView

Posted on 2011-02-14
17
402 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
 
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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Many companies are making the switch from Microsoft to Google Apps (https://www.google.com/work/apps/business/). Use this article to learn more about what Google Apps has to offer and to help if you’re planning on migrating to Google Apps. It is …
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
The view will learn how to download and install SIMTOOLS and FORMLIST into Excel, how to use SIMTOOLS to generate a Monte Carlo simulation of 30 sales calls, and how to calculate the conditional probability based on the results of the Monte Carlo …
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

920 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now