An MSForms (all VBA) treeview for Access, Excel And Word

Content

Introduction

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:

  1. Compile errors due to a difference in how the control libraries are registered in 32 bits Windows' System32 and 64 bit Windows' SysWOW64 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.
  2. 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 to develop a custom-made Treeview "control", that only uses the native Office forms controls. A couple of weeks after I started building this 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 in their simplest display mode (read on, there are even prettier screenshots further down the page):

Treeview controls
Two treeviews, left: VBA tree, right: Common controls tree

In the following pages I'll show what our treeview can do and explain how to put it to use in your own VBA project.

MAC Office and 64 bit Office Compatible!

Our Treeview works in all versions of Excel, Access and Word; from Office 2007 to 2019 and 365, for Windows and Mac, 32bit and 64bit. It also works in some earlier versions though you would need to adapt the demos made for 2007 and later.

Windows screenshot:

Treeview demo on Windows Excel
Treeview demo on Windows Excel

Mac screenshot:

MAC screenshot of our treeview demo form
Screenshot of treeview on Mac Excel 2011

Acknowledgements

The basic plumbing and code structure of this treeview control was devised by me. However, without the help of my friend and fellow (former) Excel MVP Peter Thornton, lots of functionality would not have been available now. For that I sincerely thank Peter!

Furthermore, Access MVP Ben Clothier was kind enough to make the necessary adjustments to incorporate the treeview in an Access form

Also: Fellow Excel MVP Ron De Bruin ensured the treeview also works on MAC Office 2011, Thanks Ron!

All code in the treeview is (c) JKP Application Development Services and Peter Thornton (the Authors). It remains our sole intellectual property.

However, we're offering this treeview to you at no cost. You get an unrestricted license for use in any VBA project you like. You're free to modify any part of the code at will.

We do request that you:

  • Keep our comments in place.
  • Do not remove our names, url's or email addresses from the code.
  • Send us your praise and your comments.
  • Send us any functional additions you do to the treeview.

And please mention the source of the treeview (including a link to this page) in your helpfile, manual and/or on your about screen.

We're always interested to see how people have implemented the VBA Treeview. So please feel free to send a screenshot with a brief description or relevant details.

Disclaimer

You use this control at your own risk: The authors accept no liability whatsoever for any damages which may arise due to the use of our treeview.

Donations

Many, many hours were spent developing this treeview. Although we developed it for use in our own projects, we are giving it away for free!

Nevertheless, we would really be pleased if you actually express your appreciation in a more "tangible" form. So here is a paypal donation button at your disposal:

Paypal donate button

Download

The Excel workbook contains most of the documentation (on the tabs of the workbook), so I recommend you to at least download the Excel version. The Access version has instructions on its main form (click the "How do I...?" button) on how to implement the treeview in your own projects.

Download the treeview sample Excel workbook (including documentation) (build 026.5, 10 Jan 2023, downloaded 46.839 times)

Download the treeview sample Word document (build 026.5, 10 Jan 2023, downloaded 15.367 times)

Download the treeview sample Access database (build 026.5, 10 Jan 2023, downloaded 38.349 times)

‘Pro’ Treeview and ListGrid VBA controls

If your project needs more features and/or better performance than our free treeview, or if you need a listgrid control you've come to the right place as well.

The professional version of the treeview control has exceptional performance. Even with tens of thousands of nodes it will load quickly and remain highly responsive. It also has several new features including drag and drop. Timing experiments have shown that the pro version of our treeview outperforms the common controls treeview.

Our new ListGrid combines most of the functionality of the ActiveX Listview and Flexgrid controls with many extra useful features. It is the result of extensive beta testing by some of our treeview users, thanks guys!

The screenshot of the demo below gives an idea of just some of its capability.

Professional ListGrid control

The Pro Treeview and ListGrid are available for 32/64 bit Excel and Access. The Excel version will also work in Mac, one or two features are disabled for Mac but we’re working on it. Unlike our free treeview they are self contained in their own files and designed to work more like real controls.

For more details and if interested in a trial license to try either of these ‘controls’ please contact us:

Pro Treeview enquiry

Pro Listgrid enquiry

Please note that the pro versions are paid versions. Pricing available upon request by using the links above (The download files above contain a Word document which also gives more details about the pro controls).

Content

  1. Features
  2. How To Use
  3. Examples

 

Other controls

