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!
One of the things he did in his presentation was show the equivalent of an Excel bubble chart. He showed how different countries measured over the years. He also created a trail showing how a country progressed over time.
I decided to do the same with an Excel bubble chart – and implement both capabilities, i.e., the time snapshot and the time trail tracing the path, without any VBA code! The example I used was data from one of a series of seminars I had taught to healthcare executives. They participated, in teams, in a real-time, interactive, web-based simulation. In the simulation each team made decisions about how much of their limited resources to invest in (1) product development and operations and (2) marketing and sales. Their profitability depended both on their own decision and also their competitors. The simulation typically lasted 10 to 12 periods. The scroll bar in each chart controls the period shown or the latest period, as appropriate. The checkboxes control which teams have their performance history traced in the chart.
While I implemented the solution in Excel 2010, it should work in Excel 2007 and Excel 2003, though, in all fairness, I haven’t verified the older versions.
Have you ever experienced the dreaded “Circular reference warning” popping up when you opened an Excel file or entered a formula?
Excel detects a circular reference when a chain of calculations visits the same cell more than once. Many users get (very) confused by this message and have no idea what it is about. I’ll tried to demystify that message in a new article: