An MSForms Treeview 2: Ready for beta testing

Posted on by jkpieterse

Hi everyone,

Some time ago I announced I was working on “An MSForms Treeview” replacing the Common Controls Treeview with an all-VBA counterpart.

This home-made treeview control will work on any Office version as of Office 2000, including 32 and 64 bit Office. I expect it will even work on MAC Office, but I’m still waiting for test results.

Peter Thornton (thank you Peter!) jumped in on the project enthusiastically and really made a difference adding all sorts of usefull stuff and optimising the code for performance.

Now we’re ready for beta testing.

Please visit this page of my website for a description of the control and a download file which includes a demo userform implementing the treeview classes we built:

An MSForms (all VBA) treeview

Tell us what you think of it (oh, and please report bugs too!).

Regards,

Jan Karel Pieterse
www.jkp-ads.com

23 thoughts on “An MSForms Treeview 2: Ready for beta testing”

  1. Outstanding. Finally, I can use a tree view in my projects.

    I can’t seem to uncheck the Test Sort checkbox on the demo once I’ve checked it.

  2. Very very good; a small bug found I think, probably very trivial:
    when trying to “Dump Data” it breaks at line > rng(i,.Level)=.Caption
    Sub GetData2(rng As Range)
    ‘ dump all data
    Dim i As Long
    Dim cNode As clsNode
    For Each cNode In mcTree.Nodes
    i = i + 1
    With cNode
    rng(i, .Level) = .Caption
    End With
    Next
    End Sub

    I think reason is that .Level is 0, and .Caption is “Root Node”.
    Only happened after playing quite a bit with the various functions, but I could replicate it a few times after restarting “fresh”.
    I don’t have an inmediate use for it but I’ll try to use it and report if any other bug found.

  3. Looks excellent, worked fine on windows 7, XL07, not really tested much beyond the demon app, but that all worked fine.

    Excellent work guys, excellent, excellent work!

  4. This is freakin genius! Some questions and thoughts off the top of my head, none of which diminish the fact in any way that this is freakin genius:
    1. I take it you can’t set showmodal = false?
    2. It would be cool if you could delete a mode using the delete key on keyboard, especially given you allow the use of copy and paste shortcuts.
    3. It would be cool if you could export a picture of the treemap itself to the spreadsheet.
    4. It would be way cool if this could be built in the spreadsheet itself, rather than being tied to a userform.
    5. It would be way way way cool if you could view pivottables like this! Nodes reflect the hierarchy of the pivot, plus each node display the aggregated data measure, and the more you drill down, the lower and lower aggregation you get.

    Also, I get an error when I use the Dump Data button, with debugger jumping to the line rng(i, .Level) = .Caption in the GetData2 sub. Errors out because .Level is zero. Happens for the root node only…if i skip the offending line, it otherwise works fine and does in fact dump data to the sheet. I just changed the code to rng(i, .Level + 1) = .Caption and it seems to work fine…although for some reason node 1a is showing up in the spreadsheet as 1am, and with value of 0.041666… which I can only see if I use =Value(b2)

  5. Oh, and a search function would be killer! Type some text in the box, hit GO, and it automatically closes off any nodes except those that contain your search term. :-)

  7. @Dick, the Test Sort checkbox can be toggled while the treeview is not running. If ticked before running a demo, the root’s 4th node’s childnodes will be given random text and the childnodes sorted (with any descendents). While running it acts like a button to do similar with the selected node’s children. Afraid it’s not an object lesson in intuitive form design!

    @Ross, Thanks for your comments, I take it you tried v015. In v016 a small bug crept into the “Dump Data” example; a simple fix, where it breaks change rng(i, .Level) to (i, Level + 1)

  8. Hi Jan Karel –

    Trying with XL2011, the Mac version. This line doesn’t work: Set ufForm = New ufDemo. It should.

    So going inside the ufDemo code, in InitializeDemo1, I get “Runtime error ‘30132’”

    Google says: Runtime error 30132 is a horrible computer error that can be induced by obscure registry errors, problematic Windows Registry, computer virus and irregular memory usage.

    If you’re trying to set the registry on a Mac, well, you can’t. ;-)

    … mrt

  9. Outstanding work chaps. I haven’t got anything to add that hasn’t been mentioned. Yes I suppose it would be cool to be able to delete with the delete key. I for one do not care for a worksheet control :-P

    One thing I think would be really cool would be to enable drag and drop; including the drag and drop of an entire node complete with child nodes to another node. Also a right-click menu; so Rename, Cut, Copy, Paste, Add Sibling, Add Child…

    But I’m being fussy! This is good!

  10. @Michael, one possibility for the Mac error, though not sure why it only transpires calling the demo, a reference was left to Extensibility in tools ref’s in v016. That’s been removed from v017 which will be available very soon (if not already by the time you read this). It’s 100% pure VBA so in theory it should work!

    @Jon, a delete key seems a popular request and it’s already implemented in v017. Adding right click stuff is simply a matter of trapping the node’s right-click event and adding whatever functions to a rt-click context menu. Some might need that, others not, maybe we’ll include as an example.

    Drag & drop will be included, to move or copy nodes complete with child-nodes. In effect same as ctrl-x/c & v as already in the demo. We’re considering the best way of implementing it, eg self contained or exposed in the userform. There’s quite a lot to consider if self contained, eg allow d&d within a single level, only vertically or horizontally, cater for validating the action, etc. If self contained we’d need to include loads of options. We’d be interested in how d&d might be used in real situations.

  11. I have just uploaded build 017 with the bugfix Peter mentions.

    Build 017 also contains a demo project with two files: a small Access database and an associated Excel file which pulls its data from the database.

  12. Just expressing my wish list ;-)… With all the hard work done I’m sure I can install the drag and drop as and when (if) needed. Like I told JK before; I haven’t really had much need for tv for a looooooong time, and unlikely to soon… That said your tv does make it a somewhat appealing control again…

    One time I recall using it was for organisation structure, but for a hierarchical profit centres structure. So we had different companies, with divisions, departments etc… It went down quite a number of tiers. Probably around say 10. This was for a VERY large multi-national conglomerate undergoing endless reorg and transformation. We used to move entire profit centres, teams, departments and divisions as part of the reorg; and as such the profit structures had to move accordingly. I was involved in a planning capacity so being able to move entire nodes at a time allowed me demonstrate the financial effects of moving parts of the businesses around….

    Giving a real example. We had a team that used to do reconciliations for the different operating companies. They had a number of different profit centres depending on the activity they were doing (fixed assets, bank rec, GL etc). Initially they belonged to a shared service centre. Later they were moved to a central Corporate division. I allowed our financial management to understand the financial impacts on shared services and the corporate division by simply allowing them to drag and drop the entire team node from shared services to corporate.

    Is that a helpful scenario?

  13. @Jon, thanks for that detail. It sounds like your treeview was ideally suited to the task. It’s certainly the intention our treeview should be able to do all of what you describe. I imagine in your scenario you not only wanted to move teams (+ childnodes) vertically but also horizontally, ie promote/demote from the original level. You mention a very large orinisation, do you recall how roughly many nodes were involved including all those down to the 10th level.

    @Jeff, thanks again for your comments. I know it took a while for your post to get approved and appear here, in the meantime I replied to your similar post on Jan Karel’s treeview page.

  14. Hi Peter –

    Downloaded and ran v018. Same thing happens. I’d be happy to help, but have no ideas.

    … mrt

  16. Hi Jan Karel –

    It doesn’t take long. The error occurs on the line “Set ufForm = New ufDemo”

    Interestingly, if I reset the code, what looks to be a message box immediately opens the says only “Microsoft Visual Basic”. It has only an OK button. I went into the code and changed all msgbox titles to see where that comes from (used nos. 1 to 14) and it didn’t make a change to the result. Since I searched the project, I don’t believe I missed any, so I’m not sure it’s a msgbox. But that’s what it looks like.

    … mrt

  17. @Peter

    I still have that hierarchy on file somewhere (as en export from SAP). I can try and dig it out if you think it will help. I will need to change the details so that I don’t violate NDA…

    There wasn’t really demotion/promotion because it was a profit centre level (i.e. team/function became the lowest level); there were no individuals involved. In fact not all profit centre’s include personnel costs at all.

    Let me know if you would like a sample; if it helps…

  18. @Jon, that would be great and thanks. It sounds like a useful treeview, hopefully ours can be configured to work similarly. My email is in the headers.

    @Michael, thanks for trying. We have our friendly Mac expert on the case! Check back next week.

  20. @Michael,

    We’ve found that in order for the tool to work on a MAC you have to use different icons in the userform, looks like MAC Excel does not like transparent pictures on userforms. We’re working on getting a demo that works on both platforms.

  21. Jan Karel – It’d been a long time before I told you that! Wow. I do know that the Mac makes no distinction on message box icons. ;-( They all look like the application icon.

    … mrt

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.