Populate Excel Listview Control

Hello all,

I am struggling a bit with the populating and double click even of a Listview control. I have very little experience with the Listview control.

I would like to populate the control with the table in sheet1 - I have very little idea of how this is done. Then once the control is envoked, I would like it so that when a user double clicks on a cell in the control, the value of that cell will appear in range("a1") of sheet1 - I am not even certain if the second part is possible. Thank you for any help.

Please see attached. Below is the code I have so far.

Please Login or Register  to view this content.

I confess I've never used this control so I suspect the below is wrong (or at best poorly coded)

Please Login or Register  to view this content.

That being said the only cell that can be double clicked using the above is the main item (ie the year) - the sub items are not seemingly available.

EDIT:
In regard to detecting a sub item being clicked see: http://www.developerfusion.com/code/...ick-detection/
(given functionality restrictions you might also want to consider a multi column list box given far simpler to populate ?)

Having nerded around for a while I think there is another alternative and that is to utilise the OWC Spreadsheet Control in your User Form.

Your profile states 2003 so you should find you have the Microsoft Spreadsheet Control available via Additional Controls... if you were running 2007 and were rolling this out across multiple clients you would have an issue as OWC is not installed as part of 2007 - development has ceased ... and as such you would need to install whichever version of OWC you utilise on each client ... in terms of versions I suspect you will have 9.0/10.0 listed however I've come across some posts by XL-Dennis (XL genius) that state both to be buggy and 11.0 to be more stable -- however if 11.0 is not available on your machine by default you face the same problem as if you were on XL2007 (ie installing on each client).

Assuming using the Control itself is not an issue per se (ie controls available on clients by default) you could then create a User Form with this Control in place (sized appropriately) and then use the following code:

Please Login or Register  to view this content.

The above (10.0) should generate your range in a spreadsheet control ... double clicking any cell within that range should update A1 with the associated value accordingly...
Downside compared to 11.0 (as annotated) is that for whatever reason the viewonlymode fails in 10.0 (commented out on that basis) - and as such I have added the StartEdit event to cancel (in effect enforcing read-only)

Originally Posted by Evagrius

Using a multi-column listbox, will the user be able to click on a single item?

The million dollar question... no, or at least I don't think so...

It looks, to me at least, that if you need to be able to select single cells in a matrix and you can not use the Spreadsheet control then you will need to pursue the earlier link re: capturing mouse position etc .. either that or think about building your own ... ie adding a grid of labels on the fly to your UserForm, populating each with the range value ... adding all labels to a collection and using a Class Module to handle the click event for all labels ... all of which I say that with zero expertise personally.

All that being said... as I made mention earlier I rarely use User Forms and even rarer would I use anything more complex than a ComoBox so I suspect I'm probably wrong


EDIT:
I should have added - the reason I suggested the ListBox as an alternative to the ListView was simply because given both have the same constraints regards single cell selection (ie neither can), you might as well use the ListBox given it's far simpler to populate.
(and is a default control)

If you step through the code and have the Locals Window open (View -> Locals) you will find that the Variant Array will be listed as

Please Login or Register  to view this content.

although there is only 1 column the Array is still 2-dimensional...

and though

Please Login or Register  to view this content.

will return 5 this is because with UBound function the Dimension parameter is optional (default of 1st dimension).

However, when referencing a specific point within the 2 dimensional array, eg:

Please Login or Register  to view this content.

you will need to specify values for both dimensions

Please Login or Register  to view this content.

If you were to transpose the Horizontal source range twice when first populating the variant array, ie:

Please Login or Register  to view this content.

You will find vHdrs becomes a single dimension array

Please Login or Register  to view this content.

at which point

Please Login or Register  to view this content.

would work (and the 2 dimensional syntax would not)

I would confess Arrays are not a strong point of mine and I've no doubt someone here can explain the above more thoroughly for you... if they're willing ...

Originally Posted by DonkeyOte

It looks, to me at least, that if you need to be able to select single cells in a matrix and you can not use the Spreadsheet control then you will need to pursue the earlier link re: capturing mouse position etc .. either that or think about building your own ... ie adding a grid of labels on the fly to your UserForm, populating each with the range value ... adding all labels to a collection and using a Class Module to handle the click event for all labels ... all of which I say that with zero expertise personally.

Below would be my attempt at the "build the control on the fly" approach ...
to which end there are same pretty basic assumptions...

a) source table / data is a contiguous range
b) all you want to do is output a value that is double clicked.

First I would create a UserForm - for sake of demo (given the no. of variants I have in the file) let's call this UserForm4 ... this User Form will not have any controls added to it manually.

Next insert a Class Module into your file, name that Module (via Properties [F4]): CTBEvents

Into that Class Module add the following code:

Please Login or Register  to view this content.

Now back to the User Form Object - add the below code

Please Login or Register  to view this content.

If you now create a Button to show the above UserForm you should find you're presented with a grid of textboxes (all locked), if you double click on any of the said text boxes (other than headers) A1 will populate accordingly.

I hope that helps.

DonkeyOte - you sir are a LEGEND!

After I followed your instructions and nervously initialized the userform - forgive my nerdiness - but what appeared was beautiful! I know, I know, its only a grid

For some reason Excel would not allow me to use CTBEvents so I used CTB only and that worked.

I will most likely spend the rest of the day learning from what you have done. Class modules are foriegn to me and so are collections. If you know of any links or sources to help learn those I would be very grateful.

Well done DonkeyOte - I am in your debt!

Class modules are foriegn to me and so are collections. If you know of any links or sources to help learn those I would be very grateful.

I'm no expert either, indeed I adapted my code above from advice/code that had been provided to me previously by another member (romperstomper) elsewhere.

In short you use both here because you have for ex. using the A3:F13 matrix as source - 66 textboxes to add to your userform dynamically, of which 50 need to have a double click event assigned to them and given all textboxes require the same double click event it makes sense to build "one routine to unite them all"...

For more info. on Class Modules see:

http://www.cpearson.com/excel/Classes.aspx
http://puremis.net/excel/code/086.shtml

For info. on Collection Object I would say that the VBA Help file is a good starting point.