Another often used control is the calendar control. This control has the added problem that it has been deprecated with Office 2010 (where we're supposed to use the date picker control). Frankens Team created an all-vba alternative using very similar techniques to what we've done here.

Ron de Bruin created a Date Picker control for MAC Excel.

Comments

All comments about this page:

Comment by: Benzadeus (22-2-2013 23:12:43) deeplink to this comment

That's amazing! I'm already testing it and works great. You guys created a control better than the original TreeView.

Comment by: Jeff Weir (23-2-2013 11:09:01) deeplink to this comment

This is cool. Note i'm getting an "Application-defined or object-defined" error when I hit the Dump Data button in the GetData2 sub, with rng(i, .Level) = .Caption highlighted.
Problem is .Level is set to zero.

Haven't attempted to walk through the code yet to get my head around what's happening here.

Comment by: jeff weir (23-2-2013 11:52:15) deeplink to this comment

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. 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. Would be cool if you could export a picture of the treemap to the spreadsheet.
4. Would be cool if this could be built in the spreadsheet itself, rather than being tied to a userform.
5. Wouldn't it be 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.

Comment by: Jan Karel Pieterse (23-2-2013 15:29:20) deeplink to this comment

Hi Jeff, Benzadeus,

Thanks!

We'll take note of the comments and bugs reported here and use them for future updates!

Comment by: Peter Thornton (23-2-2013 19:19:09) deeplink to this comment

Sorry about the error in GetData2, something got changed at the last moment!
It will be fixed in the next version but in the meantime -

In Sub GetData2() change
Rng(I, .Level) = .Caption
to
Rng(I, .Level + 1) = .Caption


Re 1#, There is no problem as far as the treeview is concerned to show the
form as vbMmodal or vbModeless

Re 2# Key events are exposed and a node remove (delete) method is built in,
so it's very easy to include your key-delete -

In the demo form, in mcTree_KeyDown add the following between
Select..EndSelect

Case vbKeyDelete
    mcTree.NodeRemove cNode
    mcTree.Refresh

Note that will also delete the active node's child-nodes if any. You might
want to include some code to validate OK to delete before calling
NodeRemove.

re 3# Do you mean literally and image of the TreeView? It's simple to copy a
screenshot of the form to a sheet with APIs, but you'd need to crop the
treeview (actually that would also be possible with APIs but a lot more
work). But I would have thought the data dump example could be adapted much
more simply to your requirements.

Re 4# Not quite sure what you mean, view the treeview embedded on the sheet
sheet, like an ActiveX control?

Re 5# <Smile> It ought to be possible to load a pivot-table view into the
Treeview

Comment by: Jeff Weir (23-2-2013 19:59:14) deeplink to this comment

Thanks Peter. ALso posted this over at the DDOE thread, but my comment is still in moderation there. One thing a added to that comment is that a search method would be way cool, where it searches for a text string, identifies which nodes contain it, and collapses any downstream nodes that don't contain the search term

I'd be interested in hearing about what kinds of things people use treeview for, too.

Thanks again.

Comment by: Jeff Weir (23-2-2013 20:44:25) deeplink to this comment

In terms of #3, adapting the datadump is simple enough:

For Each cNode In mcTree.Nodes
        <b>If cNode.ParentNode.Expanded And cNode.Level > 0 Then </b>
            i = i + 1
            With cNode
                rng(i, .Level + 1) = .Caption
            End With
        End If
And I guess it's even possible to replicate a static picture of the treemap on the sheet using the SmartArt hierarchy. For instance, there' some code at http://www.mrexcel.com/forum/excel-questions/554224-using-visual-basic-applications-smart-art.html


But in terms of #4 I was thinking more about a user-configurable treemap - not a static picture - using shapes, or even the Hierarchy WordArt functionality. (Aside: I see that the macro recorder doesn't work with SmartArt shapes in Excel 2013)

Comment by: Peter Thornton (25-2-2013 11:04:22) deeplink to this comment

Jeff,
# Re your search and collapse combination. All of that's straightforward with what's already built in. Briefly, start by fully collapsing the tree with "mcTree.ExpandToLevel 0". Then recursively loop 'branches' of child-nodes, if a match is found, call "mcTree.ScrollToView myNode".

ScrollToView was designed to display a particular node. In a large tree it would be better to roll your own code to expand as the same way ScrollToView does (just the While/Wend bit), then call .Refresh when done.

re #3 Excellent. It needs a little bit more, this should dump a snapshot of the currently displayed (ie expanded) nodes to a sheet.

Sub GetData3(cParent As clsNode, lCt As Long, _
                    ByVal lLevel As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
'     but only if visiblle
Dim cChild As clsNode
    lLevel = lLevel + 1
    lCt = lCt + 1
    rng(lCt, lLevel) = cParent.Caption
    If Not cParent.ChildNodes Is Nothing Then
        If cParent.Expanded Then
            For Each cChild In cParent.ChildNodes
                GetData3 cChild, lCt, lLevel, rng
            Next
        End If
    End If
End Sub

call it like this -

Dim cRoot As clsNode
Dim lCnt As Long, lLevel As Long
    For Each cRoot In mcTree.RootNodes
     GetData3 cRoot, lCnt, lLevel, ws.Range("A1")
    Next


You can simply adapt that in the "cmdGetData_Click()" demo

Re 4# If I follow I think that would be well beyond the scope of our Treeview.

Great comments and thanks for your interest.

Comment by: Carlos Manteigas (1-3-2013 00:02:09) deeplink to this comment

I entirely agree with Jeff on the use of the word genius. This was exactly what i've been searching for. Thanks! I think it would most interesting if the treeview could retrieve a previously Dumped data also. Thanks again.

Comment by: Jan Karel Pieterse (1-3-2013 15:33:40) deeplink to this comment

Hi Carlos,

Thanks for the compliment!

Just to be clear: We created the treeview control, and we created an example file so you can see how to use it.

However, implementing the control in your project is something that is up to you, which includes writing code to populate the control from whichever source you have.

The current download has a sample project which populates the tree from an Access database.

Comment by: Lisa Green (3-3-2013 17:57:38) deeplink to this comment

Stupendously excellent ultra mega hyper Good!!

Love it!

Lisa

Comment by: Jan Karel Pieterse (3-3-2013 19:37:51) deeplink to this comment

Hi Lisa,

Thanks! :-)

Comment by: Stefan Hellwig (6-3-2013 15:26:48) deeplink to this comment

Lisa expressed it very well, this is brilliant work! Thank you very much for this great piece of software! It works extremely well. I did not observe any problems while using it.

Since I am using it with Word instead of Excel I made a few (minor) changes to get it to work there (mainly the cursor).

I also implemented the "EnsureVisible" behavior of the original MSComCtl. My code also raises a double-click event when editing is disabled.

I will be sending you all my modifications directly by e-mail so that you can have a look and/or incorporate them as you please.

Again, thank you very very much!

Best Regards, Stefan.

Comment by: Jan Karel Pieterse (6-3-2013 19:31:54) deeplink to this comment

Hi Stefan,

Thanks for your compliments!
We'll certainly look at your modifications and take from it what we might be able to use!

Comment by: Hermanito (7-3-2013 23:31:21) deeplink to this comment

Amazing piece of work, I will certainly dig deeper into the code to learn how you did it.

While playing with the Demo in Excel 2013, 64-bit, I did notice a bug (I think). Not sure if it also does that in other versions or bitness.
Reproducable as follows:
- start the demo
- click the Demo Treeview button to fill the treeview
- uncheck the Full width checkbox
- uncheck the Show lines checkbox
- check the Full width checkbox
- ERROR: clsTreeView Refresh: Error in BuildRoot: Object variable or With block variable not set

Apparently, both checking or unchecking the Full width with the Show lines unchecked, will trigger the error. The weird part is that resuming after the break on 'Stop' works, but then a lot of the other interactions start causing the code to break into debugmode at the same point

Also, something weird is going on with a horizontal scrollbar appearing for no reason...

Comment by: Jan Karel Pieterse (8-3-2013 13:20:21) deeplink to this comment

Hi Hermanito,

Thanks for reporting!

Comment by: Peter Thornton (8-3-2013 17:10:03) deeplink to this comment

Hi Hermanito,

Well spotted, I was hoping nobody would until the next version! It "crept" into v019, if you want to fix yours in advance of v020, in Let ShowLines(), move the "If bOrig <> mbShowLines..Refresh" above the preceding "End If"

Comment by: Comet (14-3-2013 04:12:17) deeplink to this comment

Hi Jan Karel Pieterse,
Thanks for your product. It's great!
I got mistake when I created some Root Node (or one Root node) without branch. When I created the Root Node without branch then left click once then double Enter, the program show "run time error 91"
How to fix it?

Again, thank you very very much!
Best Regards, Comet.!

Comment by: Jan Karel Pieterse (14-3-2013 08:59:06) deeplink to this comment

Hi Comet,

Thanks for reporting, we'll fix that for sure!

Comment by: conim (2-4-2013 12:04:45) deeplink to this comment

Great project, indeed! Two things that would make it even better would be:
1) Possibility to use dashed lines instead of solid
2) Expanders to look more like original ones and not like icons/images.

