If you have ever used the Treeview control from the “Additional controls” section, then you know what a versatile control this is to show hierarchically organized data.
There are a couple of problems with this Treeview control:
- Compile errors due to a difference in how the control libraries are registered in 32 bits Windows’ System32 and 64 bit Windows’ SysWOW32 folders. If you distribute a file that was saved in 64 bit Windows, containing one of the “Microsoft Windows Common Controls 6.0” (The Treeview control is one of them) and with the reference set to “mscomctl.ocx”, people using 32 bit Windows will almost certainly have problems. At best it could entail removing both the control and the reference and replacing both, but at worst the user’s Excel can crash when trying to load the file and run the app.
- The standard Treeview control, like all non built-in ActiveX controls, cannot be used in 64 bit versions of Office.
Especially the second point convinced me it is time to develop a custom-made Treeview “control”, that only uses the native Office forms controls. I started building this a couple of weeks ago and after some time I tricked Peter Thornton into helping me with it :-)
The screenshot below shows both our new Treeview (left) and the Windows one (right) side-by-side:
Not bad, right?
Both Treeviews allow for checkboxes:
And both allow icons (windows Treeview not shown here):
You can also edit a node:
And expand and collapse nodes and navigate the tree using your arrow keys.
We built the custom Treeview using just two class modules. Using it in your project will require nothing more than copying the two classes and adding a bit of plumbing to your userform: some code and an empty frame which will hold the Treeview and possibly a frame with pictures for the icons.
We’re currently doing some cleaning up (like removing obsolete debugging stuff, adding comments and the like), so the “control” is not quite ready to be released to the outside world. Keep an eye on this blog, once we’re done we’ll post here.
Well, what do you think, is this useful or what? What functionality would be critical for you? Let us know!
Regards,
Hi Jan Karel,
A “native” treeview will definitely be welcome! I really like this control, but I’ve avoided it lately because of the problems you mention.
Very interested in this… also would be interested in a custom Listview!
Is there any way to change the node line lengths or size of checkboxes?
I used the TreeView control with a lot of data and ran into memory problems. I never figured out exactly why, but even a small object times lots of instances… Ended up with code that made the usage of the object slow and so I put that project on a back burner.
Also, I probably ran into the 32 bit vs 64 bit issue that you detail as I have 64 bit at home and 32 at work… this was another reason I put the project on hold.
So I’m looking forward to your version.
Cheers,
Eric
Hi Eric,
How many nodes approximately? Current status is that it performs nicely with about 1000 nodes. Then it starts to become noticably slow, especially when both icons and checkboxes are displayed.
I wondered if you would publish the code you for this? I would very much like to learn from it.
Yes we’ll be publishing all of it and more importantly: it will be free!
Of course we’ll ask you to keep our names in the source code so it is clear where you got it from.
We will also ask you to send us updates should you make any additions or find any bugs.
And of course we like to be praised for our hard work, so thank-you notes and Paypal donations are welcomed :-)
Jan Karel, this is fantastic.
I love the treeview, and honestly can’t believe that no 64 bit compliant version was released. One of the most popular articles on my site is on using a Treeview control, so it’s awesome to see that there is now a way to do it, and even better it’s native VBA. No more compliancy issues again. :)
Well done!
Hey Guys,
Thank you so much for your amazing work.
I’m looking for a solution to bypass 32/64 bits compilation issues on common controls for weeks.
If you have the same answer for listview control i’m your man.
And again thank you
Cyril
Great job.
I’d like to know if it’ll have support to drag and drop funcionality and HitTest method.
This was all done in VBA ? Wow, that’s really impressive.
How in the world did you do the graphics ?
Functionally, I’d like the ability to expand and collapse to a certain level via Sub/Function call.
lRet = TreeviewLevel(0,0) would collapse to the root node(s). lRet would contain the number of nodes.
lRet = TreeviewLevel(0,1) would set all nodes to the first level (1,2,3,4)
lRet = TreeviewLevel(2,3) would set level 1 node #2 to the third level (if it existed) lRet would contain the max level it achieved.
lRet = TreeviewLevel(0,3) would set all nodes to the third level (if they exist) lRet would contain the number of nodes achieving that level.
I used treeview when developing the Value Driver Modelling (VDM) add-on for Excel while working at PwC in Australia. You should be able to see some screenshots in the document here (http://www.pwc.com.au/industry/energy-utilities-mining/assets/ValueDriverModelling-Nov10.pdf)
The hierarchical display was just perfect for that application.
Cheers and thanks for the great inspiration in yiour blog.
Chris
@Ken: Thanks!
@Benzadeus: Drag and drop is not implemented (yet), but since the code will be open source, I’d like to invite others to add that!
@MSimms: Yes, VBA entirely, it only makes use of Label controls. On expanding/collapsing: We have a method in the class called
SetTreeExpansionLevel(Level) that handles this for you, but it does all nodes rather than just a selected one.
Will it be operational for MS Access as well ?
What a welcome UI control – I had stopped using Treeview because of the problems – and do have applications where your new one will come in handy – thanks for sharing
Wow, this looks fantastic!
I find Treeviews incredibly helpful in a lot of scenarios – so I’m really looking forward to see your tool replace the buggy original! Thanks already!!!
Wow, your doing this with label controls in VBA! that’s amazing! Will be very interesting to see how it works – incredible! well done guys…
Ross
@Chris Melck what did you use for the grid view?
@MSimms: No, not at the moment. Access forms are quite different from VBA userforms. But perhaps it can be adapted?
Very impressive work!
Yes, this would be very useful — I’m tired of fighting the MS Common Controls battle every time a new security update is pushed, undoing the last fix to mscomctl.ocx — no admin rights, so can’t fix it myself.
Thank you for taking on this effort.
Critical functionality: same events as Treeview 6.0, e.g., drag/drop (OLEStartDrag, OLEDragDrop, OLEDragOver, OLECompleteDrag), KeyDown/KeyUp, MouseDown/MouseUp. Also I use DragDropCursors.
Oh, and ready-by-Thursday would be perfect! Seriously, any thoughts on when you’ll release code?
“Oh, and ready-by-Thursday would be perfect”
LOL
We could publish a beta version this week I guess. We’re in the middle of tidying-up and documenting the code.
We’ll see about the events. The key and mouse events are easy. The drag and drop events I don’t know yet.
I have discovered the DatePicker control no longer works in EXCEL 2010. However I found some code that attempts to reproduce it using VBA only. I have taken it and reworked (giving appropriate credit) to function closely to the missing DatePicker control. Would there be an interest in it? Would this be the forum to publish it?
Looks awesome JK! I think it looks better than the native tv. I look fwd to laying my hands on the classes and having a play!
OK, guilty as charged. Like many software projects, this one got delayed a bit.
We got carried away in adding functionality so now have to pay the price, which is a belated delivery.
Not that we were on a schedule, but I did write “publish a beta version this week” above. And that was last week :-)
No promises, but keep your eye on this blog, perhaps we do manage to get it done *this* week.
I want to 64 bit treeview’s OCX.
Well we’ve been using the the active x controls for about 15 years now Jan, so I don’t think a few weeks will hurt!! ;-)
And here it is:
http://www.jkp-ads.com/articles/treeview.asp
@Ross
Sorry for late reply. The guy I was working with on the project managed to cobble a treeview and gridview control together. He went waaaaaaaaaay beyond my understanding, I told what I wanted the output to look like and he made it so.
Sorry can’t be of more help.
BRAVO!!…It’s here…it’s magnificent…and well worth the wait!
Excellent features and easy to use…quite an accomplishment
Thanks for sharing the results of your hard work!
@argh2: You’re welcome!
Hasta Lavista baby! Incredible.
:-)
I have been searching for about a month trying to find out what the Microsoft Tree View control was/is and finally I found this golden page. I downloaded the Demo and I am really impressed. Only one thing, I still don’t know how to implement this fine product.
I have an existing workbook with 31 sheets which all accumulate the sums on the front sheet that have totals for all the phases. This particular sheet has grouping for each phase. This is where I want the tree view, to replace my grouping. This is embarrassing but has anyone here made a video on implementing the product? I’m not even a novice VBA-er.
Oh I can hear you laughing but I am serious.
Thank You in advance.
I don’t know of any videos, but you should start with the most updated version
http://dailydoseofexcel.com/archives/2013/02/22/an-msforms-treeview-2-ready-for-beta-testing/