In other words, if MSForms treeview could look more like Common controls treeview, would be fantastic.

Comment by: Jan Karel Pieterse (2-4-2013 13:54:31) deeplink to this comment

Hi Conim,

Thanks for your feedback. Unfortunately, 1 is hard to implement, as there are no controls with dashed borders.
2 has already been implemented, you can choose whether to use image expanders or non-image expanders, which do look more like the original one (see first screenshot on this very page).

Comment by: Evelyn (3-4-2013 10:19:08) deeplink to this comment

Thank you for this nice TreeView. Really great work!

In the MS TreeView it is possible (with relative and tvwChild) to add a child-nodes to a specific parent at any time.

Are you planning to include something like that in your Code?

Best regards,
Evelyn

Comment by: Jan Karel Pieterse (3-4-2013 12:01:23) deeplink to this comment

Hi Evelyn,

Thank you for the compliments!
With regards to adding childnodes to existing nodes, our treeview works slightly different from the Common Controls treeview, I agree.

It functions similar to the VB.NET treeview; you add children to a node by using the addChild method of a node.

However, you can step through the nodes of the tree by using the clsNode's method called GetChild recusively, provided you have given your nodes unique keys.

You could use a function like this to return a specific node by its key:

Sub foo()
    Dim cRoot As clsNode
    Dim cNode As clsNode
    Dim cTree As clsTreeView
    Set cTree = New clsTreeView
    With cTree
        Set cRoot = .AddRoot(sKey:="Root", sCaption:="Root Node")
        Set cNode = cRoot.AddChild("1", "1")
        Set cNode = cNode.AddChild("2", "2")
        Set cNode = FindNode(cRoot, "2")
    End With
End Sub

Public Function FindNode(cNode As clsNode, vKey As Variant) As clsNode
    Dim cFoundNode As clsNode
    Dim cNextNode As clsNode
    Set cFoundNode = cNode.GetChild(vKey)
    If cFoundNode Is Nothing Then
        If Not cNode.ChildNodes Is Nothing Then
            For Each cNextNode In cNode.ChildNodes
                Set FindNode = FindNode(cNextNode, vKey)
                If Not FindNode Is Nothing Then Exit Function
            Next
        End If
    Else
        Set FindNode = cFoundNode
    End If
End Function

Comment by: Peter Thornton (3-4-2013 13:29:40) deeplink to this comment

Hi Evelyn,

Just to add to Jan Karel's comments, although we think the clsNode.AddChild() method is more efficient, for "backwards compatability" with the the old treeview we have already included almost exactly what you ask. In the documentation (clsTreeView sheet) check out the .NodeAdd() method. This is very similar to the original's Nodes.Add() method (note the difference with the dots).

Two small differences, our constants are named like "tvChild" vs "tvwChild" (but you change those). Also, although you can identify the "Relative" with its string key (as in the original), as a bonus you can simply pass a reference to the node, eg


mcTree.NodeAdd cNode.Key, tvChild, "my key", "my caption" ' optional icons
' or
mcTree.NodeAdd cNode, tvChild, "my key", "my caption"

Comment by: Peter Thornton (3-4-2013 13:36:44) deeplink to this comment

Hi Conim,

As Jan Karel explained we can only do continuous lines (or no lines), though they could be any colour you want.

Concerning the expander icons, you can make whatever icons you want. Take screen shots of any icons you prefer, size and save them to file, then load into the relavant image controls. Do similar for checkboxes.

Comment by: Alex (3-4-2013 20:13:56) deeplink to this comment

Hi,

Superb component! Works great.

Some small changes I had to add:

1/ When you start without an active node, and then click on a node, the nodeClick Event is not fired, so I added it in the mctlControl_Click:

Private Sub mctlControl_Click()
    If moTree.ActiveNode Is Nothing Then
        Set moTree.ActiveNode = Me
        moTree.NodeClick Control, Me 'AVDV
    End If

2/ When using the arrow keys left & right, we are not notified when a node has changed. This was also easily fixed by adding the NodeClick in the right places.
Example:
     Set ActiveNode = ActiveNode.ParentNode
     ScrollToView , 1
     NodeClick ActiveNode.Control, ActiveNode 'AVDV

3/ Sometimes the active node is not visible, but I need to investigate this more to know what is really happening.

Already a big thanks, this control is really something we can all learn from.
A decent listview control based on these principles would also be awesome.



Kr,
Alex


Comment by: Jan Karel Pieterse (4-4-2013 09:08:55) deeplink to this comment

Hi Alex,

Thanks a lot for your comments, much appreciated!

Comment by: Peter Thornton (4-4-2013 13:04:01) deeplink to this comment

Hi Alex,

#1. In theory this modification shouldn't be necessary, the new line would result in moTree.NodeClick called twice.

#2. Good point! I don't think the ScrollToView line should be necessary though, however NodeClick will only need to be called for the specific scenario so we'll look into the best way to handle it.

#3. Off the top of my head the only time the active node would be hidden is if changing the vertical scroll bar moves it out of sight. Ah, in v021 the active node wasn't always repositioned correctly after Move/Copy, but hopefully all working as expected in v022.

If I'm missing something re #1 do contact me privately (email in the headers) and I'll be pleased to look into it.

Comment by: Jeff Maultby (17-4-2013 12:41:52) deeplink to this comment

Perhaps enhance to allow each node and root node to have several columns where each column may also be a checkbox type column. Then we can use your control to replace the (MSComCtl.ocx) ListView control on 64bit Office VBA.

Comment by: Jan Karel Pieterse (17-4-2013 13:43:18) deeplink to this comment

Hi Jeff,

Thanks for your comment!

I guess even better would be to have someone step up and create a listview just like we did with the treeview.

I think combining the two into one might make it too complex.

Comment by: Billy Hamilton (20-4-2013 10:24:29) deeplink to this comment

I love your control, but can't seem to find consistent code on how to set the ActiveNode on the way INTO the form. I want to set a default value.

I see code that is commented out in the Demo:


' Set mcTree.ActiveNode = cChild ' could activate the new child
    mcTree.ScrollToView cChild, 2    ' reset scrolltop if necessary to view the new child

Neither of these seems to do anything.

Can you give a tip?

We have managed to get every other item on our wishlist working and this one has us stuck.

We are not using checkboxes, or icons.

Comment by: Jan Karel Pieterse (20-4-2013 21:13:06) deeplink to this comment

Hi Billy,

Can you perhaps send the file to me?

Note that you will only actually see the active node when the treeview has the focus.

Comment by: Peter Thornton (21-4-2013 21:10:11) deeplink to this comment

Hi Billy,

To set the Activenode do literaly simply that. Eg referring to the demo form, towards the end of InitializeDemo1(), after creating the treeview with Refresh -


With mcTree ' a reference to the treeview
' code
    .Refresh
    Set .ActiveNode = cNode 'refers to a clsNode
'or in the demo
    Set .ActiveNode = .Nodes("2.1.1")
    .ScrollToView


If you notice in the demo the node with key "2.1.1" is the only node that's not shown at startup, hidden behind a collapsed button. However setting it to the activenode will ensure all it's parents are expanded.
Also, setting the activenode like this will highlight it with choice of colours depending on if the Treeview itself is or is not in focus.
Final detail, call .ScrollToView (see the documentation sheet for optional arg's) to ensure the activenode is in view (though not necessary with the node "2.1.1").

There are lots of other ways to refer to an existing node besides its key. For your purposes, while creating the nodes, assign and maintain a reference to the node you will eventually want to make active, then when done Set it to the Activenode to it as above.


Comment by: Thomas Koester (25-4-2013 13:01:37) deeplink to this comment

Thanks a lot for making this available. That's what I need to make my tool work under 64 bit.

I need to incorporate that in Access. Do I assume correctly that the Acess version is six builds behind?

Another question: The HostProject constant is gone in build 23. Have you dropped the approach for a universal control (Excel and Access)?

Comment by: Jan Karel Pieterse (25-4-2013 14:12:55) deeplink to this comment

Hi Thomas,

We're still making lots of changes to make the treeview work seemlessly in Access. Especially optimising the tool for performance proved necessary. The Access version (017)available for dowload today is significantly slower in loading the same # of nodes than it is in Excel. The version we're working on is equally fast, but slightly more complicated to implement (it is housed on a userform).

The method displayed in the current Access download is being changed significantly.

One of the things we're trying to keep is a single version for the different applications.

Comment by: Thomas Koester (25-4-2013 16:12:46) deeplink to this comment

Hi Jan,

thank you for the quick answer. Right now I tweak the Excel build 23 to make it work in my Access app. Should I better stop and wait for the updated Access release or will the interface be more or less the same than in Excel(23)?

Comment by: Jan Karel Pieterse (25-4-2013 16:14:39) deeplink to this comment

Yes, it is better to stop.

I'll ask Ben to send you a copy.

Comment by: A Programmer (29-4-2013 07:27:43) deeplink to this comment

Sorry bad English.

In Access DEMO file your treeView can not be scrolled by mouse or by keys UP and DOWN. Clicking mouse on a scroll bar does not affect at all. When press DOWN get an error message: "Run-Time Error 438 Object does not support this property or a method."
module: clsTreeView
method: Public Sub ScrollToView(Optional cNode As clsNode, Optional Top1Bottom2 As Long)
line:


With TreeControl
        sngVisHt = .Height <<< Error Line
        If .ScrollBars = fmScrollBarsBoth Or .ScrollBars = fmScrollBarsHorizontal Then
            sngVisHt = sngVisHt - 15    ' roughly(?) width of horizontal scrollbar
        End If

MS Access version: 2003 SP3

Excel Demo works OK.

Comment by: Mike (29-4-2013 09:44:14) deeplink to this comment

Hi,

the treeview works great but the scrollbars doesn't work :-)
How can i fix this problem?

Thank you very much.

Mike

Comment by: Jan Karel Pieterse (29-4-2013 14:12:43) deeplink to this comment

@A Programmer: We're working on an update whish should work a lot better in Access. Stay tuned!

@Mike: What do you mean, they don't scroll using the mouse wheel? or do they fail when you drag them?

Getting the mouse wheel to work is possible, though cumbersome, which is why we did not include it (yet?).

Comment by: Steve (29-4-2013 22:23:30) deeplink to this comment

Hi, I am eagerly awaiting your release of the Access version of the Treeview "control". Am desperate to overcome limitations of Office 64-bit with Windows Common Controls.

Is it best to monitor this website, or can you notify by email?

Do you have any plans for a Listview Common Control replacement?

Comment by: Jan Karel Pieterse (1-5-2013 13:40:30) deeplink to this comment

Hi Steve,

I'd probably forget to do the notifications, so best is to watch this spot indeed.

We're not planning to do a listview, no.
At least not yet :-)

But the techniques we used here may very well be used in a similar fashion to create a listview.

Comment by: mGRIFO (19-5-2013 03:56:20) deeplink to this comment

First of all, sorry for my worst english
I' d like to now if you are think creating a treeview in a listbox

Comment by: Jan Karel Pieterse (20-5-2013 12:19:11) deeplink to this comment

Hi mGRIFO,

Can you perhaps try to explain why you would want that?

Comment by: Roy Lasris (22-5-2013 14:26:06) deeplink to this comment

I tried to assign a double click action, but was not successful. I would be using this for navigating down folder trees and a double click would result (hopefully) in a redraw of the screen with a new top level. Possible?

(There is a board dedicated to 'slamming' Microsoft from not supplying on its own a replacement for TreeView (comctrls). Is it okay to post a link to your solution there? I didn't see it there last time I looked.)

Comment by: Jan Karel Pieterse (22-5-2013 17:07:09) deeplink to this comment

Hi Roy,

I guess what you need should be possible indeed.

We have currently used the dblclick event in the clsNode class to enable editing of a node. You would have to change that logic there. Then you can use logic similar to the event "mctlControl_Click" in that same class to create an event that responds to a doubleclick.

Could you please share the link to that board you refer to?

Comment by: Roy Lasris (22-5-2013 17:24:09) deeplink to this comment

Could you please share the link to that board you refer to?

Here is one of the links, but there are others. Will you post, or do you want me to?

http://qa.social.technet.microsoft.com/forums/en-US/officesetupdeployprevious/thread/f3a71d69-580b-4cdb-ae0b-d8189e6cfd40

Comment by: Jan Karel Pieterse (22-5-2013 17:45:17) deeplink to this comment

Hi Roy,

Thanks for the link. If you could please go ahead and post a message there that would be great!

Comment by: Roy Lasris (24-5-2013 11:45:46) deeplink to this comment

Great VBA project! Thanks.
Still a little hung up on creating a 'personalized' DblClick event on the node.

I have set EnableLabelEdit = False.

When I double click a node, the program goes to the class routine for double-click. I cannot find a way to program anything for the node at the module (as opposed to class) level.

I can probably add the desired 'dblclick' action (if EnableLabelEdit is false) at the class level, but (1) I really shouldn't be programming at the class level because (2) the double click action in one user form is not going to be the same action from one user form to the next.

Is there a way to trigger a node DblClick event (and respond to module level coding) at the module/form level?

Comment by: Roy Lasris (24-5-2013 12:42:56) deeplink to this comment

I found a solution. I edited the class module so that if EnableEditLabel is false, a specific routine within the userform is called. That keeps editing in the class to a minimum.

Comment by: Jan Karel Pieterse (24-5-2013 12:55:38) deeplink to this comment

Hi Roy,

To add a doubleclick event to the project you can do the following:

1. Edit mctlControl_DblClick in clsNode. Add a line like this one:

moTree.NodeDoubleClick Me


2. Edit clsTreeview and add the following items:

In the declaration section at the top of teh class, add:

Event DoubleClick(cNode As clsNode)


Anywhere convenient, add:

Friend Sub NodeDoubleClick(ByRef cNode As clsNode)
    RaiseEvent DoubleClick(cNode)
End Sub


3. Now you can add a mcTree_doubleclick event in the userform, e.g. like this one:

Private Sub mcTree_DoubleClick(cNode As clsNode)
    MsgBox cNode.Caption
End Sub

Comment by: Claude Berman (27-5-2013 05:17:23) deeplink to this comment

Thanks for posting this, I'll give it a try. In return, if you'd like it, I'll share code I developed that allows one to overcome the Access limitation that attachments cannot be appended (moved) from one table to another. It does it by looping through the recordset, then looping through the attachment field in the "donating" table and if it finds and attachment, it saves to disk, then loads from disk into the other table. I needed it to allow synchronization of inspection records which are collected off-line on slate or tablet and then moved to the main database during sync.

Comment by: Roy Lasris (27-5-2013 20:32:24) deeplink to this comment

Jan, I added the below line in the declaration section at the top of clsTreeview (and followed your other instructions).

Event DoubleClick(cNode As clsNode)

The new doubleclick operation worked great in Word2003. In Word2010 and Word2013, I got a critical error that did not go away until I added 'Public' in front of Event:

Public Event DoubleClick(cNode As clsNode)

Of course, Event is by default 'public' (or so say the Help systems), but I thought I would report the 'failure' anyway. (I don't know what happens in Word2007. That computer is elsewhere.)

Everything is up and running PERFECTLY. Thanks for your guidance, and for a wonderful program.

Comment by: Jan Karel Pieterse (27-5-2013 21:00:10) deeplink to this comment

Hi Roy,

Thanks for letting me know (and you're welcome!)

Comment by: Jan Karel Pieterse (28-5-2013 15:49:59) deeplink to this comment

Hi Roy,

Regarding Roy's post -Roy Lasris (5/27/2013 8:32:24 PM)- :

Roy discovered that the problem is not whether or not the Event line is made Public, but rather that all lines declaring Events MUST come AFTER all lines declaring variables WithEvent.

Comment by: Greg Naxet (30-5-2013 23:34:14) deeplink to this comment

Hi,

I'm attempting to utilized (my head is still spinning ;-))
your custom treeview control in a project that I need to use in Word 64 byte. I'm getting close thanks so much.

One question for now:

I my old code I could programatically select a treeview item:

Me.TreeView1.Nodes(4).Selected = True

I can't figure out how that is done using your control. Can it be? If so, can you tell me how?

Thanks and the head spinning remark was meant to be complimentary!!

Comment by: Jan Karel Pieterse (31-5-2013 12:09:50) deeplink to this comment

Hi Greg,

You can set the Checked property of a node.

The Excel download of the treeview contains a lot more documentation than the Word example. You may want to download that one.

Comment by: Peter Thornton (2-6-2013 13:29:44) deeplink to this comment

Hi Greg,
Just to add to what Jan Karel suggested, the equivalent of

Me.TreeView1.Nodes(4).Selected = True

in our VBA treeview is
Set mcTree.ActiveNode = mcTree.Nodes(4)


Unless you are keeping very careful track of the index numbers of all your nodes, rather than .Nodes(4) set a reference to your particular node in one of several ways (see the documentation in the Excel download). You might also want to ensure the new Active node is both visible (it's parents are expanded) and is scrolled within view, eg

Set mcTree.ActiveNode = myNode
mcTree.ScrollToView myNode







Comment by: Jan Karel Pieterse (5-6-2013 08:01:37) deeplink to this comment

Hi Billy,

Thanks, problem solved.

Comment by: Steve (13-6-2013 18:41:35) deeplink to this comment

Hi I've been monitoring your site for some time now, with hopes of an Access Treeview release. Is this still being developed?
Thanks, Steve

Comment by: Jan Karel Pieterse (14-6-2013 15:01:35) deeplink to this comment

Hi Steve,

Third download link above perhaps?

Comment by: Andy Litchfield (16-6-2013 14:37:58) deeplink to this comment

Perfect! I'll be a treeview expert by the end of the day... Thanks very much

Comment by: Jeff Silcock (20-6-2013 15:18:13) deeplink to this comment

I have a project in Access 2010 which uses treeview to display a list of nested reports. This, of course, no longer displays in Access 2013, so am very interested in your solution. However tried importing the various modules and the subTreeView form into a new DB, set references to Forms 2.0 Object Library, but am getting compile error on

Private mUF As ufTreeView
'User Defined type not defined' I notice your VBA has a Form 'ufTreeView' but can't see how to get that into my DB. Glad of your help

Comment by: Jan Karel Pieterse (20-6-2013 16:34:51) deeplink to this comment

Hi Jeff,

Did you press the "How Do I?" button on the form of the demo mdb? It describes how to use the treeview in Access better than this page does.
You can insert the userform by running some code that is in the ModStartup module

Comment by: Jeff Silcock (20-6-2013 17:02:12) deeplink to this comment

Ah! yes, sorry I missed that bit of info. The form is now in, so I will continue with my test. Thanks I will let you know how it goes.

Comment by: Rasmus (26-6-2013 18:44:20) deeplink to this comment

Hi and thanks for creating this great 'add-in' :),

I was worrying about the forward compatibility with the control i newer versions of Excel so this is spot on.

I would like to use your code to build af treeview based on a hierarchy, but I'm having trouble converting your original code to a 'variable' based one.

I have X columns (hierarchy level) with lots of duplicate values in the Y rows (persons). I sort the table (if there are three columns, I sort all three), so I can loop through each column and add nodes accordingly, without adding duplicates and getting runtime errors. I can add the root node and the first set of branches, but I fail to add level 2 and below. I need to refer to the former created branches but I cannot create n variables to store the keys - so I need something else. I tried referring to the cell value to the left, but that doesn't work. Would you help me out with a tip to get me started adapting the code to my needs?

The data is arranged, so a person has hierarchy lvl1 in the first column, lvl2 in the second, e.g. "Sales" and "Sales East", and so on.

Many thanks in advance :)

Comment by: Jan Karel Pieterse (27-6-2013 07:33:24) deeplink to this comment

Hi Rasmus,

Check out the NodeAdd method of the Tree class, it allows you to specify the key of the parentnode of a node you are adding.

Comment by: Rasmus (27-6-2013 16:24:58) deeplink to this comment

Just a comment with a big THANK YOU to Jan for helping me adjust the excellent Treeview code to my exact needs. It works perfectly now and I can tailor it do much more than what was possible with the MS treeview control. Class act providing such an extensive resource free of charge to everyone, highly appreciated. Respect and gratitude.

Comment by: Jeff Silcock (27-6-2013 17:43:20) deeplink to this comment

Just to say this is a brilliant replacement for treeview. I have it working with 4 node levels, and drawing all the structure from one table. This table has parent and child fields to enable you to select each level as you populate the tree.

One question though is how to initially display the tree in the collapsed state. I will be glad of help on this.

Regards

Jeff

Comment by: Jan Karel Pieterse (28-6-2013 11:50:39) deeplink to this comment

@Rasmus: Thanks!

@Jeff: You can use the ExpandToLevel method of the clsTreeview class instance to force the tree to expand to a certain level. Also you can change the expanded property of an individual node:

cNode.Expanded = false

Comment by: Jeff Silcock (28-6-2013 17:15:52) deeplink to this comment

Yes that's done it. Added

cNodex.Expanded = false
immediately after the code to add the node (where x = node level) and it works a treat! Much appreciate the work you have done on this.

Regards

Comment by: José (8-7-2013 14:31:58) deeplink to this comment

Dear Sir

I am a curious interested in Access. I installed your TreeView demo. It aborts with a message : Type incompatibility.


Note that I use a French version of Office.

Regards

José

Comment by: Jan Karel Pieterse (8-7-2013 17:30:58) deeplink to this comment

Hi José,

I'll ask my colleague who has more knowledge of Access to contact you.

Comment by: Rasmus (10-7-2013 10:08:37) deeplink to this comment

Hi Jan,

Is it possible to access the treeview on a multipage control in a userform? I have tried the standard syntax: Me.multiPage1.Page(1).frTreeview - but nothing happens.

Regards

Rasmus

Comment by: Rasmus (10-7-2013 16:15:19) deeplink to this comment

Hi again Jan,

Regarding my comment today on Multipage - I'm really sorry, but the error was not caused by the Multipage, but strangely a lack of using "Thisworkbook." in first in a range reference. I apologize for the inconvenience!

Regards

Rasmus

Comment by: John (26-7-2013 14:44:04) deeplink to this comment

Hi Jan. I am implementing your excellent TreeView in Word 2010 with some success and I wanted to thank you. I was also wondering if you have any news on drag and drop functionality or any quick tips to point me in the right direction. I can drag a node's key out of the tree (e.g. to a textbox) but I cannot see how to drop it to another node in tree. I am missing something obvious... Any help would be very much appreciated.

Regards, John.

Comment by: Sherry Massey (30-7-2013 20:29:05) deeplink to this comment

Jan,

I built an Access application that is driven entirely by a treeview interface. It worked well on my 64-bit workstation and died a swift death on the 32-bit tablets it is being deployed on. While drying my tears and searching for an alternative, I found your site.

Your TreeView has worked perfectly, so far. I cannot thank you enough.

With gratitude,

Sherry

Comment by: Peter Thornton (1-8-2013 16:57:08) deeplink to this comment

Drag & Drop can certainly and effectively be implemented in the Treeview. We thought best not to include it (yet) in the beta demos, partly as it’s quite tricky and updating to work with other changes in new versions was time consuming, and partly as ideally it’s best tailored to the particular application, though we’re happy to adapt it for clients. However John did contact me off-line and I understand he has now got Drag & Drop working for his needs.

Comment by: Fernando Santos (7-8-2013 16:51:56) deeplink to this comment

I was wondering if you plan to do the same for listview control...

Comment by: Jan Karel Pieterse (8-8-2013 20:57:04) deeplink to this comment

@Fernando:

No, there are no plans for a listview control (though I have made some feeble steps in that direction, but I lack the time currently).

@Sherry: Thanks!

Comment by: Dieter Samel (14-8-2013 16:08:45) deeplink to this comment

Hi Jan,
I need urgently a way for drag and drop in the tree.
Can you show me a useful approach.

Sorry, my english is not the best. But I could German well :)

Comment by: Tony Matyas (19-8-2013 16:48:23) deeplink to this comment

Hi Jan,
a wonderful piece of work.

Couple of Questions:
a) What can I do in the class modules to show the UserForm in a modal way - the DemoNow procedure results in an error message, when I simply try to change '.Show' to -> '.Show (Modal)'.
b) Is there any limitation to the number of TV nodes?

Suggestion for a possibly new release:
Multicolumn treeview, e.g. Tag | Key | Value,
editable by default: only Value (DblClick/F2). Would be a nice XML-Editor, for instance!

Thank you in advance for any helpful info
TM

Comment by: Don Maskew (20-8-2013 02:13:15) deeplink to this comment

Hi,

I'm using your treeview in Access 2013 32/64 bit. Great to get around the 32/64 bit problem.

But...I can't get the ActivateNode to work. I want to activate a node given it's key. Is that possible, if so, can you give me an example syntax?

Thanks
Don

Comment by: Jan Karel Pieterse (20-8-2013 08:52:58) deeplink to this comment

Hi Don,

I guess you could add this to the clsTree class:

Public Sub ActivateNodeByKey(sKey As String)
    Dim cNode As clsNode
    For Each cNode In mcolNodes
        If cNode.Key = sKey Then
            Set ActiveNode = cNode
            Exit Sub
        End If
    Next
End Sub

Comment by: Jan Karel Pieterse (20-8-2013 08:53:50) deeplink to this comment

Hi Tony,

The userform is already shown modal, so I guess I nmay not grasp what you are trying to do?

Comment by: Mark Scicluna (1-9-2013 11:21:41) deeplink to this comment

Hello Jan

This is just what I have been looking for. I ran into a lot of problems with our current application to deploy on multiple Windows and Office versions because of the MSCOMCTL.OCX. Your code works brilliantly.

I have to now integrate this code into our .ADP application. This uses Access 2002 - 2003 format. When I converted the downloaded Access Demo from 2000 to Access 2002 - 2003, the form did not load :(. I got a runtime error 2147286785 - Invalid flag error.
The line of code on which this error was produced is:


    Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)

in the Load_Form event of subtreeview.

Would you have a work around for this?

Thanks very much in advance and keep up the great work!

Comment by: Jan Karel Pieterse (1-9-2013 20:12:22) deeplink to this comment

Hi Mark,

You probably have to remove the userform altogether and use the code in the example file to re-create it using Access 2000/2002.
Not sure though, I did not create the Access demo myself.

Comment by: Richard Tawn (11-9-2013 12:10:30) deeplink to this comment

Hi,
the treeview is great, I have made a few minor adjusts to the behavior to suit my needs, couple of questions, could you help I am trying to pass the mouse button to the Click event so "mcTree_Click(cNode As clsNode)" would be "mcTree_Click(cNode As clsNode, byval Button as integer)"
also is there method to set checkboxes ?

Comment by: Jan Karel Pieterse (11-9-2013 16:30:46) deeplink to this comment

Hi Richard,

To change the signature of the Click event, find this line in the clsTreeview class:

Event Click(cNode As clsNode) 'Node clcick event


and modify it like so:

Event Click(cNode As clsNode, Button as Integer) 'Node clcick event


Then also change all occurences of:

RaiseEvent Click(cNode, Button)


and also change this:

Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode)


To:

Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode, ByRef Button As Integer)


Now make sure that you add that button variable to all instances of NodeCLick in both classes.

However, the mctlCheckBox_Click event that is supposed to trigger these events does not return the mousebutton. For that, you would have to use the MouseUp (or down) event instead.

Comment by: Justin Glasgow (11-9-2013 22:52:33) deeplink to this comment

Hello,

I had a suggestion. One addition that I made was the "ControlTipText" property to clsNode. This would allow the user to add text that would appear while hovering over a node.

-Justin Glasgow

Comment by: Jan Karel Pieterse (12-9-2013 10:44:04) deeplink to this comment

Hi Justin,

Good point!

Comment by: Richard Tawn (12-9-2013 11:01:46) deeplink to this comment

Jan,

Thank you very much for the response and your time, I am pretty much at the same point I was at yesterday, things halt in
"Private Sub mctlControl_Click()" in clsNode
with
"Variable not defined"
at
moTree.NodeClick Control, Me, Button

Adding Button as integer to declarations returns "0" to the event

Regards, Richard

Comment by: Jan Karel Pieterse (12-9-2013 11:11:34) deeplink to this comment

Hi Richard,

The point is, that the click event does not give you the mousebutton. You need the MouseUp or MouseDown event of the control (which you can add by clicking inside the click event in the clsNode class and then clicking the right-hand dropdown at the top of the code window). From there you can call an event in the clsTreeview class whilst passing the button property.

Comment by: Richard Tawn (12-9-2013 14:55:00) deeplink to this comment

Jan,
got it !
declared "private btn as integer" in clsNode,
added "btn = button" to mctlControl_MouseDown,
finally "moTree.NodeClick Control, Me, Button"
becomes "moTree.NodeClick Control, Me, Btn"
in mctlControl_Click()" .
voila
for anyone else after the same functionality

Regards Richard

Comment by: Jan Karel Pieterse (12-9-2013 16:29:25) deeplink to this comment

Hi Richard,

That is one way of doing it indeed. Note though that when one is using the keyboard to navigate the tree, the click event may also fire, which might mean the value of btn might be off.

Comment by: Greg Maxey (13-9-2013 19:50:49) deeplink to this comment

Hi,

I've recently encountered a problem using your treeview control.

The problem results in a run time error duplicate key when the code hits the third line shown below:

In your Public Function AddChild of your clsNode

With moTree.Nodes
        If Len(sKey) Then
100         .Add cChild, sKey


I use the treeview control to display the contents of a CustomXMLPart in a Microsoft Word project.

I may have a node with an xPath "...Apples[1]" and another node with the xPath "...APPLES[1]" The first node passes the code above without problems, however when attempting to add the second node the duplicate key error is thrown. It seems the control does not see a difference between "Apples" and "APPLES."

I'm not sure what kind of compare you key is using, but is there some way to modify your code so the key compare would distinguish this difference?

I've stetted this line:
100         .Add cChild, sKey

as follows:
100         .Add cChild ', sKey