Most Valuable Professional

View Jan Karel Pieterse's profile on LinkedIn subscribe to rss feed
Subscribe in a reader

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


Excel VBA Masterclass (English)
Excel VBA for Financials (Dutch)

Third party tools

Speed up your file

The best tool to optimise your Excel model!

What has changed?

PerfectXL Compare, for Spreadsheet Comparison
This Excel compare tool is fast, reliable, and easy to use. Compare two Excel files now and see for yourself!
Home > English site > Articles > Treeview control

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


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 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 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 was tested (and works) on 64 bit Office. It also works on MAC office. Tested applications include:

Excel 2000
Excel 2003
Excel 2010
Excel 2010 (64 bit)
Excel 2011 (MAC Office)
Excel 2013 (32 bit)
Excel 2016 (32 and 64 bit)

Access 2003
Access 2010 (32 bit)
Access 2010 (64 bit)
Access 2016 (32 bit)

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


The basic plumbing and code structure of this treeview control was devised by me. However, without the help of my friend and fellow 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!

Copyright and Licensing

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 have some rules:

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.


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.


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:


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, 15 Oct 2015, downloaded 38.958 times)

Download the treeview sample Word document (build 026, 15 Oct 2015, downloaded 12.315 times)

Download the treeview sample Access database (build 026, 16 Oct 2015, downloaded 31.804 times)

‘Pro’ Treeview and ListGrid VBA controls

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 proven 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.



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.


All comments about this page:

Comment by: Benzadeus (22-2-2013 23:12:43)

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)

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)

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)

Hi Jeff, Benzadeus,


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)

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
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

Case vbKeyDelete
    mcTree.NodeRemove cNode

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

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


Comment by: Jeff Weir (23-2-2013 19:59:14)

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)

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

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)

# 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
        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")

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)

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)

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)

Stupendously excellent ultra mega hyper Good!!

Love it!



Comment by: Jan Karel Pieterse (3-3-2013 19:37:51)

Hi Lisa,

Thanks! :-)


Comment by: Stefan Hellwig (6-3-2013 15:26:48)

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)

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)

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)

Hi Hermanito,

Thanks for reporting!


Comment by: Peter Thornton (8-3-2013 17:10:03)

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)

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)

Hi Comet,

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


Comment by: conim (2-4-2013 12:04:45)

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)

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)

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,


Comment by: Jan Karel Pieterse (3-4-2013 12:01:23)

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
        End If
        Set FindNode = cFoundNode
    End If
End Function


Comment by: Peter Thornton (3-4-2013 13:29:40)

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)

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)


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.
     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.



Comment by: Jan Karel Pieterse (4-4-2013 09:08:55)

Hi Alex,

Thanks a lot for your comments, much appreciated!


Comment by: Peter Thornton (4-4-2013 13:04:01)

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)

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)

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)

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)

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)

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
    Set .ActiveNode = cNode 'refers to a clsNode
'or in the demo
    Set .ActiveNode = .Nodes("2.1.1")

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)

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)

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)

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)

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)

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)

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)


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

Thank you very much.



Comment by: Jan Karel Pieterse (29-4-2013 14:12:43)

@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)

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)

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)

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)


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


Comment by: Roy Lasris (22-5-2013 14:26:06)

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)

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)

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?


Comment by: Jan Karel Pieterse (22-5-2013 17:45:17)

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)

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)

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)

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)

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)

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)

Hi Roy,

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


Comment by: Jan Karel Pieterse (28-5-2013 15:49:59)

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)


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)

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)

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)

Hi Billy,

Thanks, problem solved.


Comment by: Steve (13-6-2013 18:41:35)

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)

Hi Steve,

Third download link above perhaps?


Comment by: Andy Litchfield (16-6-2013 14:37:58)

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)

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)

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)

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)

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)

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)

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)

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.




Comment by: Jan Karel Pieterse (28-6-2013 11:50:39)

@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)

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.



Comment by: José (8-7-2013 14:31:58)

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.




Comment by: Jan Karel Pieterse (8-7-2013 17:30:58)

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)

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.




Comment by: Rasmus (10-7-2013 16:15:19)

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!




Comment by: John (26-7-2013 14:44:04)

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)


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,



Comment by: Peter Thornton (1-8-2013 16:57:08)

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)

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


Comment by: Jan Karel Pieterse (8-8-2013 20:57:04)


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)

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)

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


Comment by: Don Maskew (20-8-2013 02:13:15)


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?



Comment by: Jan Karel Pieterse (20-8-2013 08:52:58)

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
End Sub


Comment by: Jan Karel Pieterse (20-8-2013 08:53:50)

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)

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)

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)

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)

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)


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)


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)

Hi Justin,

Good point!


Comment by: Richard Tawn (12-9-2013 11:01:46)


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
"Variable not defined"
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)

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)

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()" .
for anyone else after the same functionality

Regards Richard


Comment by: Jan Karel Pieterse (12-9-2013 16:29:25)

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)


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

and I no longer get the error and everything "seems" to work ok, but I am not familiar enough with the wizardry you've put together here to know or understand the consequences.

Thank you very much. I can send you my project with instruction for creating the sitution causing the error if you wish.    


Comment by: Adrian Price (15-9-2013 14:34:47)

I want to add my name to the numbers of people that would like you to produce a ListView control :)
I do hope that you find the time to produce it - you must have spent hours on the TreeView control which I would like to use.
Many thanks for the work you have done.


Comment by: Jan Karel Pieterse (16-9-2013 06:37:39)

Hi Greg,

I'm afraid not much can be done, since we're using the add method of a collection with a key if you provide one. The add method is case insensitive unfortunately.

You can try changing the code in the NodeAdd sub in clsTreeview so it does not use the key (linenumber is indicated and equals 100) when adding the new node to the collection, it will still add the key to the clsNode class instance.


Comment by: Peter Thornton (16-9-2013 16:07:01)

@ Justin
Simulating a ControlTipText window is not simple, if you mean say to show a borderless/captionless form near the node or mouse. That said it can be done with APIs. A different approach to force user to select from a choice might be to show a dropdown (combobox) over the node, populated with choices.

Much of the code to do that can be adapted from the “edit-box” code (the textbox that appears if you double click or press F2). It would be straightforward to add a node property to store an array of choices for the combo or pick up from elsewhere depending on selected node.

Instead of working with the click event could you just simply use the mouse-up event for your purposes. As Jan Karel explained the mouse-up exposes the button directly (as does mouse-down). Typically there’s little effective difference in timing between click & mouse-up events, but generally better to use mouse-up. Reason, mouse-down “provisionally” selects the new node, but it's only fully activated if mouse is released while still over the node, IOW if user drags mouse off the node and releases mouse the previous active node is restored without exposing an unwanted click event. As Jan Karel flagged consider use of the keyboard and equivalent events.

If you don’t particularly need Keys there’s no need to add them (they're optional), and best not if there’s a risk of attempting to add duplicates (unless deliberately testing). However if you want to include your Apples & APPLES as additional data you could use the node’s Tag property. Thereafter you could loop and search (case sensitive) all nodes for a given case sensitive Tag and in turn retrieve your node.


Comment by: Jan Karel Pieterse (16-9-2013 18:33:32)

@Peter: You misunderstood :-)
Adding a controltiptext is straightforward, we're working on it.


Comment by: Peter Maclaurin (23-9-2013 00:54:22)


Just found your control and it looks just what I am looking for to replace the Microsoft Treeview in my Access application.

I have got the code to work in Access 2013 and managed to get it to read Major and Minor Categories from tables in the database (just two levels) with checkboxes. I'm now trying to set the checkboxes by code (ie. reading in from another table which were previously selected and saved - saves the unique key of the Minor Categories) and then testing the checkboxes on exit to see if any have changed and saving the changes (if necessary). I just can't figure out how to do this. I'm sure it's simple to a lot of people but I'm brain dead on this.

Great control by the way!


Comment by: Jan Karel Pieterse (23-9-2013 06:37:39)

Hi Peter,

Not sure if this is what you're after, but the clsNode class has a Checked property you can interrogate. Also, the clsTreeview class has a nodes collection. So:

For Each cNode in mcTree
    If cNode.Checked Then
        'Do something
    End If


Comment by: Peter Maclaurin (23-9-2013 10:48:40)

Hi Jan,

Not sure what I'm doing wrong. I have attached the code to the click event of a button on the form and added:
Dim cNode as clsNode
What I get is Run-time error '438'
Object doesn't support this property or method


Comment by: Jan Karel Pieterse (23-9-2013 11:25:19)

Hi Peter,

Hmm, my code should have read:

For Each cNode in mcTree.Nodes
    If cNode.Checked Then
        'Do something
    End If


Comment by: Peter Maclaurin (23-9-2013 11:33:37)

Hi Jan,

Works perfectly in my test database! Now to try to incorporate it into my database.

Thanks for everything.



Comment by: Richard (30-9-2013 12:18:08)

Might be a big ask but... is there any possibility to add functionality to clsNode to enable/dissable a check box for the particular set node ?




Comment by: Jan Karel Pieterse (30-9-2013 13:47:14)

Hi Richard,

You're right, it is quite a bit of work, as it involves:

- Adding the property
- changing the checkbox color to denote a disabled checkbox (if you're not using icons)
- Adding pictures for a disabled checkbox so it is visible to a user that checkbox is unavailable (if you're using icons) and making sure the correct picture is shown.
- Adding a reference to that new picture
- Checking for the property in the click event of the checkbox so we can cancel the click where needed


Comment by: Richard (30-9-2013 14:42:01)

Actually all im after is to set a property during populate that determines before adding a node weather a checkbox is visible or not for said node, as I am not using images im not bothered about that side of things, what im saying is i want the checkbox completely absent for certain nodes and visible for others, for example setting or forgetting msngChkBoxPad before setting a node ? not sure if im in the correct place or not ?




Comment by: Richard (30-9-2013 17:45:23)

Done it ! I added a property to clsnode called exp
changed the node poplulate if statement to if exp etc
I removed the checkbox function from root as i did not require it

Cheers for your work sir !


Comment by: Peter Maclaurin (1-10-2013 13:30:18)

Hi Jan,

I have successfully incorporated the treeview into my database but I have one form that used to have both a treeview and a listview (both with checkboxes). I have replaced the treeview with your one but the listview is now the problem. I could also replace it with your treeview but I'm not sure if it is possible to have two on the same form?

Regards Peter


Comment by: Jan Karel Pieterse (1-10-2013 15:19:48)

Hi Peter,

Yes, having two should be possible. Probably best to put them both on the userform, but I have not enough Access forms knowledge to know this. I'll ask Ben, who enabled the treeview to work on Access.


Comment by: Ben (1-10-2013 15:24:40)

Peter -

Yes, it is possible - you would simply put down two subform controls, and set each subform's SoureObject = "subTreeView". Though this would point to single form named "subTreeView", it will be instantiated as a 2nd object and you can manage each subform and therefore the treeview independently of each other.


Comment by: Peter Maclaurin (2-10-2013 11:24:05)

Hi Jan and Ben,

Took me a little while to figure out how to initialize both Treeviews on the same form but I can report it does work. Many thanks for that.


Comment by: Peter Maclaurin (7-10-2013 12:50:00)

Hi Ben,

One further question. The tree is on one form and I need to be able to read through the nodes, getting their description if their checkbox is checked, from another form, which then adds these node description into a listbox on it. I do check to see if the form with the tree on it is open prior to trying to read the tree. This is the Listview control that I have replaced with your Treeview control. The old code used to read:
If IsLoaded("frmSales") Then
    For i = 1 To Forms!frmsales!ListView0.ListItems.count
     Me.lstFields.AddItem Forms!frmsales!ListView0.ListItems(i).Text _
     & ";" & Forms!frmsales!ListView0.ListItems.Item(i).SubItems(1)
    Next i

Hope that makes sense.

Regards Peter


Comment by: Jan Karel Pieterse (7-10-2013 14:56:10)

Hi Peter,

You should be traversing the Nodes collection of the treeview on the other form. They have a Checked property.


Comment by: Peter Maclaurin (8-10-2013 10:35:57)

Hi Jan,

Yes, I knew that but was having trouble working out how to get to the treeview from another form. I eventually managed to get a few brain cells to work and figured it out. In case anyone else has this problem the code looks like:
If IsLoaded("frmSales") Then
    For Each cNode In Forms!frmSales.mcTreeSalesFields.Nodes
     If cNode.Checked Then
Where IsLoaded is a function that checks if frmSales is loaded.

Many thanks for all your help. Peter


Comment by: Jan Karel Pieterse (8-10-2013 11:47:50)

Hi Peter,

Thanks. I assume this means mcTreeSalesFields should be declared as a public variable?


Comment by: Peter Maclaurin (9-10-2013 03:15:23)

Hi Jan,

Yes. If use the coding in the example you provided the TreeView is declared as a Public Variable in the declarations section. I just modified the name as I actually have two treeviews on that form.

Regards Peter


Comment by: Ben (10-10-2013 13:43:43)


Just as a FYI -

If you want to "early bind" your treeview variables, you could do so like this:

Dim frm As Form_NameOfMyForm

If IsLoaded("NameOfMyForm") Then
Set frm = Forms("NameOfMyForm")
For Each cNode In frm.mcTreeSalesFields.Nodes
End If

The advantage is that you have assurance from VBA compiler that the form's property exists and can be validated. This coding pattern is effective in a larger VB project where you might have many forms and treeviews to keep track of and don't want to get runtime errors due to form being not loaded or change to the forms.

You also can 'early-bind' a subform in like manner:
Dim sfrm As Form_NameOfMySubForm
Set sfrm = frm.MySubFormControlName.Form

You can then reference sfrm.mcTreeview and this also enable you to disambiguate two things that would from same subform loaded twice such as sfrm1.mcTreeview and sfrm2.mcTreeview. You then don't have to change name on account of loading it more than once.

I hope that helps.


Comment by: Peter Maclaurin (11-10-2013 11:11:54)

Thank you Ben. I agree that it is probably better to early bind.

Regards Peter


Comment by: Claude (20-10-2013 13:48:59)

Hi, and thanks for publishing this, it's worked beautifully for managing a large space inventory (customers > buildings > floors > rooms). I didn't notice the recent update and am experiencing some corruption issues that may be caused by an old common controls treeview I haven't gotten around to fixing. It may also be related to the sheer size of the application, and it may be hitting the limits, just haven't figured it out yet.

I do have one silly question, or at least I feel that way about my inability to figure it out. I'd like to load the tree (4 levels) but initially just have the upper three nodes expanded. I've searched on code for the word "expand", but can't figure out the syntax to collapse the a node level for initial presentation. Is there a way to do this?


Comment by: Jan Karel Pieterse (21-10-2013 09:33:23)

Hi Clause,

It is simple really. Nodes are expanded by default, so just for the nodes that you want not to be expanded:

cNode.Expanded = False


Comment by: Claude Berman (21-10-2013 13:18:11)

Thanks Jan, I tried that: cNode4.Expanded = False in various places within the procedure that builds the tree. I tried it:
1. Before the .refresh at the end
2. After the .refresh at the end
3. Immediately after the set cnode4= statement

And just can't get it to work in the Access code. Is there somewhere else I can try?


Comment by: Peter Thornton (21-10-2013 15:20:15)

Hi Claude,
Curiosity, could you give an idea of what you mean by "the sheer size of the application". How many nodes and how many controls are being created. If you have the "DebugMode = 1" compile constant in place (in project properties) the stats will appear on the form's caption.


Comment by: Peter Thornotn (22-10-2013 12:29:21)

Hi Claude,

About your expansion problem, if you want to show only the first three levels you need to set the expansion property of all nodes in the 3rd level, like when you click an expander on the 3rd level you show or hide its parents in the 4th level. So instead of cNode4 try
cNode3.Expanded = False

Also keep in mind internally the levels are numbered from zero for root nodes.


Comment by: Claude Berman (31-10-2013 15:07:03)

Thanks Peter, I'm having all kinds of fun with this thing, such as setting the colors of the last (detail) nodes according to state (green=inspection passed, etc.). Now, I'm wondering if there is a way to control the expansion level from outside the tree without actually rebuilding it. I'd like to have an option group with three or four toggle buttons meaning (account, building, floor, all) corresponding to the four nodes in the tree (disregarding the root). Thus, if the user clicks the toggle "building", the tree would expand all nodes higher and collapse all nodes lower. I could easily do this via rebuilding the tree (now that you showed the way), but in working with large data sets, it would help if there is a more efficient way. Thanks for all your free help, you really should sell this thing, it would be worth a reasonable sum.


Comment by: Jan Karel Pieterse (31-10-2013 16:34:16)

Hi Claude,

Thanks! Not sure I understand what you're after though.

Regarding selling the tool: you can always make a donation :-)


Comment by: Peter Thornton (31-10-2013 17:31:44)

Hi Claude,
If I follow you want to collapse all to level 0 (if you have multiple root nodes) or level 1, then you want all child and grandchild nodes of say "building" expanded. There are several approaches but try something like this, in your form code -

Sub ShowBranchOnly(cNode As clsNode)
    mcTree.ExpandToLevel 0 ' < change as required, eg 1
    Set mcTree.ActiveNode = cNode
    mcTree.ScrollToView cNode
    ExpandBranch cNode
End Sub
Sub ExpandBranch(cNode As clsNode)
Dim cChild As clsNode
    cNode.Expanded = True
    If Not cNode.ChildNodes Is Nothing Then
        For Each cChild In cNode.ChildNodes
            ExpandBranch cChild
    End If
End Sub

Pass a reference to your "building" node to ShowBranchOnly say in your toggle button. You may want to remove or adapt the Set ActiveNode and ScrollToView lines. See ScrollToView in the documentation for more about its optional arguments,

You mentioned before you have a "large" treeview, I'm interested to know how large. We have ideas to make the current treeview to handle a very considerably larger number of nodes but it means complete redesign and a lot of work!


Comment by: Claude Berman (31-10-2013 21:36:21)

Thanks Peter, and Jan I sent you guys a pitcher or two of Ale since you'll probably need it dealing with all my questions. Ok, how do I pass a reference to a particular node to this function? I have a tree (mcTree) sitting on the form. Sorry, I really need an Ale too.


Comment by: Claude Berman (31-10-2013 21:58:39)

Peter, I forgot to answer your question concerning size vs. performance. I have a test database with 8000 rooms (showing in the tree) and performance really isn't bad. This corresponds to a very large hospital or a mid-size university. But, I'm not sure how it run on a normal user's machine since I run a very fast workstation with solid state drives in a stripped array ( backs it up nightly since this is dangerous). It slowed down a bit when I added some formatting via conditions to change the final node color, but I think it's more the impact of the if/then/else statements to determine the formatting. To further confuse the issue, each room can have multiple inspections and I created a "demo" tool for distributors that generates any number of randomly scored inspection records. These are then used prior to processing the tree to determine which space inventory to show. In other words, select a particular inspector, then I've got to run the tree with a different set of queries. I'm also doing similar stuff to generate a chart on the same form. It takes a couple of seconds on my machine usually, worst case 5-10 seconds to rebuild the tree and paint the chart. I really don't think I'd need or want a ground-up rebuild since the real work and time is mostly in the pre-processing I have to do. And, blush, when I started 20 years ago my profession was mechanical engineering, so I didn't completely normalize the data, this propagated over the years as this app was developed, so I have to waste some CPU cycles normalizing the data on the fly.


Comment by: Peter Thornton (1-11-2013 15:33:50)

Re "how do I pass a reference to a particular node"
This is the type of thing you really need to know how to do:-) There are various ways, eg if you are using keys you could do this

Set cNode = mcTree.Nodes("myBuildingKey")
Call ShowBranchOnly(cNode)

But there are lots of other ways, eg if you maintain a reference to the parent node, eg the root node and if only one it's be simply .Nodes(1), you can loop its childnodes

' loop first level nodes
For each cNode in cRoot.ChildNodes
If cNode.Caption = "Building" Then
' you've found your node so bail out then pass cNode

Re size: 8000 nodes is pushing the treeview well beyond its design limits! If you've got a powerful system it might just be OK, but be sure to test it on equivalent systems your users might have.

That said even with 8000 there are things you can do to help, in particular do not show all nodes when building the treeview, IOW set appropriate nodes as .Expanded = False. However if/as user expands nodes and the number of visible nodes grows, scrolling and expanding will become clunky, especially in less powerful systems (note controls are only created the first time they are required and that exponentially takes time). Also do not show lines with such a large treeview (to reduce the number of controls).

I'm interested though in your "large" treeview. As I mentioned we have a new design for the treeview that should handle 8000 nodes very easily, even in old systems. As so much work has gone into this and it would only be of interest for a small number of professionals we are thinking of making it available as a special "pro" version, hopefully to recoup some of the development costs!

Hey thanks for the beer - cheers :-)


Comment by: Claude Berman (8-11-2013 13:34:29)

Hi and thanks Peter. I'm still stuck on this. In your second example, which is what I need since I'm trying to collapse the tree to a user-selected level (i.e. all node levels below a certain one - pick building from the option group and all buildings collapse so the only account and building nodes show:

My question is what is the syntax for reference the tree from outside the tree? I've tried everything I could think of, such as mcTree.nodes.cnode1, etc.? I'd composed a note the other day in which I asked if you would do this as an example for a fee. Jan has the email sent with download instructions and install instructions if you want to take a whack at it. But, perhaps I got distracted and failed to hit the submit button.

Otherwise, assume a reasonable price on an enhanced (pro) version, and you've got at least one customer. The tree itself seems to handle a large number of nodes quickly, I've slowed it down a bit with conditional expressions to determine formatting, and some expressions to calculate values (displayed in the tree itself), but even then, it's pretty quick. I have a partner application running on a lame Atom processor device used for inspections (an Acer W510 tablet). It synchronizes data and displayed the same inventory in your tree for selection of the room to inspect. It's the slowest device I have and, without any internal tree enhancements, such as expressions running during the tree-build loops, it displays the entire data set it a second or two.


Comment by: Dieter Samel (13-11-2013 18:16:48)

Hi Jan,

My problem is the same as:

Comment by Mark Scicluna (01/09/2013 11:21:41 AM)
Unfortunately, your recommendation does not:
You probably have to remove the userform altogether and use the code in the example file to re-create it using Access 2000/2002.

Could you send Mark my email address or post the solution?


Comment by: Jan Karel Pieterse (14-11-2013 06:34:37)

Hi Dieter,

I suggest you to open the Access demo file and click the "How do I...?" button on its main form. It has a lot of information on how to implement the treeview.


Comment by: Peter Thornton (14-11-2013 13:51:01)

I understand Jan Karel has contacted you and hopefully all is working for you now.

I'm surprised you can load and display 8000 nodes so quickly, particularly if lines are shown. But if so the current version should be fine for your needs and no need to 'pay' for our faster version! (still testing it)

@Dieter & Mark
We haven't tested in Access 2002 but I couldn't get it to work in 2000 even in a new project, at least not with the current internal arrangement. It works fine in 2003 but it seems only if built in 2003 (which is what the demo is compiled in).

IOW, you can distribute from compiled in 2003 to 2007+ but not (necessarily) from 2007+ to 2003. Not sure why but it seems to vary with different systems. There might be a simple trick for 2000/2 so if you or anyone figures it please let us know!

It is written up top only tested in Access 2003 and later. (Excel & Word all since 2000, Mac only tested in 2011)


Comment by: Dieter Samel (14-11-2013 15:04:05)

my approach :
System : Win 7 with off 2003 SP3
An empty 2003 mdb created.
All objects without the forms subTreeview , ufTreeView imported.
Then the following references to FM20.DLL and it created the new ufTreeView .
Then I also have the subTreeView imported.

The same error !

Analogous to :
Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True )
I have manually tried to insert a frame in the ufTreeView .
I got an error message:
Microsoft Visual Basic
! not implemented

The VBA code throws the following error: -2147286785 Invalid option

This works in all other development environments !

Appears frequently in conjunction with the FM20.dll a fault :
Not enough memory !

I put on the line during debugging
Set mfrTreeControl = mUF.Controls.Add ( " Forms.Frame.1 ", " frTreeControl ", True )
a different value for the number 1 a , the error changes to :
Invalid class string

It seems as if in this particular installation , the frame is not supported!

Translation with Google from the German


Comment by: Dieter Samel (15-11-2013 10:05:02)

Hello again,
A new installation with Win XP and Off2003 was created by me.
Now I'm sure the UserForm does not support FRAMES in MDB format MS Access 2002-2003.
In a 2002-2003-MDB I can not create a frame, and when I put it in a 2000 MDB, and then convert to the 2002-2003 MDB, leads the appeal or change the Frams errors!
As long as I'm on the same system using the same MDB than 2000 everything works. Unfortunately, I have but the 2003 format change to create an MDE!


Comment by: Peter Thornton (15-11-2013 11:28:08)

Hi Dieter,

Does your system with Access 2003 also include multiple and later versions of Office. If so there might be some sort of conflict.


Comment by: Dieter Samel (15-11-2013 13:16:51)

Hi Peter,

No my systems are pure virtual Fittings are installed with exactly 1 Office.
With friends adding frames is not even in the UserForm with a 2003 mdb with Off2003!

Incidentally created on this system 2003 MDE (yes, it is created) does not work there. But they can be used on any newer MS-Access without problems!

I have my clients now told him to install the runtime Acc2010!
Thus I will not provide support for this application in Off2003 first on 8 April ;o)


Comment by: TheSmileyCoder (18-11-2013 10:30:20)

Very impressive piece of work.

Would it be possible to make the node click event handler include arguments on which mousebutton was clicked?

I would also hope to see a guide on how to add my own icons to the treeview.

Great job! Thank you all for sharing.


Comment by: Jan Karel Pieterse (18-11-2013 13:20:18)

Hi TheSmileyCoder,

The default click event does not pass along mouse button states. Instead you should use either the MouseUp or the MouseDown event.
As it is now, in the MouseUp event we call the click event, which as I said does not pass mousebutton information. However, you can simply change that implementation by adding your own mouseUp event to the clsTree class and call that.

To find out how to add events, check out the clsTree class declaration section, there are 4 events set up there. Each one has a corresponding RaiseEvent codeline somewhere in the class.

This raiseevent line should be within a public sub in the class which in turn is called from the appropriate control event in clsNode.

Clear as mud?

Adding icons is very simple: add image controls to the (hidden) frame with icons, name them uniquely and use that same name when creating the nodes.


Comment by: TheSmileyCoder (18-11-2013 14:05:14)

Hi Jan
Thank you for your swift reply. I must admit, at present I have only been playing with your treeview, to see if it is a potential solution for when/if my organisation switches to Access 2013 or office 64bit. I run alot of applications that use the treeview as a main component. I shall try to play with both the icons and the events.

Do you have any idea on the long term availability of the MS form? My fear would be spending lots of time implementing your treeview as part of my app, only to see MS forms be removed in OfficeNext.


Comment by: Jan Karel Pieterse (18-11-2013 14:36:06)

Hi TheSmileyCoder,

I don't think anytime soon. msforms getting removed will only happen if VBA is omitted from Office. That is highly unlikely to happen soon.


Comment by: Sujoy (19-11-2013 09:27:21)

Hi jan,

It seems that the 'control' property of node has not been implemented properly. I cannot access any properties of 'node.control' like "MyNode.control.backcolor" etc.

Kindly suggest a way to do it. I am working upon a wonderful VBA Code Review tool. I may share it in this site after completing.

Sujoy (+91 8010189196)


Comment by: Jan Karel Pieterse (19-11-2013 11:09:59)

Hi Sujoy,

Timing is important. Before the treeview has been refreshed, none of the controls exist so the Control's properties can only be accessed After the Refresh command was done.

To set properties of a control when you are building the nodes, you need a different syntax. Instead of:

cNode.Control.BackColor = vbRed


cNode.BackColor = vbRed


Comment by: Duke Carey (26-11-2013 16:13:36)

Jan/Peter -

After years & years of using the MS common controls tree view, I slapped together a quick Excel file this AM using yours and have to say I'm very impressed. Performance while loading and then traversing a nearly 1,000 node tree was pretty darned good. I'll be using it a lot more in the future.

Kudos to you both for the quality of this utility and for freely sharing the work product of what had to be many, many development hours.


Comment by: Jan Karel Pieterse (26-11-2013 17:30:42)

Hi Duke,



Comment by: The Only Sheet (30-11-2013 18:16:07)

This control is awesome to replace the annoying version from Microsoft!

There are two functionality that the original control has, that I have not seen (please correct me if I am wrong - and please let me know where to look to to find this!)

A) Is it possible to have the Mouse Wheel 'scroll' the scroll bar? Is there an option to turn this ON?

B) The original control allows the used to type part of an word to 'jump' to the first occurrence (if any) of this word. This is VERY useful for long lists. Is this doable? Is it available already?

MANY THANKS for your work on this!!


Comment by: Jan Karel Pieterse (1-12-2013 19:41:48)

Hi The Only Street,

Very useful suggestions. Unfortunately, mousewheel scroll is quite hard to add. The other options shouldn't be too difficult.


Comment by: Seth (2-12-2013 07:42:23)

I tried this with Windows 8.1 and Access 2013 64-bit and it gets run-time errors. I know you haven't certified it for this, but I am wondering if you are working on getting it running in this environment.

The first runtime error occurs in Form_subTreeView in the Form Load. I have a comment below on the line that gets the run-time error (there is no TabIndex property for ctl).

If Not moSubControl Is Nothing Then
    moSubControl.OnEnter = "[Event Procedure]"
    moSubControl.OnExit = "[Event Procedure]"
    Set mcolTabControls = New VBA.Collection
    On Error Resume Next
    For Each ctl In moSubControl.Parent.Section(moSubControl.Section).Controls
    lngIndex = ctl.TabIndex ' RUN-TIME ERROR OCCURS HERE
    If Err.Number = 0 Then
        If ctl.TabIndex > mlMaxTabIndex Then
            mlMaxTabIndex = ctl.TabIndex
        End If
        mcolTabControls.Add ctl.Name, CStr(ctl.TabIndex)
    End If


Comment by: Jan Karel Pieterse (2-12-2013 16:26:43)

Hi Seth,

I'll ping Ben Clothier (he has done the Access integration work on the control), see if he knows what is going on (I don't have a 64 bit 2013 installation).


Comment by: Ben Clothier (4-12-2013 15:47:51)

Thanks for the comment, Seth.

There is something off here because if you look at the code, it's in a "On Error Resume Next" -- the fact you are getting a runtime error inside a Resume Next block suggests to me that there is something off with the VBE setting -- check your setting in the VBA editor:

Tools -> Options -> General tab

Ensure you've selected "Break on Unhandled Errors"

That should then let the code run through the Resume Next -- the error you saw is expected because not all controls have the TabIndex property which is why we're using the Resume Next & Err.Number = 0 to determine if we're going to add that control to the collection or not.



Comment by: Paul Sondervan (17-12-2013 17:17:41)

Is it possible to change add more space between the icon an the text?


Comment by: Jan Karel Pieterse (17-12-2013 18:13:58)

Hi Paul,

Sure, in clsTreeView, at the top there is a number of constants which control the looks, this one seems to be what you need:

Const mcIconPad As Single = 14.25


Comment by: Paul Sondervan (18-12-2013 09:08:03)

Hi Jan Karel,

I've adjusted the mcIconPad constant, even to a large value, but I didn't see any change.
After I changed FullWidth to true, it worked.
I would like to have it work when FullWidth = False.


Comment by: Paul Sondervan (18-12-2013 09:47:36)

Hi Jan Karel,

I have got a suggestion.
In clsTreeView the SpecialEffect is set to sunken.

        If bInit Then
            .SpecialEffect = 2    ' fmSpecialEffectSunken
        End If

It would be nice if that was an option that could be set by the programmer or that the special effect setting of subtreeview is used.
Just my 2 cents.

I really like your Treeview.
It's such a relieve that this just works.

I've been trying to get Treeview (MSCOMCTL.OCX) work in Windows 7 with MS Access 2013 for over a day.
I've used various version MSCOMCTL.OCX and didn't succeed.
Selecting an menu item didn't trigger the onclick event.
Very frustrating.


Comment by: Jan Karel Pieterse (18-12-2013 11:50:21)

Hi Paul,

Thanks for the comments and compliments, much appreciated.
Of course you can implement the suggested improvements yourself :-)


Comment by: Peter Thornton (18-12-2013 15:37:33)

Hi Paul,

Re Icon spacing: With the FullWidth option we're obliged to use a separate control for the icon, but it gives the advantage of being able to place the icon wherever we want. So for padding simply change the constant as Jan Karel suggested. But with larger treeviews with icons it's best not to use the fullwidth option, then the icon can 'share' the same control as the text, but we can't pad it. A workaround might be to add extra spaces before the text.

However, if treeview size and overheads is not the issue but you simply don't want fullwidth, continue to use the fullwidth option but comment anything that applies that full width value, eg

' .Width = mcFullWidth
' .AutoSize = False ' ensure autosize is always true

Re SpecialEffect: You could remove that altogether and configure the Frame's properties at design time.


Comment by: David Cushing (20-12-2013 08:14:29)

I have been in the IT industry for almost 40 years and have designed many databases using Access and several mainframe DBMs.
Since my semi-retirement this year, I have not been doing much coding and I found I missed it.
All of that is a verbose explanation of my reason for developing a genealogy database for my family :)
I was using Microsoft's Treeview control and I got it working reasonably well but while I was browsing for help with making it work really well I tripped across the discussion of the Treeview control at eileenslounge and that led me to your Treeview replacement.

Downloaded it and spent a couple of hours earlier tonight getting it to work in my database.
Probably the easiest re-write I have ever done; your code is clean, clear and intuitively elegant. Great Job!!!!

My questions are:
1. Can I trigger a refresh of your embedded subform from another subform in the same parent?
It was easy to use the Click event in your subform to modify the data in my subform but now I want to click a button in my subform and force an event in your so I can rebuild the tree from a new starting point.
I feel like this should be relatively easy but I have been up too long and it is escaping me.

2. Can I get the control to work in a report?

Thanks for developing this control and for any help you may be able to offer.


Comment by: Peter Thornton (21-12-2013 12:29:36)

Hi David,

Thanks for our comments. In answer to your questions -

1. All you need in your second form is a reference to the treeview, then you can control it exactly the same as in the main form. One way would be to start with this property in the main form

Public Property Get propTree() As clsTreeview
    Set propTree = mcTree
End Property

and in the second subform something like this

Private Sub Command0_Click()
Dim cTree As clsTreeview
    MsgBox Me.Parent.propTree.Nodes(1).Caption
    ' or probably better ensure it exists
    Set cTree = Me.Parent.propTree
    If Not cTree Is Nothing Then
        With cTree
            MsgBox "Nodes.Count " & cTree.Nodes.Count
            ' your code here
            ' cTree.Refresh
        End With
    End If

End Sub

Suggest do not make an semi permanent module level copy of the reference in the 2nd subform, get it as & when needed only.

2. I hadn't looked at putting a treeview in a Report but in a quick try it appears to be exactly the same as installing in a normal form. Working with a backup of the demo I did this
- Create a blank Report
- From the Design tab, Controls, subform/subreport
- in the wizard, 'use and existing form or report'
- I selected subTreeview and gave it a name (though you'll probably want to start with your own subform based on the demo's)

Add the essentials in the form code to create the treeview and be sure to 'tear down' the treeview in the form's close event if not before


Comment by: Helen Feddema (28-12-2013 00:22:12)

It took a lot of work to convert my old Treeview code to work with yours, but I got it working in 64-bit Access, so thank you for making this possible! Since I have a number of forms with similar Treeview controls in my database, I had planned to make a public Sub called from the forms' Load event which would build the Treeview, with arguments for the queries to use (the only thing that differs between forms). But when I placed the Public WithEvents mcTree As clsTreeview in a standard module, it turned red. I tried putting it into the clsTreeview class module, and it didn't turn red, but the code didn't run. Does this feature only work in a form module?


Comment by: Jan Karel Pieterse (28-12-2013 09:59:53)

Hi Helen,

Events can only be declared in class modules and a userform is a class too. Modules are not.


Comment by: Helen Feddema (1-1-2014 23:02:50)

I have tested my sample database that uses your Treeview alternative in 32-bit Access 2013 running on 64-bit Windows 7, and it works fine in that environment. I may be able to test it in 64-bit Access 2013 soon, and I will post a message about that test.


Comment by: Jan Karel Pieterse (2-1-2014 06:46:00)

Hi Helen,

Thanks for letting us know!


Comment by: Ian (6-1-2014 18:22:21)

Jan, this is awesome. It would be amazing if you are ever able to find the time to create a version of the listview control.


Comment by: Duncan Broom (9-1-2014 21:22:14)

This is great.

We're starting to have new machines introduced with Access 2013 (which doesn't work with Treeviews, presumably a mscomctl issue...). The test file works great, so I'm very hopeful!

I'm surprised the NodesAdd functions were housed in the treeview class, rather than creating a custom Nodes class (Collection wrapper) and storing the Add/Remove/Clear/etc methods to that class.


Comment by: Jan Karel Pieterse (10-1-2014 11:16:59)

Hi Duncan,

Perhaps splitting this into three classes might have been neater, I don't know. As a side effect, it currently only takes copying two classes into your project to make it work :-)
I would expect the old treeview to work in Access 2013, as long as you install the 32 bit version.


Comment by: Thanapol Rojanapanpat (17-1-2014 02:45:10)

It is easy to integrate and works in both 32bits and 64bits MS Word 2010 on WinXP and Win8.


Comment by: Tony Matyas (23-1-2014 09:04:17)

Hi Jan Karel,
I succeeded in filling your Demo TV with XML Elements.

In order to show further details on the Form, I tried to create another TreeView in the same UserForm of your Demo Version 'ufDemo' by means of a second Frame ('Me.frTreeControl2') with additional TreeView Nodes.

I added the following code, but didn't succeed at the moment when I want to add Root/Nodes to the second Frame without subsequent error already in class clsTreeView's 'BuildRoot' procedure . - What can I do using your Demo code as far as possible? Thanks in advance

' TreeView Code on Top of your UserForm 'ufDemo'
Private WithEvents mcTree As clsTreeView
Private WithEvents mdTree As clsTreeView
Private mbExit As Boolean

'No Changes in Private Sub InitializeDemoIcons()
'I repeat only the important lines
Set mcTree = New clsTreeView
With mcTree
Set .TreeControl = Me.frTreeControl ' Demo Frame/TV
Set .Images = Me.frmImageBox        ' Img Frame Method
' build Nodes
' ...
End With
'What's wrong with my cloned procedure, where I made only the following small changes ?
'Private Sub InitializeDemoIcons()
Set mdTree = New clsTreeView
With mdTree
Set .TreeControl = Me.frTreeControl2 ' Another Frame/TV
Set .Images = Me.frmImageBox         ' Img Frame Method
' build Nodes
' ...
End With


Comment by: Jan Karel Pieterse (23-1-2014 10:08:00)

Hi Tony,

Am I right that you now have two subs called "InitializeDemoIcons"? That is not allowed. You should combine the code in both subs into one.


Comment by: Tony (23-1-2014 19:25:55)

Hi Karel,
no, of course I did name the second sub differently InitializeDemoIcons2(), just omitted the '2' in my posting.
I would appreciate testing your ufDemo Code with this small modifications.


Comment by: Jan Karel Pieterse (24-1-2014 07:06:48)

Hi Tony,

If you can send your modified demo to me I'll have a look.


Comment by: Steve (14-2-2014 21:14:15)

Getting your Treeview control working in my Access application was straightforward. Just took a few edits to replace the ActiveX Treeview control properties and methods. Thank you so much for this wonderful work.

My application dynamically populates the child nodes when the parent nodes are expanded, instead of populating the entire tree at once. For this strategy to work, I rely on the Expand and Collapse Events of the existing ActiveX control. How could I trap these events using your control?


Comment by: Jan Karel Pieterse (17-2-2014 06:58:40)

Hi Steve,

All clicks on the tree are handled by this routine:
Friend Sub NodeClick

there is a staatement to check whether an expander was clicked:
If oCtl.Name Like "Exp*" Then

It is there where you would write code to add nodes to the tree.
Alternatively, you can add your own click event to the expander. You can see how to do that by checking out the mcTree_NodeCheck event in the demo form. It has two lines of code in the clsTreeView class. One in the declaration area:
Event NodeCheck(cNode As clsNode)

and one in the NodeClick sub:

RaiseEvent NodeCheck(cNode)

The first one enables you to add the event to the form where the clsTreeview class was added. The second one actually fires the event.


Comment by: Daniel (19-2-2014 22:31:58)

Hi there,

great tool. Is is possible to store this as a dll file or something similiar so that I can load it as a library (Tools => References).
You know, like the old form was stored in the "Microsoft Windows Common Controls 6.0" if i"m not mistaken.

Thanks a lot!


Comment by: Jan Karel Pieterse (20-2-2014 09:20:09)

Hi Daniel,

Well, not easily that's for sure. We tried to package this as much as we could so implementing it is as easy as possible.
One great advantage of having to include it in your VBA project is that it makes your solution self-contained. No need to ensure your users have any special dll's installed.


Comment by: Steve (21-2-2014 03:49:20)

Wanted to share my experience after experimenting with the Treeview control for about a week.

Consider the scenario where the Treeview Userform is embedded on a form (just like frmDemo in the sample file), call this Form A.

Form B is a data entry form bound to a table.

Both Form A and Form B are subForms on a Master form. The Treeview nodes are populated on the load event of the Master form.

If the user first clicks on a field in Form B, everything functions as expected.

If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated.

This behaviour has something to do with the Treeview container receiving focus before any other field does. It behaves as if it won't release the focus to any other control.

I've developed a bit of an exotic workaround, but was wondering if anyone has seen this strange scenario?


Comment by: Peter Thornton (24-2-2014 15:32:00)

Hi Steve,

I'm not sure what's going on there wrt "the fields on Form B can receive focus, but they cannot be updated" or "It [the treeview container] behaves as if it won't release the focus to any other control".

We deliberately include stuff related to focus for two reasons, if I briefly explain maybe that might shed some light on your situation:

1 When the treeview container is clicked that does not of itself set focus to its subform host, hence moSubControl.SetFocus in the container's click event.

2 To enable keyboard navigation with tab. When user tabs to the subform and it receives focus the userfom window is brought to the top so it can receive the keyboard. Then if user keys tab (on the treeview) focus will be passed to the next control in the "tab order" in the main form. See moSubControl.Parent.SetFocus (switch focus to the main form) and ctl.SetFocus (activate the adjacent control in the tab order)

If user with the mouse activates a different subform, or any control on the main form, it should gain focus in the normal way (the subform's exit event lets the container know its lost focus but only to give the active node a different highlight).

It's possible to have multiple subforms each with a treeview and switch focus between them, either with the mouse or tab. IOW there should be no problem with the treeview container or its subform releasing focus.

If the above does not lead to an obvious fix by all means send a file that replicates the problem you described, but please give full and clear instructions.


Comment by: Dennis (12-3-2014 13:39:17)

I am running Access 2003 on a Windows 7 64bit machine.
When I try to open one of the treeview forms I get the error "Invalid flag error" and the code stops at "Set mfrTreeControl = mUF.Controls.Add("Forms.Frame.1", "frTreeControl", True)". When I reset in the code window the form opens showing just the icons in the treeview area. When I close the form I get another error "Object variable or With block variable not set". When I click debug the code stops at the form "fsubTreeview" Close procedure at "Unload mUF".

Any ideas would be greatly appreciated.


Comment by: Jan Karel Pieterse (12-3-2014 13:57:56)

Hi Dennis,

Is this with an mdb of yourself or with the download example mdb?
If you open the demo mdb, press the "How do I?" button to get an elaborate explanation on how to implement the treeview in your projects.


Comment by: Diego F. Pereira-Perdomo (15-3-2014 17:42:04)

Hi Jan,
Forget my previous comments.
Something happened with my Office, and after using the repair tool everything started working again as expected.


Comment by: Jan Karel Pieterse (15-3-2014 17:46:03)

Hi Diego,

Glad you could sort it out!


Comment by: Hakan (19-3-2014 20:10:43)


How can I select any node exist in tree with code ?

I used


set myTree.ActiveNode= anyNode


but it is not highlight the item like when you click this item with mouse.Therefore not fire mouse click event of TreeView.

Simulate the click event

I added following line to above code

call myTree_click(anyNode)

Ok! It fires the event but still selected item is not highlighted.

Is there any solution for it ?


Comment by: Hakan (19-3-2014 20:23:31)


Forgot my last question.

I set focus to subform of treeview berfore set activenode. It's fixed




Comment by: Paul Sondervan (20-3-2014 09:59:28)

The "download the treeview sample Access database" indicates that its build 025, 16 Oct 2013.
When I download this version it turns out to be build 024, 20 May 2013.
Is it possible to put the right download on the website?
Is there a history with the changes in build 025?

Paul Sondervan


Comment by: Ermanno (25-3-2014 12:08:09)

Hi and good morning.
First of all I'd like to thank you for your outstanding job converting mscomctlib.treeview into pure vba code.
It's saving my life in converting MS TreeView vba code able to run in Access64 (Office 2010) environment.
I've only added node_MouseEvent to best cover my needs.
I've only a question conerning a weirtd behaviour when node_MouseUp should fired after loading the treeview.
To be more precise, I load a bunch of nodes from an ADO recordset.
At the end of the reading cycle, if a certain condition is matched, a single node has to be selected and viewable (former treeviewNdoe.ensureVisible equivalent to mcTree.ScrollToView cNode)

Everything works fine meaning the node is regularly selected when condition matches but if I right-click, no MouseUp event is fired but If, for example, I select the previous node using mouse right-click the event is fired up.

This behaviour happens only once and just after the recordset has been loaded into treeview control.

Here is the affected code

Set nodFound = mcTree.Nodes(j)
set mcTree.ActiveNode = nodFound
nodFound.checked = True

I'm not sure I'm doing something wrong. If you could you please help me addressing to a solution I'd really appreciate.

Thanks again for your TreeView control.

Best Regards



Comment by: Jan Karel Pieterse (25-3-2014 12:23:15)

Hi Ermanno,


I suspect if you put the Refresh command BEFORE setting the activenode it should work, but I'm not sure :-)


Comment by: Ermanno (25-3-2014 15:20:57)

Thank you Jan for your prompt reply and I apologize for my delay in answering.
Unfortunately the proposed solution does not work.
I've realized that if I use the following code

Set mcTree.ActiveNode = nodFound
nodFound.Checked = True
mcTree.ScrollToView nodFoumd

I get the node selected but no mouseUp event (nor nodeClick one).

On contrary if I apply the following code

Set mcTree.ActiveNode = nodFound
nodFound.Checked = True
nodFound.BackColor = vbGreen

the node is highlighted and right-clicking, fires up the event.
Basically the final effect is quite the same (except the background color). Node is set as ActiveNode and it's highlighted so final user can see at glance where his/her node is.
Though it seems weird that selecting a node and mouse right-click does not fire up its event. I need to investigate with more accuracy.

Thank you again



Comment by: Peter Thornton (26-3-2014 14:29:49)

Hello Ermanno,
You've have identified a small bug or rather a little detail that somehow got removed in a recent version. What should happen is at startup the activenode should be highlighted depending on whether the treeview is in focus (backcolor) or not in focus (only the border).

As a temporary fix *all* users can add the line that simulates the treeview gaining or losing focus and in turn apply the relevant highlight

        mcTree.EnterExit True ' or False

Change the True to False if the treeview is configured to be in focus. This will probably be a permanent fix for Access as it is difficult to determine with code if the treeview is in focus until an enter/exit event has been received, but if anyone knows how drop me a line!

Now for your particular question - ScrollToView does not change the activenode, working with the demo form try this

    With mcTree
        .EnterExit True 'False
' .EnterExit probably not necessary this time but
' no harm to include it
        Set cExtraNode = .Nodes(.Nodes.Count - 4)
        .ScrollToView cExtraNode
        Set .ActiveNode = cExtraNode
    End With

This should scroll to show the 5th node from bottom and make it active.

To trap right click in your form, Raise the MouseUp event similar to the click event but include the Button argument.


Comment by: Peter Thornton (26-3-2014 16:47:49)

Further to my previous comments about highlighting the activenode at startup, there is no bug but to highlight correctly at startup the following can be included after calling .Refresh when building the treeview for the first time -

For Excel and Word (not Access)

With mcTree
' code
    'create the node controls and display the tree
    ' let the treeview know if it's in focus and
    ' highlight the activenode's backcolor or border as appropriate
    .EnterExit bExit:=Not Me.ActiveControl Is Me.frTreeControl
    ' optionally put the treeview in focus, the frame's
    ' enter event will fire and highlight the activenode's backcolor
End With

For Access only

With mcTree
' code
    'create the node controls and display the tree

    ' let the treeview know if it's in focus and
    ' highlight the activenode's backcolor or border as appropriate
    .EnterExit bExit:=Not Me.ActiveControl Is Me.subTreeView

    '' optionally put the treeview in focus, the subTreeView's
    '' enter event will fire and highlight the activenode's backcolor

End With

Once running the treeview's container frame (or subForm in Access) will receive Enter & Exit events and highlight the activenode appropriately.


Comment by: Chris (9-4-2014 06:39:07)

Last year there was a comment about you working on a version with ControlTipText. Is there any update? Has anyone else got a working example with the ControlTipText property?


Comment by: Jan Karel Pieterse (9-4-2014 07:14:31)

Hi Chris,

The current build 025 has a controltipText property in the clsNode class.


Comment by: alberto (10-4-2014 17:10:24)

how to populate the tree with a recursive function? Using traditional ms tree control I use
Set objNode2 = tvw.Nodes.Add(id_father, tvwChild, id_child2, child2)



Comment by: Charles Sestrem (10-4-2014 21:05:34)

Hello Guys, congratulations class, is very functional.

I am using the Treeview class, and am having difficulty changing the icon image selected at runtime.

For the name I am updating DESTS way.

Set cNode = mcTree.ActiveNode

cNode.Caption = "Charles".

And for image I'm using as well;

cNode.ImageMain = "My Icon"

but does not update

Can anyone help me ?


Comment by: Chris (10-4-2014 22:41:32)

Yes, I found it in the Excel project - thank you. I have it working fine now. BTW, The Access example database doesn't contain it and is still on build 024.


Comment by: Jan Karel Pieterse (11-4-2014 11:46:47)

Hi Charles,

You probably have to refresh the tree after changing the icon: mcTree.Refresh


Comment by: Jan Karel Pieterse (11-4-2014 11:47:40)

Hi Alberto,

I see no reason why that shouldn't work with our treeview?


Comment by: Charles Sestrem (11-4-2014 16:01:40)

Thanks for the feedback Jan Karel.

I'm using the mcTree.Refresh line, just had not posted the previous comment.

is a pity unable to update the icon.

My project need to use dynamic icons when the user makes a change of status.


Status = Free -----> green ball
Status = Closed -----> red ball

My Code Example:

'Mycode Example

Sub REfreshTree()

If mcTree Is Nothing Then Exit Sub

Set cNode = mcTree.ActiveNode

Select Case Forms!FormPrincipal!Status.Column(0) 'Set Status

Case 1

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON1"
cNode.ImageExpanded = "ICON1"

Case 2

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON2"
cNode.ImageExpanded = "ICON2"

Case Else

cNode.Caption = Forms!FormPrincipal!Name
cNode.ImageMain = "ICON3"
cNode.ImageExpanded = "ICON3"

End Select


End Sub


Comment by: Jan Karel Pieterse (11-4-2014 16:35:10)

Hi Charles,

Actually, you can update the icon, it just takes a bit of convincing to have the tree update it.

This seems to work:

cNode.ImageMain = "FLGUSA02"
cNode.Expanded = Not cNode.Expanded
cNode.Expanded = Not cNode.Expanded


Comment by: Greg Maxey (18-4-2014 15:37:21)

I have used your tree view control in a utility for mapping content controls in Word. Mapped controls (their XML) can then be displayed in a userform using your control.

I am getting an error "duplicate key" and the source is in your clsNode Public Function AddChild procedure whenever my XML contains nodes with "similar" but "different" base names. For example the following XML will cause the error:

<?xml version="1.0"?>
<CCInstaMap xmlns="http://USAOCCInstaMap/GKM">
<Name xmlns=""/>
<name xmlns=""/>

Is their any way to make the key in your moTree.Nodes collection case sensitive? Currently it treats
"/ns0:CCInstaMap[1]/Name[1]" and "/ns0:CCInstaMap[1]/name[1]" as the same key when in fact they are different.

I'll be happy to share the utility with you if you need to conduct any test. Thanks.



Comment by: Jan Karel Pieterse (18-4-2014 19:24:22)

Hi Greg,

Do you happen to have "Break in class modules" turned on?


Comment by: Peter Thornton (19-4-2014 12:01:16)

Hello Greg,
When a node is added the Key (if included) is added as the Collection item's key; if a duplicate is attempted the error is trapped and referred back to tell you "don't do that"!
Collection keys are not case sensitive, normally that's useful but in your case unfortunately it isn't. To demonstrate -

Dim c As New Collection
s1 = "/ns0:CCInstaMap[1]/Name[1]"
s2 = "/ns0:CCInstaMap[1]/name[1]"

c.Add 100, s1
c.Add 200, s2 ' error #457 duplicate key

It would be difficult to rewrite to accommodate for case insensitive keys, and in doing so might add new problems for others as the whole point about keys is they should be unique, irrespective of case.

At least three workarounds come to mind, two of make use of other built in methods and properties (eg Tag) and a third where you maintain your own look up table. If you want to send something to test with I'll have a look (my address is in the headers).


Comment by: David (12-5-2014 03:10:22)

This functionality you have created is fantastic. For what I need, I would also need a 64 bit grid control with hierarchy capability (I currently use the iGrid).

Is this functionality available by any chance?



Comment by: Jan Karel Pieterse (12-5-2014 10:03:31)

Hi David,


I have no real plans to create such a control, it is a heck of a lot of work.

However, I've recently seen a chap who has managed to use (heaps and heaps of) API functions to backdoor himself into the Windows common controls after all, even with 64 bit Office:


Comment by: Eduardo (17-5-2014 22:51:15)

Hi, sorry by my english, I speak spanish.
First,thanks you for sharing your knowledge. I have a question: How can I add an event by e.g (open a new form) making double click on a last child child node or on a check box (but the check box appears at all nodes, not only at last child node)

Thanks in advance




Comment by: Jan Karel Pieterse (18-5-2014 20:51:29)

Hi Eduardo,

Do you mean have something respond to a click on a checkbox in the treeview?


Comment by: Eduardo (19-5-2014 14:07:56)

Hi Jan,

Yes, e.g open a form or file after select a check box o double click at node. The form has a reference to the node or after select the node, this node refer to a path file




Comment by: Jan Karel Pieterse (19-5-2014 14:34:25)

Hi Eduardo,

Please see this comment:

Comment by: Jan Karel Pieterse (5/24/2013 12:55:38 PM)

To view the comment, click:


Comment by: Jaap van der Sijp (3-6-2014 16:22:12)

I have found an issue with the Treeview, which I have been able to replicate in your demo.
The issue is that if you leave a control (say a frame with some entry fields) and click on the treeview, first an frame_exit event is raised. I use this to check if the data in the fields has been saved. If not, then a messagebox comes up to ask if the user wants to save the changes. Then the focus is transferred to the treeview and the correct node is highlighted. However, the node_Click event is not firing, ie it only superficially looks asif the node has been clicked. Your demo shows this very nicely in that the details of the clicked node do not appear below the treeview. It seems that the messagebox highjacks some of the eventsmeant for the treeview. To replicate this: create a frame on the userform. I added a textbox and a label. Then I added the following 3 subs:

Private Sub Frame1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
End Sub
Sub ChkCh()
    If lblC.Caption = "1" Then
    MsgBox "Changed!"
    lblC = 0
    End If
End Sub
Private Sub TextBox1_Change()
    lblC.Caption = 1
End Sub

Now run the form, I selected tree with icons. Click on some nodes: all works well go to the frame, make a change in the textbox, then click back on a different node on the tree. Note how the active node description below the form has not changed. Click again in the tree, the original node is activated and only now does the tree start behaving normal again.

Having put some breakpoints in the code shows that node click and node mouseup events are not firing.
Any quick fix for this?


Comment by: Jan Karel Pieterse (4-6-2014 07:57:33)

Hi Jaap,

If I understand correctly, indeed setting the ActiveNode property does not fire a click event. You can do that by adding the raiseEvent line to the appropriate property in clsTree:

Public Property Set ActiveNode(oActiveNode As clsNode)
'Existing code
'Rais event goes here


Comment by: Jaap van der Sijp (4-6-2014 13:24:57)

No, you understood wrong. But with a bit more testing I have found that it is a general issue and not related to TreeView particular:

When the user clicks on a control, and this first triggers a messagebox or dialog (for instance when an onExit check is performed for the previous control), then Windows only sets the focus of the control clicked, by raising a MouseDown event, but does not raise a MouseUp event for the control.

The user will have to click again to get the proper event chain going.

So to get the behaviour I want I can call the MouseUp event from the Mousedown event sub, but in the (less likely) case that the user didn't want to click and is trying to move the mouse away, (s)he gets the wrong behaviour (The onExit event is raised before the MouseDown event anyway).

Consider this topic closed/solved as it is a Windows issue, not a treeView issue


Comment by: Jan Karel Pieterse (4-6-2014 14:48:12)

Hi Jaap,

Form events can be challenging indeed :-)


Comment by: Gilad (26-6-2014 06:12:38)

This is so great. But is it safe to assume that the required references will work under any system? Different versions of Windows or of Office?


Comment by: Jan Karel Pieterse (26-6-2014 07:04:13)

Hi Gilad,

Yes, because there are no references to external libraries involved! Just the default MSForms, Office and Excel


Comment by: Stephen Campain (1-7-2014 03:32:45)

This is a fantastic piece of work. Congratulations

I have been trying to modify the cmdGetData sub in Form_frmDemo so that it will get data from a table containing some 30k records of hierarchial data identified with parent child and a level identifier i.e. 1, 2,..12. I wish to loop through the records one level at a time and use the mcTree.nodeadd method to build the tree. However, as a noob I am having a problem passing a variable to the SQL statement.

At the start of the cmdGetData sub I inserted
Dim levelCurr As Integer

Then I set the variable and run the SQL but the variable does not pass to the SQL statement.
         levelCurr = 1
        strSQL = "select LocAsset, LocAssetDesc from dtbl_Hierarchy where LocAssetLevel = levelCurr"
        Set rst1 = dbs.OpenRecordset(strSQL)

Why is this?

Regards, Stephen


Comment by: Jan Karel Pieterse (1-7-2014 07:09:02)

Hi Stephen,

The proper syntax is:

levelCurr = 1
strSQL = "select LocAsset, LocAssetDesc from dtbl_Hierarchy where LocAssetLevel = " & levelCurr"
Set rst1 = dbs.OpenRecordset(strSQL)


Comment by: Jaap van der Sijp (1-7-2014 16:08:04)

Jan Karel, as promised a donation. I have a good working version.

Any pointers as how to make the nodes mouse drag-able for move or copy? The Office VBA TreeView has a OLEDrag/Drop property, but simple controls don't have this.


Comment by: Jan Karel Pieterse (2-7-2014 11:26:52)

Hi Jaap,

Thanks for the screenshot, looking good.

Peter Thornton has been working on a pro version of the treeview which would include drag&drop, but I'm not sure of the status.


Comment by: Peter Thornton (3-7-2014 09:47:26)

Hi Jaap,

Drag and drop can work well with the treeview but have held back from making a demo for various reasons, partly as it's difficult to make a generic demo which typically will need considerable adapting for specific applications. In the meantime we'd be happy to help implement D&D in your app on a consultancy basis. If that might be of interest please contact either of us (emails inside the demo).


Comment by: luke (9-7-2014 08:10:30)


I am trying to use the MSAccess version; this is/was an MDB. Whenever I tried to Design the main form frmDemo I get the error 'You do not have exclusive access to the database at this time'.

I am the only one opening it, is only open once on my local drive.

I tried upgrading it to Access2010 accdb , still same problem. Then tried to build the accdb from scratch and import all the forms and code (added the FM20.dll reference). Everything compiles ok, but still same message ;do not have exclusive access...'

I am using Win7 Pro on a 64bit machine, with MSAccess 2010 32bit


Comment by: Jan Karel Pieterse (9-7-2014 10:35:01)

Hi Luke,

I'm no Access expert, but the Access demofile contains specific instructions on how exactly to implement the treeview in your own Access database. Perhaps that description gives you some pointers?


Comment by: Davide (18-7-2014 22:46:21)

I cannot change or insert icons in the access version.
I can select the properties and try to change the image by selecting "..." but the new image is not loaded and the icon doesn't change.
Ther's a way to do that?



Comment by: Muneeb Mansoor (21-7-2014 09:04:26)

Hi Guys,

First of all, amazing work on this. Exactly what I was looking for!

I have one question though. Is it possible to use a drop down menu as a child instead of static values? For example, I would have a parent node called Attributes and then multiple child nodes with the options to select values from a drop down. Possible?

Thank you.


Comment by: Jan Karel Pieterse (21-7-2014 21:01:38)

Hi Davide,

I'm sorry, I am not sure how you are trying to add pictures, can you detail out the steps?


Comment by: Jan Karel Pieterse (21-7-2014 21:02:33)

Hi Muneeb,

You could use the click event of a node to add new nodes to it at runtime if that is what you mean?


Comment by: Muneeb Mansoor (21-7-2014 21:29:52)

Let me try to demonstrate what I'm try to say:

+ Parent 1
     - - - - Child 1 <-- I want this to be a drop down menu.


Comment by: Steven Elliott (28-7-2014 06:23:00)

Thank you so much for this awesome treeview control!

I have a VBA app I developed in AutoCAD and I needed to update it for the 64-bit version. It is a block librarian that made extensive use of the MS Treeview control, which is not an option for 64-bit, as you well know.

Your all VBA Treeview is a blessing. It works great in AutoCAD. The only place I had any problem (so far -lol) is where you use the variable 'HostProject' such as Private Sub BuildRoot. That seems to be an MS Office variable that is not available in AutoCAD. I simply REMmed out the offending lines and hard-coded what I needed there, since this app won't run on other host programs anyway.

Great job on this accomplishment! And many, many thanks for making this available to others!

Steven Elliott


Comment by: Hannu Metso (5-8-2014 03:21:54)

How to make initalisation from worksheet data same data structure as dumped data in excel demo?
I want to builsd some default parts to make treeview


Comment by: Jan Karel Pieterse (12-8-2014 10:40:13)

Hi Hannu,

The demo already shows how to add items to the tree. It is a matter of cleverly reading the worksheet and adding the items in a similar fashion.

Something like this:

Sub Demo()
    Dim oCell As Range
    Dim lLevel As Long
    Dim nParentNode As clsNode
    Dim nNode As clsNode
    For Each oCell In ActiveSheet.UsedRange.Columns(1)
        For lLevel = 0 To ActiveSheet.UsedRange.Columns.Count
            If oCell.Offset(, lLevel).Value <> "" Then
                If lLevel > 0 Then
                    'Add node to last parentnode, then make current node new parentnode
                    Set nNode = nParentNode.AddChild(sKey:=oCell.Offset(, lLevel).Value, vCaption:=oCell.Offset(, lLevel).Value)
                    If oCell.Offset(1, lLevel + 1).Value <> "" Then
                        'Next row has child, change parentnode
                        Set nParentNode = nNode
                    End If
                    Exit For
                    'Add new rootnode, make it current parentnode
                    Set nParentNode = clsTree.AddRoot(sKey:="Root", vCaption:="Root Node")
                    Exit For
                End If
            End If
End Sub


Comment by: Jan Karel Pieterse (12-8-2014 10:54:24)

Hi Steven,

Thanks and you're welcome!!


Comment by: Jan Karel Pieterse (12-8-2014 11:17:29)

Hi Muneeb,

That would require modification of the control. It can be done, but we would have to start a (paid) project if you want assistance with that.


Comment by: Hannu Metso (14-8-2014 08:22:45)

Thanks for code

Comment by: Muneeb Mansoor (7/21/2014 9:29:52 PM)
Let me try to demonstrate what I'm try to say:

+ Parent 1
     - - - - Child 1 <-- I want this to be a drop down menu

I am also keen on that


Comment by: Jan Karel Pieterse (14-8-2014 11:40:23)

Hi Hannu,

Noted. But as I replied above, this would need to be a paid project as it deviates significantly from a normal treeview control.


Comment by: Siegfried Wieser (18-8-2014 08:08:34)

Thank you very much for this awesome treeview control!

Is there a way to make the expander to "+" without children? At the moment i make "fakechildren" and remove them, when i expand to add other nodes.

Or maybe im completly wrong how i do this?


Siegfried Wieser


Comment by: Jan Karel Pieterse (18-8-2014 09:50:13)

Hi Siegfried,

I'm not sure what you need; are you saying you'd like to have expanders regardless of whether the node has childnodes? I think that isn't how the Common controls treeview works, hence it isn't the way ours works.


Comment by: Siegfried Wieser (18-8-2014 11:38:46)

Thank you for your quick response!

Yes, I need expanders regardless of whether the node has childnodes. Thats because i want to make the childnodes before the node expands. The Node_click event does a different job in my treeview. I have a lot of nodes and the childnodes are only needed when you expand them.

Thank you again for your awful treeview control!

Siegfried Wieser


Comment by: Jan Karel Pieterse (18-8-2014 11:41:37)

Hi Siegfried,

You can set a nodes Expanded property to control whether or not it's childnodes are visible, is that what you are after perhaps?

Indeed, it is an awfully nice control isn't it? :-)


Comment by: Siegfried Wieser (18-8-2014 12:04:41)

Hi Jan Karel,

if there is no Expander, the user has no Control to click at for expanding. As i mentioned, the Node_click event does a different job in my treeview. So how can the user expand the node - so that i can make the children?

Thank you very much for your quick Answers!

Siegfried Wieser


Comment by: Jan Karel Pieterse (18-8-2014 14:21:23)

Hi Siegfried,

Ah, the treeview expects the childnodes to already be there.
The code is currently in a state that that is not easy to change.

This is the routine where the expander is added:

Private Sub BuildTree(.....

It is only called when a node has childnodes.


Comment by: Peter Thornton (20-8-2014 12:14:14)

Hi Siedried,
I don't follow if the objective is to make nodes at design but not expanded, or "to make children"..?

A method to add new nodes during runtime is already built-in to the treeview, see the buttons on the demo form "Add Sibling" and "Add Child".

There are several ways you can provide on the UI for the user to add new nodes, eg a button on the form (as in the demo but with more details), a right click context menu on node, press a shortcut key on the activenode, etc.


Comment by: Frans Truyens (20-8-2014 18:38:09)

Is it possible to use a double-click event in the treeview? I would like the user to be able to double-click on a node, and intercept that event


Comment by: Jan Karel Pieterse (20-8-2014 19:02:12)

Hi Frans,

There already is a double-click event, but it is currently in use for node editing. You can change that relatively easy though.


Comment by: Miguel Mezquita (26-8-2014 21:05:38)

Hi Jan,
Before anything, let me say thanks for sharing such an amazing piece of work.
I am working in a project where the user needs to compare three similar products and as the products are not identical, I think that I will show three trees at the same time. Also, every node has a picture.
Do you think that your component could implement these requirements?
Thanks again and look forward to hearing from you.


Comment by: Jan Karel Pieterse (27-8-2014 10:22:55)

Hi Miguel,

You're welcome! Nice to hear that people enjoy our work.

Yes, you can have as many treeviews side-by-side as you like, as long as you do not exceed whatever it is a userform can handle regarding the total number of controls (which limit I do not really know). Three should not be a problem at all.

To do that, each tree needs its own frame (e.g. frmTree1, frmTree2, frmTree3) and its own module level variable, e.g. mcTree1, mcTree2, mcTree3.

The documentation describes how to handle icons.


Comment by: Hannu (29-8-2014 04:45:10)

I modify little dump data so can insert to cells also node`s key, because node`s captions can be same and you get error when read data back to treeview control.

Maybe useful someone else too...

Sub GetData1(cParent As clsNode, lCt As Long, ByVal lLevel As Long, rng As Range)
' PT, a recursively retrieve the node's decendent data
    Dim cChild As clsNode

    lLevel = lLevel + 1
    lCt = lCt + 1

    rng(lCt, lLevel) = cParent.Caption
    rng(lCt, lLevel).ID = cParent.Key
    If Not cParent.ChildNodes Is Nothing Then
        For Each cChild In cParent.ChildNodes
            GetData1 cChild, lCt, lLevel, rng
    End If

End Sub



Comment by: Jan Karel Pieterse (30-8-2014 11:47:39)

HI Hannu,

You should not be getting duplicate key errors, they are triggered by a setting in your VBA editor "Break in class modules" is not the usual setting it should be "Break on unhandled errors".


Comment by: Tobias Johnson (2-9-2014 18:08:10)

Dear Jan,

first off all great, no outstanding work!!

One question. I would like to use it as a hierarchie for different countries within a region. Is there a way that a user can just douple click on an element ( Sibling or Child) and this will be lets say linked to a certain cell within Excel so I can perform some other calculations?

Thank you in advance for your feedback.

Tobias Johnson


Comment by: Jan Karel Pieterse (3-9-2014 11:51:08)

Hi Tobias,

Right now the double-click event is used to enable editing a node in the treeview.

This is handled in the clsNode class by this event sub:
Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)

You could remove this part:

        If moTree.EnableLabelEdit(bDummy) Then
            moTree.EditMode(Me) = True
            EditBox bEnterEdit:=True
        End If

and replace it by a call to a new sub in the clsTree class:

moTree.DblClick Me

In the mcTree class you would have to add (at the top):
Event DblClick(cNode As clsNode)     'Node dblclick event

Also in clsTree, add:

Friend Sub DblClick(cNode As clsNode)
    RaiseEvent DblClick(cNode)
End Sub

Finally, in your userform you should now have a dblClick event available to the variable you used to hold the instance of the clsTree. E.g.:

Private Sub mcTree_DblClick(cNode As clsNode)

End Sub


Comment by: Andy Barber (9-9-2014 13:03:11)

the 'How Do I' section for the MS Access version of this states we need the MS Forms 2.0 library, however this is not installed on some of the machines I need to run this code on and Microsopft states the the FM20.dll file is not a re-distributable file. Any ideas how I can get round this. your tool is excelent and I would really like to use it.

Please reply to attached email


Comment by: Yves (9-9-2014 15:32:58)

Hi Jan,
Using your treeview to prototype a development in access...
Do you see a way to use images from files instead of having to glue them in form control?


Comment by: Jan Karel Pieterse (9-9-2014 21:24:12)

Hi Yves,

You should be able to load an image from a file, but you would have to do the plumbing yourself:

Me.Image1.Picture = LoadPicture(strPath & "DeerPicture.jpg")


Comment by: Jan Karel Pieterse (9-9-2014 21:25:21)

Hi Lex,

Odd, any Office installation should have the Forms dll installed. If you insert a userform in e.g. Excel VBA, does the reference not turn up?


Comment by: Yves (10-9-2014 10:29:47)

Hi Jan,
thanks for your reply.
Do you mean image should first be added to form control?
Assigning image in tree don't seems to be accepted...

I wrote
cRoot.ImageMain = "c:\xxx.bmp"

and got error 5 Invalid procedure call or argument...


Comment by: Jan Karel Pieterse (10-9-2014 11:16:40)

Hi Yves,

I mean that the control currently does not support adding images from file, but that you can add that support yourself :-)


Comment by: Yves (10-9-2014 11:38:37)

OK, will do if/when I have time to do :-)

Your tree code is already big help for me and I understand he can not do everything!


Comment by: Alex (24-9-2014 13:45:16)

Hi Jan,

I just discovered your Treeview, and it's really great. I'm playing around with it, to find out how it works, and have just one question :
I can't figure out how to display treelines in the treeview. Whether I check the 'Show lines' box in the demo application or not, no line is displayed between nodes...

Using build 025 under Access 2010 64 bits.

Any idea where this can come from ?


Comment by: Jan Karel Pieterse (24-9-2014 13:58:26)

Hi Alex,

If you expect to see horizontal lines between the nodes, then you are right, those are not in the treview (nor are they in the original AFAIK). The LInes checkbox controls whether or not the tree connection lines display.


Comment by: Alex (24-9-2014 15:27:08)

I was talking about the lines that links one node to an other, like in the screenshots at the top of this page.

So, something like that :

- Root
+-- Node
+-- Node

I get more something like this :

- Root

+ Node

+ Node

so no vertical lines, nor horizontal ones.

Here is a screenshot of the demo application :


Comment by: Jan Karel Pieterse (24-9-2014 15:54:56)

Hi Alex,

Odd, they should show. They do on my system. What Excel version and Windows version are you on?


Comment by: Alex (24-9-2014 16:10:48)

I'm working on Windows 7 64 bits, Access 2010 64 bits.

But it works fine on Windows XP 32 bits with Access 2010 32 bits.

Oh, by the way, there are two small bugs (at least under Access) :
Application.Version is as String value ("14.0"), so Application.Version >= 12#
throws an error in frmFelp.Form_Load and in the AutoExec macro.


Comment by: Jan Karel Pieterse (24-9-2014 16:51:12)

Thanks for letting me know about the bug.

The tree shows up just fine on my system, I'll ask Ben to take a look.


Comment by: Dale Fye (28-9-2014 15:14:53)


In your response at:Peter Thornton (8/20/2014 12:14:14 PM)

you mentioned "right click context menus". How would you implement these? I've got my menus built, use them all the time with the ActiveX treeview control, but now that I need something that works in 64 bit Office. Unfortunately, I have not figured out how to test for the MouseUp event and detect whether the mouse button that was clicked is the left or right button.


Comment by: Peter Thornton (29-9-2014 13:10:23)

Hi Dale,
MouseDown/Up node events were added in v023 so they should exist in your version. FWIW they were included to enable the previous activenode to be semi highlighted until user releases mouse on the newly selected node, or reactivate the previous node if user moves the mouse off the new node before releasing, in effect cancel selection. Trust you appreciate the attention to detail, but I digress <g>

In the mctlControl_MouseUp add the two new lines as indicated

    If Not moLastActiveNode Is Nothing Then
     ' about 23 lines of code
    ElseIf Button = 2 Then ' new
        MsgBox "my right click" ' new
    End If

There are various of ways to go from here, eg call a routine in the clsTreeView (passing a ref to the node) to raise an event back to the form (similar to click) to create the context menu.

Generally easiest to create a commandbars popup, but can also use APIs to create a menu if say you want to include your own custom icons.


Comment by: WOJTEK (30-9-2014 17:30:13)

It's very usefull tool for programmer. I must admit it is the best solution found by myself in this area.
I'd like to use the solution in my job. But my question is:
what do you mean by statement "The code may not be republished without prior written consent of the authors."?


Comment by: Jan Karel Pieterse (30-9-2014 19:38:11)

It just means that if you want to make the code publicly available by writing about it (for instance an article on your blog) and including code from it, you need our permission.
You can use the tool in your projects without having to ask permission. But if you add useful things to it, we ask you to send them to us.


Comment by: Andy Barber (5-10-2014 11:42:42)

Has anyone used drag and drop with the tree view to add/Move nodes? If so can you point me in the right direction to implement in my system.
I have a list of entries which I wish to drag to a node in the tree and add the items as children of that node

Many thanks



Comment by: Alex (8-10-2014 09:47:09)

Hi Jan,

I think I found it, concerning my problem with tree lines not showing up.

When drawing the lines, you use this code :
    .BorderColor = vbScrollBars

Alas, the theme I'm using on my Win 7 uses the same color for scrollbars background and window background, thus drawing white tree lines on white background, which is not very visible...

Changing this line to
    .BorderColor = 0

displays correctly black tree lines connecting the nodes.

Maybe vbWindowText would be a better choice for the border color ?


Comment by: Jan Karel Pieterse (8-10-2014 10:23:12)

Hi Alex,

Thanks for bringing that to our attention!


Comment by: Peter Thornton (9-10-2014 13:35:45)

@ Andy Barker
We've held back from posting a generic Drag & Drop example as it's best adapted to individual projects and can be a bit complicated, we could spend a long time fielding questions! However we can probably put D&D together for your project privately but it would depend on what and where your "list of entries" is that you want to drag from.

@ Alex
My thanks too for flagging your line colour issue. I think normally a lighter colour than black is more appropriate for lines, but clearly the scrollbar colour is not good if almost the same as the background colour in some user's schemes.

Can you see another system colour in your scheme that would be suitable and also likely work well in more typical setups. Feel free to email me privately.


Comment by: Alex (9-10-2014 16:52:14)

Hi Peter,

Well, I'm afraid there is no safe choice apart from vbWindowText, assuming that nobody will set the same color for text and background...

If I choose another system color based on my color scheme, it will probably fail with other schemes...

The only workaround I could see would be to check the scrollbar color against the windows background color, and, if they are too close, then apply another color. And choosing this 'other' color could also be tricky...

Or, possibly, falling back to a vbWindowText dotted line when scrollbar color is too close to window background (if the dotted line attribute can be set for a label) ?


Comment by: Andy Barber (10-10-2014 16:00:30)

Thanks Peter,

I'm using the Treeview in MSAccess.

On the left of my screen I have the tree view and on the right I have a list box which allows multiple selections. I need to be able to drag the information in the list box (will be the key and caption info) and drop it into a node on the tree.

Many thanks for you assistance with this and I hope the above is enough info for you.



Comment by: Peter Thornton (10-10-2014 16:19:19)

Hi Alex,
No we can't do dotted lines. But we can compare the normal default colour for lines to the background, and if fairly similar change to a new default of black (or white if a dark background), all automatically. In addition include a new exposed "LineColor" property to override.

We'll (probably) include this in the next version (026), not sure when that'll be so in the meantime contact me if anyone needs this.


Comment by: Alex (13-10-2014 13:19:11)

Hi Peter,

Yes, I think it's the only way to do it. And the override property will help to get rid of the most difficult cases.

I also saw that you have a fallback to RGB(180, 180, 180) for the checkboxes ForeColor in some cases. This could lead to the same problem if the background nears this color.

If you need some more testing or information, just drop me a mail.

Thanks !


Comment by: Peter Thornotn (13-10-2014 16:49:26)

Although the Access Listbox doesn't support D&D it does seem to respond to the built-in MSForms approach and in turn the DragOver and Drop events on individual nodes can be trapped.

Alternatively, can simulate D&D which would need to include the equivalent a "HitTest" (eg to toggle cursor and/or highlight when over a suitable node and then to id the drop target). However with this approach MultiSelect=Extended doesn't look viable but "Simple" seems OK.

Well spotted! The RGB(180, 180, 180) forecolor would only affect "textbox" style checkboxes (on a similar grey background), which we kept for compatibility before figuring the icons not only look better but counter intuitively don't use more resources. I suspect less important to do as I described for Lines, not sure, maybe we'll wait for complaints before deciding <g>


Comment by: Alex (13-10-2014 19:21:17)

Yes, that sounds quite reasonnable.

No reason to fix it if nobody finds it broken. :)


Comment by: Barry C (17-10-2014 00:30:46)

First of all - Amazing piece of work!

I'm in the middle of working this into a project and I'm loving it.

But... I'm having the same issue as Davide (a couple of months back).

Using Access (tried it in versions 2k, 2k3 and 2010) I can not add a new image or change an existing image in the image frame.

If I select an existing image or add a new one, the "Picture" property in the property sheet is read only.
Other properties (name, size etc) are fine.

Works fine in Excel (all versions), but not in Access.
Any ideas?

In the meantime, I'll try a work around - do that work in Excel, then export it to Access.



Comment by: Peter Thornton (19-10-2014 18:22:47)

Hi Barry,
Strange, I can't load a new picture to an image control in MSForm frame container either, thought I could!

OK, rather than "export" you can add the image controls to a form in Excel (or Word) and simply copy the control with it's picture to the Frame container in Access (can copy/paste multiple image controls at the same time). The Frame in the demo was added as an ActiveX "Microsoft Forms 2.0 Frame".

Note you can also pass a Collection of picture 'handles' as an alternative to passing the Frame control if more convenient to store your images elsewhere.

You say you've tried it in Access 2000 - interesting, does the demo actually work for you? In light testing although in 2k it all compiles OK trying to load in resulted in a bunch of "Object of class does not support the set of events" errors, no idea why!

Untested in 2002. 2003 should be fine but it seems only if compiled in 2003 (may get similar error in 2003 if compiled in 2007 or later), again no idea why. (For anyone else reading this concerns Access only.)


Comment by: Dale Fye (21-10-2014 14:09:37)

Have been playing with the treeview for several days now. Was able to add code on the MouseUp event to display a popup menu, that worked great. One of the items on my popup menu is to expand or collapse the currently selected node and all of its children.

The default, when you collapse the root node appears to collapse that node, but to leave all other expanded nodes expanded. Since my tree can 4 or 5 levels deep, I want to be able to right click on the node and select Expand All or Collapse all. Right now, my Collapse code looks similar to:

set nd = forms("frmMain).mctree.activenode
IF CommandBars.ActionControl.Parameter = "Collapse" Then
    CollapseBranch nd
    ExpandBranch nd

Sub CollapseBranch(cNode As clsNode)

    Dim cChild As clsNode
    cNode.Expanded = False
    If Not cNode.ChildNodes Is Nothing Then
        For Each cChild In cNode.ChildNodes
            CollapseBranch cChild
    End If
End Sub
Sub ExpandBranch(cNode As clsNode)

    Dim cChild As clsNode
    cNode.Expanded = True
    If Not cNode.ChildNodes Is Nothing Then
        For Each cChild In cNode.ChildNodes
            ExpandBranch cChild
    End If
End Sub

But all this is doing is changing the icon in front of the node from - to + (or + to -), it does not actually collapse or expand the node.


Comment by: Dale Fye (21-10-2014 14:51:30)

Is there a way to implement the MouseUp event in the main form? I notice in the frmDemo that there are Click, KeyDown, and NodeCheck events listed, but don't see the MouseUp event.

I've added code to the mctlControl_MouseUp subroutine in clsNode, but would like to leave that code unchanged and simply be able to refer to the mcTree_MouseUp event in my main forms code module.

Is there something special that I need to do to make that event visible in the main form?


Comment by: Jan Karel Pieterse (21-10-2014 15:30:58)

Hi Dale,

The easiest way is to call the mcTree.NodeClick method with the Node's Expander control as the first argument. Like so:

        nd.Expanded = Not nd.Expanded
        mcTree.NodeClick nd.Expander, nd


Comment by: Dale Fye (21-10-2014 16:10:34)

That seems to have done it. Thanks.


Comment by: Dale Fye (22-10-2014 16:05:38)


I'm trying to add some images to the image frImages in Access 2007. I was able to drill down to the image control on the form, and then select properties, but am unable to change the actual image.

I noticed a thread above that you cannot currently edit the images in Access, but I'm the first to admit that I'm an idiot when it comes to working with Excel UserForms.

I added a user form to an Excel file, added an image control to it, but cannot figure out how to actually assign an image (bmp, png, ico) to that image control.


Comment by: Jan Karel Pieterse (23-10-2014 07:07:56)

Hi Dale,

There are various ways to add an image to an image control on a VBA Userform:

- At runtime you can use the LoadPicture method
- At design time:
Copy the picture and paste it into the picture property of the image control
Click the three-dotted button next to the image property and select an image file


Comment by: Isabella Tsoglin (27-10-2014 14:25:49)

Thanks for your amazing work.
Works good with Access 2010.
I'll try to add "dblclick" event, not yet done.


Comment by: Jan Karel Pieterse (27-10-2014 14:46:29)

Hi Isabella,

You're welcome!


Comment by: Jon (1-11-2014 16:33:55)

Regarding the comments from Barry C (10/17/2014) and earlier from Davide (7/18/2014) I too, just now, am not able to modify the Picture property in an existing Image control, or set the Picture property of a new Image control.
In fact I added 4 new Image controls and was able to set the Picture property just 3 days ago.
My steps: Open the subTreeView form, display the Toolbox and Properties sheet for the MSForms Frame, add a Image control to the Frame and attempt to set the Picture property. The Picture property does not get set. Nor can I change the Picture property of the existing Images. I can however still do this successfully in a copy of your demo database that I used to learn with. To my knowledge nothing has changed on my computer (Windows updates, etc), but I wonder if it is some sort of Access/Current database security issue.

BTW this is an excellent piece of work and I hope to use it more.

Thanks, Jon


Comment by: Jon (3-11-2014 16:39:54)

To followup my last comment:
I actually remember, now, that I added the images to the frame in a copy of the Access demo database and imported it to my application. I have tried to add/edit image pictures to 2 different Access 2007 databases on 2 different machines. There must be some setting in the demo database that makes this possible.

It is possible, as mentioned in a different comment by Dale to change the image picture property in code with LoadPicture. This might actually be a better way because it adds some flexibility when initializing your app.


Comment by: Francisco Sciorilli Jr (6-11-2014 20:44:41)

Great work !


Comment by: Nicole (7-11-2014 17:00:36)

I have the Access build 25 treeview control and everything has been working very smoothly. For our mixed 32 and 64 bit environment this tool is perfect!

The information being loaded into the tree view is at a level that a search feature would be helpful to the end user. With that in mind I would like to search and set the active node based on the unique key value or caption assigned when loading. I can set the active node based on the item number but can't figure out how to set the active node based on the unique key. Based on all the features in this tool it is probably a simple task but any feedback would be appreciated. Thanks!


Comment by: Nicole (7-11-2014 17:18:42)

I have the Access build 25 treeview control and everything has been working very smoothly. For our mixed 32 and 64 bit environment this tool is perfect!

The information being loaded into the tree view is at a level that a search feature would be helpful to the end user. With that in mind I would like to search and set the active node based on the unique key value or caption assigned when loading. I can set the active node based on the item number but can't figure out how to set the active node based on the unique key. Based on all the features in this tool it is probably a simple task but any feedback would be appreciated. Thanks!


Comment by: Michael (7-11-2014 20:15:01)

I have added this control to my workbook, but I am being prompted for the VBA project password when Excel quits. I checked Microsoft support, and they say (Q280454) the cause is I am "running a macro that passes a reference for a workbook containing a password-protected VBA project to an ActiveX dynamic-link library (DLL), and the ActiveX DLL does not properly release the reference to the workbook that contains the password-protected VBA project."

Has anyone else experienced this? Can you help me fix it? I'm pretty sure it has something to do with how I coded my function to use these classes.

Any help is appreciated.


Comment by: Kurt Bergman (7-11-2014 21:37:15)

Re: Collapsing all nodes...

In the populate Tree Procedure I set cnode1.Expanded to false.

This add-in rocks!!


Comment by: Peter Thornton (8-11-2014 12:15:28)

Hi Nicole,
If you are using unique keys it's easy to activate the node, also scroll it into view and expand it if necessary, eg

Set cNode = mcTree.Nodes("myUniqueKey")
Set mcTree.ActiveNode = cNode
mcTree.ScrollToView cNode, , bCollapseOthers:=True

Unless 100% your unique key exists be sure to use an error handler.

If you are not using keys see the "Extended Project Explorer" demo in the examples tab (top left this page), user can enter one or more letters which will find and select nodes whose captions match the search string.


Comment by: Jan Karel Pieterse (10-11-2014 11:32:05)

Hi Michael,

This is a problem that may be caused by an add-in loaded on your system. Adobe has a reputation causing this for example.


Comment by: Nikola Petanjko (10-11-2014 15:44:12)

Thanks ! thanks ! Thanks for your wonderfull treview

I need to add 3 features
- collapse all from root (or from selected node)
- expand all from root (or from selected node)

- collapse all + expand up to deep level N
keeping collapse since any branch from level N

I will start them now, if you already have such features, thanks for sharing.


Nikola Petanjko


Comment by: Jan Karel Pieterse (10-11-2014 16:04:30)

Hi Nikolai,

Yes, such functionality is already present. Check out the ExpandToLevel method of the tree class.


Comment by: Lance Duvenhage (14-11-2014 11:31:53)

Hi Nicole, not sure if you found a solution for your problem. I used the code to read from different tables with one to many relationships. And can do the surge function you need, use the unique ID to run a query based on the selected node. May be along way of doing things but it works very well. Can share if it needed. What I would like to be able to do is when you rename the a node, how can this be saved in the table? My second question will be almost the same, If I add a new node how can this be saved in the table. My last question will be, the tag function, how to use multi select in to run the query. (Have an idea but have not tried it) Any advice will be helpful.


Comment by: Ake (14-11-2014 23:14:39)

Excellent job!


Comment by: Peter Thornton (17-11-2014 11:30:47)

Hi Lance,
1. By "rename a node" I assume you mean if the user manually edits a node with double-click or F2. Look at the AfterLabelEdit Event, slso see the example in the demo if you try and edit the caption to include "123" or "456".

2. You didn't explain if adding a new node is instigated by the user or forced by your code. Either way you should be able to know the details of any new node and update your table, either in real time or perhaps after user presses an update button.

3. "MultiSelect" is not implemented. Suggest use checkboxes, depending on your scenario the TriState option might be useful (see the demo).


Comment by: Aaron (10-12-2014 00:42:32)

Was this control written in VB.Net? I have created a ActiveX Control in C# and successfully made the dll, which I am able to bring into a VB form, but would like to use it directly on a excel worksheet. When I select the ActiveX control and try putting it on the worksheet is gives the error message "cannot insert object". Do you know how to get ActiveX objects to work on worksheets?


Comment by: Jan Karel Pieterse (10-12-2014 09:24:38)

Hi Aaron,

As the page title says: it is entirely done in VBA.
In fact, it may even be possible to update the code so it works on a worksheet as well.


Comment by: GotQuestion (6-1-2015 15:52:54)

After looking through the code, I notice you are not storing node references (parent/child) as pointers. Doesn't storing them as objects create the potential for memory leaks?

(See Rob Bruce's comment on this page: )


Comment by: Jan Karel Pieterse (6-1-2015 16:10:49)

Hi GotQuestion,

We spent quite some time figuring out how to oproperly terminate all objects and in fact (in the demo) even have some checking code in place that keeps score of all objects (as long as you are in debug mode). The tool currently does the right stuff: all objects are properly terminated at the right moment as far as we know.


Comment by: Paul Sondervan (21-1-2015 20:30:31)

I have a dropdown menu with recent choices.
When the user selects one of the recent items, I would like to set the corresponding menu choice in the Treeview.
Is it possible to set this menu choice by using vba?


Comment by: Jan Karel Pieterse (22-1-2015 07:01:46)

Hi Paul,

You can set the active node by using code similar to:

Set mcTree.ActiveNode = MyNode

MyNode is an object variable pointing to any node in the tree. This object variable you would have filled from your dropdown somehow.


Comment by: Hesham (22-1-2015 12:25:16)

I added the subform to mail form the displays detail infor on students, but How to use the tree control to filter the main form?


Comment by: Lucio (23-2-2015 17:20:03)

Ho visto il Treeview e mi interessa nella versione full..
quanto costa?


Comment by: Jan Karel Pieterse (23-2-2015 17:35:44)

Hi Lucio,

Can you please explain a little bit more? The download already contains the full version?

NB: I'm afraid I do not speak Italian :-)


Comment by: Jerzy Orysiak (24-2-2015 19:52:12)

Great piece of code, congratulations. It is a pity "drag and drop" functionality is not included (anyway I'll try to add this). I'd be grateful for hints how to handle context menu (declaring in standard module public variable containing ObjPtr? to clsTreeview object?) What is a simplest approach?
Also, I've spotted weird behaviour of checkboxes (Build number 25). Is it the last version?
1) On preloaded nodes checking node causes checking this node and all subtree "hanging" on that node and "Third state" for all direct ancestors (not sure why) and it is OK.
2) After adding some nodes, checks made on new ones goes crazy! All added ancestors of checked node are checked as well which I suppouse is not correct.
3) "checks" without icons appears as "a" letter.

The second question is: is there any property like "that node cannot be used as a parent"?

And last at this moment. I havn't found public types for export/import data for populating treeview. One for treeview object (1 data record only) and array of variables containg available nodes data (key, parent?, text, tag, font , Path?, etc). More of that it also tells which properties might be manipulated (background colors for instance should be used with care since they show for instance "selected node" , "cut subtrees") WITHOUT digging deeply inside your code.


Comment by: Peter Thornton (25-2-2015 13:11:37)

Hi Jerzy, lots of points!
Drag & Drop is not builtin, we had to stop somewhere but it’s possible to implement. There are two approaches: you can work with ‘BeforeDragOver’ and ‘BeforeDropOrPaste’ events or “simulate” D&D which is more complicated but can use custom icons. The hard work is already included in the treeview, your D&D will end up calling the builtin Copy or Move methods.

A right click context menu is more straightforward. Trap node’s MouseDown (or Up) with Button=2 and “Raise” it as an Event similar to the other events. Validate as required then create a normal CommandBars popup menu (don’t forget to destroy it). Buttons in the popup can 'OnAction' ordinary macros in a normal module, you may need to temporarily pass node objects to the normal module before showing the popup but be sure to destroy any additional treeview/node references.

I don’t follow what you are saying in 1) and 2), but if it’s about TriState checkboxes some changes to the logic when nodes are added and events for ‘indirectly’ changed checkboxes will be included in the next version (026).
Re 3) If you are not using checkbox icons the checkbox is simulated with Label controls to look like a small square, and with the Marlett font the “a” should appear as a tick. Is Marlett is installed in your system?

To retrieve data see the code in the demo behind the button “Dump Data”, cmdGetData_Click, GetData1 and GetData2. These are only examples, adapt to selectively retrieve the data you want.

Indeed colours are temporarily changed with Selection, Move & Copy, but the default or your applied colours should be automatically restored, ie after changing the selection or done with Move or Copy. Play with the 'Icons' demo and in particular the alternate shaded nodes.


Comment by: Jerzy Orysiak (26-2-2015 00:57:12)

Hi Peter,
Many thanks for a prompt reply.
1) I do have Marlett installed and when I inserted a label to userform with caption "a" with Marlott font I got correct "tick". Also, I traced the line 1017 (clsNode)
.Caption = IIf(mlChecked, "a", ""): Debug.Print "%%" & mctlCheckBox.Name, mctlCheckBox.Font, mctlCheckBox.Caption

the resultes were like:
%%CheckBoxNode8             Marlett     a
so they were correct.
When I realized that checkboxes in "Demo treeview" and "Demo treeviews with icons" work COMPLETELY different way (but they shouldn't - both go through the same function UpdateCheckbox), I gave up.
Maybe it's because i've got excel 2003 and some functionalities were lost (or work another way) during conversion. It seems I have to go back to common controls SP6.
Anyway, thanks a lot


Comment by: Peter Thornton (26-2-2015 12:20:23)

I replicate your "a" in Excel 2000. Strange, I'm sure we tested in 2000/2003 but it seems when the Label is added at runtime, although Marlett is correctly applied the default font continues to display, hence "a" not a tick.

Normally this VBA Treeview should work in all Excel & Word versions since 2000 and Mac 2011 (though Access only since 2003). However for your 2003 I suggest you use checkbox icons, see the demo code and documentation. There are two sets of checkbox images bundled in the demo or you can find your own.

Yes there is a difference in the way the checkboxes work in the two demos, with TriState and not with TriState.


Comment by: Jerzy Orysiak (26-2-2015 18:12:50)

I found the reason for "a" tick.
Setting font to "Marlett" is not enough.
lines 1626, 2053(about)
.Font.Name = "Marlett"

should be followed by:
.Font.Charset = 2

(at least it works on my system


Comment by: Peter Thornton (27-2-2015 11:04:15)

Thanks Jerzy. Font.Charset = SYMBOL_CHARSET or 2 also fixes the Mareltt tick for me in old Office versions. Not sure why it's not necessary in 2007 and newer, at least not for me and I assume not for most users or it would have been flagged here before!


Comment by: Thomas magdahl (6-3-2015 19:46:47)

Hi guys,

Nice work and I am definitely using it.

I am checking the AfterLabelEdit subrutine, simply overriding the edited text. The routine is at the end of this text.
My problem is that on the node "Hello" is diplayed but the node Caption keeps the value that was on NewString before the override (modified version of what was before editing). In your demo code 456 is replaced with 789 and both the visual text and the Caption are the same.

Any clue of what I am doing wrong?


Thomas Magdahl

Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)

    NewString = "Hello"
    Cancel = False
End Sub


Comment by: Thomas Magdahl (6-3-2015 22:06:50)


As a matter of fact the untouched demo is now behaving in my machine with the same problem.
In my previous posting I siad the problem was only on my code but not on the demo and now I realize they both have this same apparent inconsitency. Not sure if it is an erratic behaviour or I am going nuts!
When you edit a Node in the demo and add 456 at the end, it gets replaced by 789 but the text on the node in the tree window does not match with the text displayed in the labInfo label. The problem will not disapear with a Refresh or by selecting different nodes and returning to the appatently "offending" one.
Not sure if this is of any help!



Comment by: Peter Thornton (7-3-2015 13:09:02)

Hi Thomas,
If I follow you are saying everything works except the labInfo does not update with thew new Edit. That's because in the demo labInfo is only updated in the node Click event. Next time the node is activated labInfo will update with the new edit.

One of the uses of the AfterLabelEdit event, apart from "validating" an edit along the lines of the 123/456 examples, is to trap a user edit and record for your own purposes as required. Add the following to the AfterLabelEdit in the demo

Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)
Dim s As String

' Validate user's manually edited node here
    With cNode
        s = "Edit: " & .Caption & " <changed to> " & NewString & vbNewLine & _
            "Key: " & .Key & vbNewLine & _
            "Index: " & .Index & "    Level: " & .Level
            ' .VisIndex might change with EnableLabelEdit bAutoSort
    End With
    Me.labInfo.Caption = s
End Sub


Comment by: Thomas Magdahl (9-3-2015 13:38:38)

Hi Peter,

Sorry that I comming back with this same issue, but I think there are two posibillities:
1) I still do not get something that might be abvious
2) Your code behaves in my machine in a different way

From your explanation, after I edit a label and the AfterLabelEdit routine makes a change to the string (for example changes 456 to 789) and I keep on traversing and editing other labels/nodes, when I return or select the label/node whose string was chenged by AfterLabelEdit, I should see the same string in the display and in the Caption property. That is not happening in my machine.

A more detailed explanation follows.



Comment by: Thomas Magdahl (9-3-2015 13:39:21)

Hi Peter,
I just unpacked the file again from the original downloaded ZIP file, enabeled macros in EXCEL and run both demos.
I double click on the first non root node 1.A and add at the end 456 and press return. The text visualized in the tree node, displayed in the frame (just after pressing return) effectively displays 1.A789, and labInfo displays 1.A456. Based on your explanation this is explained because the labInfo label is updated with the Click routine before 456 was changed to 789. So I understand that if I move to another node and regardles if I did an edit or not in that other node (for example 2.B), when I return to 1.A789, labInfo will display 1.A789 too. That is not happening on my machine. I still get 1.A456. If after moving through a bunch of the remaining nodes I stop the VBA code and inspect mcTree, and look into mcTree->Nodes->Item2->Caption, 1.A456 is stored (the screen is still showing 1.A789 as expected). If I look at mcTree->Nodes->Item2->Control->_Value, 1.A789 is stored. Not sure if _Value is of any relevance, but at some place in the structure 1.A789 is stored and displayed correctly on screen, but the Caption property is still showing 1.A456 which in my oppinion not correct, because AfterLabelEdit made the change.

I made the decription as thorough as possible so you might better diagnose if this is case 1) or 2).

I hope I am not making a fool of myself :-)




Comment by: Peter Thornton (9-3-2015 15:32:06)

Hi Thomas,
You are right! I don't know how that crept it, it's not in the version I'm working with or most others. Please edit clsNode as indicated

Private Sub moEditBox_KeyDown(ByVal KeyCode As MSForms.ReturnInteger, ByVal Shift As Integer)

' code

    bCancel = moTree.RaiseAfterLabelEdit(Me, sNewText)
    If Not bCancel Then
        ' Me.Caption = moEditBox.Value ' << remove this
        ' Control.Caption = sNewText ' << remove this
        Me.Caption = sNewText ' << add this

This will be fixed in the next build 026 in the next week or two.



Comment by: Thomas Magdahl (9-3-2015 16:59:57)

Hi Peter,

Worked fine!
Thanks for your help!



Comment by: Thomas Magdahl (11-3-2015 17:21:36)

Hi Peter,
Besides the node editing capabilities of the TreeView, I am providing in my application a form to set additional node info. So when the user selects a node and depresses an EDIT button, a form is opened where he can not only change/edit what will be displayed in the Caption of the node (active node), but other info stored in an array (indexed by the Tag property). After the unload of that form I set the new Caption, overriding the old value and do a tree refresh.

My problem is that when no horizontal scrollbar is present, because all nodes have short texts, and the new text of the edited node is very long, I am not able to get the horizontal scroll bar to appear.
You can duplicate this behavior in your demo doing this two modifications.
1) In the InitializeDemo1 routine, shorten the text of this node so that when initializing the TreeView, only a vertical scrollbar is present.

Set cNode = cNode.AddChild(cRoot.Key & "_A", " level 5 with extra text to test scrollwidth")

Cut the text that follows “level 5”

2) Add a button to the form with the following code:

Private Sub CommandButton1_Click()
    mcTree.ActiveNode.Caption = "This very long text is used to test if the horizontal scroll bar will be activated automatically!"
End Sub

Any suggestion on how to solve this?




Comment by: Peter Thornton (11-3-2015 21:00:15)

Hi Thomas,
A method to fully update scrollbars has already been developed for the next version 026. For your purposes after changing the caption you will simply be able to call

mcTree.SetScrollbars bRecalcMaxTextWidth:=True

SetScrollbars(False) will also cater for updating scrollbars if resizing the treeview, eg while resizing the form with the mouse.


Comment by: Frank Russell (19-3-2015 17:18:21)

I think that what you have done is great for the MS Access user/developer. There appears to be only one thing missing that I would greatly appreciate if you could incorporate and that is the ability to Drag and Drop between nodes and another treeview. I have tried, unsuccessfully to develop my own drag and drop routines.
Thanks again for a great product.


Comment by: Jan Karel Pieterse (19-3-2015 20:15:11)

Hi Frank,

Thanks! There is some plumbing in there already to handle drag and drop if I recall correctly.

Peter Thornton has been investing quite some time in that feature, but I'm not entirely sure of the current status.

Watch this place, as Peter does visit here now and then to post comments too.


Comment by: tino (20-3-2015 03:39:47)

I can't open any of the files with Microsoft Office 2003.

Excel 2003, gives... invalid file format

Word 2003.. nothing happens when you click the button. I think it gets converted to a doc with no macros.

The only one I could open is the Access file, but it gives a code error when the demo starts to run upon opening the file.
I can see the code though in here.


Comment by: Jan Karel Pieterse (20-3-2015 13:42:16)

Hi Tino,

Try reinstalling Office.


Comment by: Peter Thornton (23-3-2015 11:07:38)

As Jan Karel mentioned quite a lot of work has been done with Drag & Drop. It's not included in the demo as some of the code is external to the intrinsic treeview and how best to adapt depends of the individual project. We'd be happy to help implement D&D privately if that's an option for you. Alternatively, to point you in the right direction, there are two approaches: using the built-in D&D MSForms events or "simulating", as Jan Karel also said some of the "plumbing" is included.

I've just double checked and demo v026 works fine for me in Excel 2003 and 2000, albeit after saving the file as an .xls. There can be problems with the Access 2003 if the file was not compiled in 2003, eg if used in 2007+ then attempted in 2003.
I wonder if the Dec-2014 Windows update that affects MSForms controls has hit you. With Office apps closed try deleting or renaming all MSForms.exd files in your system, restart windows.


Comment by: Jerzy Orysiak (22-4-2015 16:46:34)

good to see the next version...
I work on excel 2003. I've spotted the problem using combo boxes in documantation worksheets (don't work). It's caused by the line 28 (modDocNav)

sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)

It seems vba6 can't use names defined by formula.
I suggest sollution (works probably also with vba7)

sProc = Evaluate(Mid(ActiveSheet.Names("ProcsList").RefersTo, 2))(idx, 1)



Comment by: Jerzy Orysiak (23-4-2015 03:22:29)

As I've noticed before documentation worksheets clsTreeview and clsNode with comboboxes do not work for excel 2003. The similar situation is as far as sort "commandbutton" are concerned. RefersToRange method doesn't work with name defined as formulas. I had to change 2lines of code of modDocNav (line28)
sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)

sProc = Evaluate( Mid(ActiveSheet.Names("ProcsList").RefersTo, 2))(idx, 1)

and respectively (line 90)
Set rng = ws.Names(sName).RefersToRange

Set rng = Evaluate(Mid(ws.Names(sName).RefersTo, 2))
and it works fine. This is certainly very minor problem and doesn't concern treeview at all.
The problem is with D&D within the same tree and with 2 trees (as Frank Russel mentioned). I understand that for 2 trees there are many obstacles (like "incomapatibility" of trees definition), so it requires quite a work for validation copying etc. and it is difficult to include these methods in general model object. I would be very grateful however for some code for the begining (leaving validation apart). I've got also some idea regarding right click context menu but it requires API functions. Is there a way to avoid this approach?


Comment by: Peter Thornton (25-4-2015 12:33:03)

Hi Jerzy,
Set rng = .Names.RefersToRange(name) should work in all versions since Excel 97, I've just double checked the demo file in 2000 and 2003, all working fine. I'm not sure why you need the Evaluate workaround in your system, have you tried the example in VBA help for RefersToRange. If there really does appear to be an issue contact me privately.

D&D can be configured to work across multiple treeviews and you can use the built-in Move or Copy methods, if indeed the objective is to Cut or Copy a node and its children. The intrinsic class objects are the same in both treeviews so "incompatibility" doesn't apply. At least not as far as the treeview is concerned though of course most likely you'd need appropriate validation to ensure a node can only be dropped on a suitable target. The logic will be specific to your project. Without knowing quite a lot about your project it's hard to give code examples, there are different approaches and unfortunately not straightforward!

For a right click context you can use the Office Commandbars popup which does not require any APIs, plenty of examples out there. You can also create a Windows popup which requires eight APIs including CreatePopupMenu, look at CommandBars!


Comment by: Jerzy Orysiak (26-4-2015 17:51:31)

Hi Peter,
1) Set rng = .Names.RefersToRange(name)works fine but only when name is defined as range directly, i.e. not like in this case formula "=OFFSET(...)". This is not a problem for me and I think it's not worth to examine that further.
2) D&D Thanks for tips. The main reason I need multitreeview D&D is to create "commandbar manager" which consists of 3 treeviews:
i) target treeview - for keeping current application user commandbars, references and basic call function information (short help)
ii) source treeview - excel "Worksheet Menu Bar" commandbar
iii) source/target my "universal" fully tested functions (placed in commandbars) from other excel applications
3) Right click context menu. It's not a problem to create popup commandbar. Problem is: how to avoid public functions/variables.



Comment by: Peter Thornton (27-4-2015 19:33:14)

1) RefersToRange should also work in all versions since '97 with a Dynamic name.
2) Sounds like an interesting project!
3) You can avoid public functions in normal modules by prefixing the functions and variables with Private. There's also Option Private Module.
Alternatively put everything relating to the context menu in a Class, including object variables and action code. Instead of OnAction macros trap respective button click events with WithEvents.


Comment by: Jerzy Orysiak (29-4-2015 01:32:34)

1) I didn't express myself correctly what I meant "public". Commanbarcontrol .OnAction requires PUBLIC function/sub placed in standard module. I want to avoid this. I want to take all actions in class module either in userform or another "wrapping" withevents class.
What I do NOT KNOW is how to trap popup menu choice (item selected) whithin class ...

2) I want to keep tree in a recordset (sheet) (RecID, ParentID, field3, field2...) of simple data type (images represented by keys).
I need to update image frame each session with userform (pictures can be added/removed. It requires VB project Designer action (I don't know any other solution). Once I execute designer's images updating, I can also save current treevieew settings like "showlines", "Show expanders" in for instance in userform "tag". Is this acceptable approach?


Comment by: Peter Thornton (29-4-2015 11:38:30)

1) The OnAction macro in a normal module can be Private. Or, in a class module when adding the buttons assign then to 'WithEvents' CommandBarButton object variables, which will expose trappable Click events.

2) Icon images do not need to be hard coded, and no need to use the Designer object. Although the simple way is to pass the frame of images, you can also create and pass a 'collection' of images. The collection could include a combination of 'picture' handles from existing image controls, from file with LoadPicture, or created in various other ways, eg with APIs.
Set mcTree.Images = colIcons
See "Collection Method" and "Frame Method" in the demo form.

New in v026 are Treeview.ImageAdd and Node.ImageUpate methods. You can now also add a new images to the treeview's internal collection and update or change node icons while the treeview is running. The new images could be sourced from anywhere with a variety of methods.


Comment by: Jerzy Orysiak (30-4-2015 19:01:07)

1) Thanks as lot!. It's exactly what I want! (although I've substituted WithEvents commandbarbutton declaration for each commandbarbutton in shortcut menu with a collection of objects - class designed to trap commandbarbutton click event and returning to the parent userform the "clicked" commandbarbutton caption)
2) That solution is fine until I have to transfer workbook to another PC...
To avoid transfering icon files along with workbook I need one way or another to "save" them in a file. The only way I know is to keep them "hard way" is: either using Designer or keeping them in Shapes collection.
Also, I have developed fine "Iconpicker" from office icons and spent quite a lot of time to create db table with fields(faceid, cathegory, tag) for few hundreds of icons out of more then 7000. Sooner or later I will have to migrate to the newer windows and office. I don't know whether next versions of office keep icons faceid the same
way and I am afraid of loosing everything.


Comment by: Peter Thornton (4-5-2015 09:29:54)

You can maintain and distribute your icon images any way that's convenient. That might be in a say a separate folder of image files and get with LoadPicture, probably better with a large number.
With a smaller number you could store them in a workbook in ActiveX Image controls on a sheet, or in say a second userform. You can also store as hex "blobs" in cells (text) though it requires a fair amount of API stuff to convert them. No point to store as Shapes, not simple to convert to StdPicture objects, use aX images.


Comment by: Jerzy Orysiak (6-5-2015 14:59:47)

1) I've tried to use standalone Move method (for changing order of nodes having the same parent in this case) with Node.key as a vAfter /vBefore parameter. It seems it doesn't work that way. It does work when vBefore /vAfter is set to childnodes collection index of destination parent node, available through not included in documentation ChildIndex function.
Am I missing something?
Move sub can be easily modified but I don't want to make any changes in your code.

2) There is no "public" way to change order of root nodes in multiroot treeview. There is no "public" way to add new root before/after (if I have to do it: rewriting roonodes collection with desired order is a solution?)

3) There is no "public" way to cut node and paste as a new root - child of "RootHolder" (?)


Comment by: Peter Thornton (7-5-2015 13:22:11)

Hi Jerzy,
First, adding and "positioning" a new node can be done with the tree.NodeAdd method, eg

With mcTree ' also tvFirst tvPrevious
    .NodeAdd .ActiveNode, tvNext, , "new node"
    ' .Refresh ' if no more to add
End With

All your other objectives, if I follow, can be done with Move. You could put buttons on your form to "move" a node (with its children) vertically up/down the tree, or promote/demote nodes between levels, including rootnodes, even making a child a rootnode or a rootnode a child.

However, I see now the documentation is not correct about vBefore/vAfter for the Move method, these optional arguments should be a "Numeric index of an existing Node in the Destination node's collection of ChildNodes" but not a string Key. Eg to move a node with its children up or down:

    idx = GetColIndex(cMove)
    Set cParent = cMove.ParentNode
    mcTree.Move cMove, cParent, idx -1 ' up 1
    mcTree.Move cMove, cParent, , idx +1 ' down 1

This function will return the position of a node between it's siblings (need additionally to check not trying to move above the first or below the last child)

Function GetColIndex(cNode As clsNode) As Long
Dim i As Long
Dim cSibling As clsNode
    If Not cNode.ParentNode.ChildNodes Is Nothing Then
    For Each cSibling In cNode.ParentNode.ChildNodes
        i = i + 1
        If cNode Is cSibling Then
        GetColIndex = i
            Exit For
        End If
    End If
End Function

If you need to position next to a new sibling when only its Key is known, get its index between its siblings with this GetColIndex function.

The "RootHolder" is not directly exposed but can get it as the .ParentNode of any RootNode. It's a clsNode though not in the Nodes collection. It's what holds it all together!


Comment by: Juan (7-5-2015 16:43:30)

Your class is great! Thanks for share it!!
How can i get the caption of each node?
i've tried using
for i=1 to mcTree.nodes.count
next i

but it doesn't works......

Thanks a lot!!!!


Comment by: Jan Karel Pieterse (7-5-2015 17:47:21)

Hi Juan,

Something like:

Dim cNode as clsNode
For Each cNode in mcTree.Nodes
    var = cNode.Caption


Comment by: jerz (8-5-2015 00:51:38)

Hi Peter,
1) I was wrong. Move method works fine with root nodes (however, looses highlight with shortcuts or command button and activenode is almost invisible). Anyway, I have added DropHighlight sub to my auxiliary standard module .
2) I am unable to make NodeAdd work with root nodes. First:
Call mcTree.NodeAdd(mcTree.ActiveNode, tvprevious, skey, skey)

gives child node instead of sibling (works ok for non-root nodes)
Call mcTree.NodeAdd(, tvprevious, skey, skey)

(omitting vRelative – rather tricky anyway – not a word in doc) works but adds node as a last nodes’ collection item.
I use workaround (addroot aggregated with move).

Copy and Move work fine with root nodes, so (including workaround) I have all basic functions working correctly.


Comment by: Peter Thornton (9-5-2015 12:20:50)

Hi Jerzy,
I don't follow all of what you're doing there but it sounds like you've got it all working.

Indeed the activenode's highlight formats change as the treecontrol gains or looses focus if you included the frame's enter & exit events in the main form (see the demo) to make it consistent with Window's behaviour, it's a feature! However you can easily adapt or override the these highlight changes.


Comment by: Jerzy Orysiak (12-5-2015 13:47:02)

Hi Peter,
below is the part of cmdAddSibling_Click() of your demo:

If cNode.ParentNode.Caption = "RootHolder" Then
        'We have a root node, add another root node
        Set cSibling = mctree.NodeAdd(, , _
                                    "MyUniqueSiblingKey" & lNewItem, _
                                    "New Sibling of " & cNode.Caption & " #" & lNewItem, _
                                    vIcon1, vIcon2)

Any value of the second parameter - "Relation" (also tvchild) gives the same result: adds csibling as last.

Attempt to set the first parameter cnode.parentnode gives run-time error.


Comment by: Peter Thornton (13-5-2015 13:06:47)

Hi Jerzy,
If I follow you want to add a new root node and at the same time position it before or after some other root node. To use the NodeAdd method for this the clsTreeView code will need changing: In Function NodeAdd comment and add these lines respectively

    'If vRelationship = tvChild Or cRelative Is cRelative.Root
    If vRelationship = tvChild Then

then call it like this

Set cSibling = mcTree.NodeAdd(cSomeRootNode, tvNext, etc
     ' or tvPrevious

Could you confirm if this works for your needs, and/or if the change causes a different problem. We will probably change this for the next version though probably not soon.

Alternatively you could use .AddRoot() as normal then use the Move method to place in the order required.

The possibility to add multiple Root nodes was added during development, prior to which we needed to prevent accidental adding of sibling roots. Your particular scenario got overlooked!


Comment by: Jerzy Orysiak (14-5-2015 01:15:55)

Hi Peter,
I have changed mentioned line, however the first line after declaration in NodeAdd should be changed as well (at least I think so). It is not neccesary but if a tree is empty (ActiveNode is Nothing) it prevents from error and/or additional condition checking.
If IsMissing(vRelative) Then

Should be:
If IsMissing(vRelative) Or mcolNodes Is Nothing Then

Now it works (tested).

Anyway, it was not a problem before (I found workaround) and if I wrote about it, that was becouse I think that kind of remarks might have some value for you. I work intensively with treeview and I keep collecting remarks (mainly regarding documentation). When I finish, if you wish, I will send them to you.


Comment by: Peter Thornton (14-5-2015 20:54:01)

Hi Jerzy,
I don't think we ever considered .NodeAdd for adding root nodes. Although it works with the change I mentioned for subsequent root nodes, I think at least for the first node it can be added with .AddRoot, and then never necessary to check if mcolNodes Is Nothing with all other nodes, but no problem to do that.

It looks like you are giving the treeview a very thorough work out! Indeed please send your remarks when done.


Comment by: Chris (15-5-2015 13:39:13)

I'm wondering if you have a vba listview alternative that work both on 32 bit and 64 bit office?


Comment by: Edgar (1-6-2015 11:36:09)

Will this treeview also work in Access 2013 32/64?
Thanks, Edgar


Comment by: Jan Karel Pieterse (1-6-2015 12:34:26)

Hi Edgar,

Yes it should work on Access 2013 as well.
If you could download the Access sample and try it on Access 2013, that'd be great!


Comment by: Edgar (1-6-2015 13:28:55)


Tested on Access 2013 64 bit... Works like a charm...
Many thanks!



Comment by: Chris (2-6-2015 11:55:15)

Steve wrote a comment back on (2/21/2014 3:49:20 AM) saying "If the user first clicks on a node on the Treeview in Form A, the fields on Form B become locked. More accurately, the fields on Form B can receive focus, but they cannot be updated." Peter Thornton came back on the (2/24/2014 3:32:00 PM) and replied.

Well, I'm experiencing a similar problem where the focus seems to be stuck on the Treeview. I can click on an unbound field on Form B (the cursor goes there) but its acting like the field is locked. Was there any further communication with Steve? Did he get his problem resolved?


Comment by: Eduardo (3-6-2015 20:05:41)

I'm really having a lot of trouble in populating the tree
Maybe you can make an entire instruction or tutorial:

I need to create this nods por example:

1. Item 1
2. Item 2
3. Item 3

Then I need to add the followings:

1.1. Item 11
1.2. Item 12
2.1. Item 21
3.1. Item 31

And then:

2.1.1 Item 211


So the tree must be:

1. Item 1
1.1. Item 11
1.2. Item 12
2. Item 2
2.1. Item 21
    2.1.1 Item 211
3. Item 3
3.1. Item 31

How can i locate a specific node (index) so i can add a new child into it?


Comment by: Chris (3-6-2015 22:57:42)

Just as a follow-up answer to my question about focus sticking on the Treeview and for anyone else that might experience this behaviour. I have resolved the problem by simply setting "Tab Stop = No" on the Treeview subform. Everything now works!

Interestingly, the same behaviour can be replicated with the demo project in Access 2000. Just place an unbound text control on frmDemo, press "Demo Treeview", click on any node on the Tree, click in the unbound text control and try and type anything – unbound text control acts if its locked (even though it's not). The same example running under Access 2013 with "Tab Stop = Yes or No" works fine. I haven't tested it under other Access versions so it could well be an Access 2000 special. I know Access 2000 - its still being used! Just thought I'd pass on my findings...


Comment by: Jan Karel Pieterse (4-6-2015 07:29:00)

Hi Eduardo,

You can use this function traverse the nodes and check for their key (if you gave them one) or their Caption:

Private function GetNode(sCaptionOrKey As String, bUseKey As Boolean) As clsNode
    Dim cNode As clsNode
    For Each cNode in mcTree.Nodes
        If bUseKey Then
            If cNode.Key = sCaptionOrKey Then
                Set GetNode = cNode
                Exit Function
            End If
            If cNode.Caption = sCaptionOrKey Then
                Set GetNode = cNode
                Exit Function
            End If
        End If
End Function


Comment by: Peter Thornton (4-6-2015 12:21:21)

Hi Chris,
I couldn't replicate the problem in Access Steve and now you described. But I wonder if your "Tab Stop = No" workaround prevents key tab into the treeview? Ideally we'd want to prevent what you describe and allow tab in and out, and 'notify' the treeview so it can change the active highlight as I described (2/24/2014)

You say it works in Access 2000, we didn't know that! The earliest we thought was 2003, and only if compiled in 2003 but in a system without a later version. It's why we're slow to update the Access demo with the latest version without such a system to hand.

A belated response to your Q (5/15/2015) about do we have a listview, as the saying goes - watch this space!


Comment by: Chris (15-6-2015 07:29:24)

Hi Peter,
Yes, that is the downside of setting “TAB Stop = No”, but I can live with that given the alternative is that every control on the form is locked! Apart from that little glitch it works perfectly fine under Access 2000 and Access 2000 runtime :)

Thanks for your response re: listview, say something was in the works... any chance of an idea of what a time frame could be? 2015, 2016? ;)


Comment by: Eric (15-6-2015 17:00:32)


I've got an odd issue that crops up on form resize, specifically if the use minimizes the form and the form's detail height or width is updated. On detail size update the treeview will terminate itself. The other sub form doesn't appear to be affected, and if I disable the detail update the treeview doesn't terminate itself. So, the logical thing would be to disable the detail update on (max|min)imize, however that doesn't appear to be possible.

I thought this might be an issue with the treeview subform size dipping down to 0, but it never reaches that point. None of the form elements get below 300 twips. The treeview also doesn't terminate until the detail size update event.

The code:

Private Sub Form_Resize()

    ' Disable screen updating
    Application.Echo False
    ' Bypass errors
    On Error Resume Next
    With Me
        ' #### Some Stuff being sized ####

        ' Sub form height update
        .subDetails.Height = .Form.WindowHeight - 2595
        .subTreeView.Height = .subDetails.Height
        ' Detail size update
        .Detail.Height = .subDetails.Height + 150

        ' #### Other stuff being sized ####
    End With
    ' Reenabled errors
    On Error GoTo 0
    ' Enable screen updating
    Application.Echo True
End Sub

So, I'm stumped. For now I'm disabling the detail size update and moving on, but I'm wondering if anyone might have some insight into what might be causing this issue.


Comment by: Peter Thornton (18-6-2015 10:04:40)

Pleased if not surprised to hear it works in Access 2000! Could you clarify the tab issue you mentioned is only in 2000, or if not which versions?

The listview alternative is still coming soon, as it has been for a little while! If you drop me a line privately (see a module header) I'll keep you updated.

I couldn't replicate your resize problem in our demo. You say the "treeview terminates itself" - is that getting done 'nicely' because something's gone wrong? If not sure put a break in clsTreeview.TerminateTree and see what called it.

I take it your Form_Resize() is in the main form, wonder if that conflicts with the Sub ResizeUF() in the subTreeView, try putting a break in it and step through.

Another thing you could try: in Project Properties / Conditional Comp' Arg's
DebugMode = 1
In the VBE, tools/options/general - break on all errors
If there's any error in the class modules when you resize the code should break, step through and 'Resume' to the error line.
Be sure to remove or reset DebugMode = 0 before distributing.

Report back if not fixed.


Comment by: Chris (19-6-2015 07:20:08)

Hi Peter,
Just confirming its only happening in Access 2000. It's a strange one but it can be replicated using your demo access project under Access 2000 easily. It would be great to know what the problem is but I can understand if you can't lay your hands on a copy off Access 2000 anymore!

I've also just emailed you privately regarding getting updates from you on the listview alternative when there is an update :)

Thanks for all your responses and indeed getting back to me – your great work is appreciated!!


Comment by: Andrew Glasgow (22-6-2015 16:43:32)

How can I download the treeview control. There's no link associated with "Download". Do I have to donate first?


Comment by: Jan Karel Pieterse (22-6-2015 17:01:36)

Hi Andrew,

"Download" is just a header, the download links are below that heading. There are three of them:
One for Excel, one for Word and one for Access.


Comment by: Eliton Elias (23-6-2015 19:21:27)

I'm struggling to create a macro to populate the treeview with data from the spreadsheet.
Thanks in advance


Comment by: Peter Thornton (24-6-2015 18:09:39)

Hi Eliton,
Without knowing anything about how your data is arranged it's hard to suggest a macro. There are several approaches.


Comment by: Niels de Nies (29-6-2015 13:44:44)

Jan Karel,
Ik heb -als prototype- in Access een database gebouwd waarmee een product breakdown van een te ontwerpen installatie gemaakt kan worden. Jullie Treeview werkt daarin prachtig.
Nu wordt mij gevraagd het prototype op te schalen naar SQL. Ik zou Access kunnen handhaven als client, maar mijn klant heeft dat liever niet.

Mijn vraag is: kan jullie Treeview ook in een VB omgeving draaien. De code zelf lijkt me niet zo'n probleem, maar ik zou zelf niet weten hoe ik met het ufTreeview scherm uit de voeten moet. Kan je me adviseren?

Vriendelijke groet, Niels de Nies


Comment by: Jan Karel Pieterse (29-6-2015 14:43:18)

Hallo Niels,

VB heeft een eigen treeview die je kunt gebruiken voor zover ik weet. Ik neem overigens aan VB.NET?


Comment by: Niels de Nies (29-6-2015 15:14:24)

Klopt: VB.NET.
Ik had er nog niet naar gezocht. Op is het zich prettig dat er een treeview in VB.NET is, maar ik ben erg tevreden met jullie Treeview. Die ken ik nu en doet alles waar de gebruiker behoefte aan heeft.

Maar goed, ik snap dat het niet opportuun is om naast de standaard van .NET nog een andere treeview te gaan maken.

Dank je wel. En nogmaals, voor Access is die van jullie TOP.


Comment by: Jonathan Brown (2-8-2015 08:57:17)

Is there a way to programmatically change the state of a node without triggering the nodecheck event again?

This may not be necessary as I really just want the treeview to behave the way you have it in the tristate mode. Specifically when I click a parent node, all of it's childnodes update accordingly. But then I want it to execute some code after all the nodes are finished updating.

I've found that when I put code into the mctree_nodecheck event it repeats that code for every child node. Following the debug I can see that it raises the nodecheck event each time a checkbox changes its state.

I've tried using application.enableevents = false to prevent it from retriggering the nodecheck event when a checkbox's state changes but it doesn't seem to work the way I want it to.

Would I have to create something like a new node_aftercheck(cNode as clsNode) event that'll run after the nodecheck event? Is that possible?


Comment by: Jan Karel Pieterse (3-8-2015 16:23:54)

Hi Jonathan,

That is the beauty of this control: you can massage it to do exactly what you want.

One way to have this work is by adding a public boolean to the clsTreeView class (at the top):

Public NoEvents As Boolean

Then in your userform you can temporarily switch off events by setting:

mcTree.NoEvents = True

Of course at the end of the code you need to reset:

mcTree.NoEvents = False

To make this work, the clsTreeview needs this line of code in any event, the most important one being:

Friend Sub NodeEventRouter

In that sub, the first line of code should become:

If NoEvent Then Exit Sub

Add that code to any routine that contains the RaiseEvent line of code and you should be good to go.


Comment by: Ken (6-8-2015 03:41:14)

Sorry to be dense, but I am not seeing where I can locate some array or collection or similar from which I can extract a list of checked boxes? For example, I checked 4.0003 and 4.0005, which also selects the parent node...How can I iterate through and use these 3 checked items in my next operation?


Comment by: Jan Karel Pieterse (6-8-2015 11:04:45)

Hi Ken,

You would traverse the nodes of the tree like so:

    Dim cNode As clsNode
    For Each cNode in mcTree.Nodes
        If cNode.Checked Then
            'Now do something
            'Or don't
        End If


Comment by: Anthony Powers (17-8-2015 15:28:59)

I have studied your example for many hours. I am amazed at your work!
May I ask a question concerning the Access database?
If I add more columns to a table, just as an example, “Gender” to tblStudents, how can I change the forecolor of student names (nodes) based on their gender criteria, and without this criteria being in the treeview. Or if I add a true/false column to tblClasses, and do not want this to show up in the treeview, but want to change the forecore of “Class” nodes based on true/false criteria how can I do this? I’ve tried everything but can’t figure it out.
Thank you very much for your assistance.
Kind regards,


Comment by: Jan Karel Pieterse (17-8-2015 17:30:33)

Hi Anthony,

You would set the ForeColor property of the node in question when you are loading the nodes into the tree.
If you change any formatting of a node once the tree is already displaying, you need to refresh the tree:



Comment by: Anthony Powers (18-8-2015 00:29:35)

Hi Jan,
Thank you for your response. Much appreciated!
My problem is with the criteria. How can I refer to information in the tables? For example: If Gender is Male then node text changes color. Can you provide a simple example?
Thank you for your support and kindness,


Comment by: Jan Karel Pieterse (18-8-2015 10:05:17)

Hi Anthony,

You would read the information of tblStudents and extract the gender information belonging to each node and color the nodes accordingly. Some air code:

Sub ColorNodes()
    Dim oRS As ADODB.Recordset
    Dim cn As ADODB.Connection
    Dim sSQL As String
    Dim cNode As clsNode
    Set cn = CurrentProject.Connection
    Dim mcTree As clsTreeView
    For Each cNode In mcTree.Nodes
        Set oRS = New ADODB.Recordset
        sSQL = "SELECT Gender FROM tblStudent WHERE StudentID = " & cNode.Key
        oRS.Open sSQL, cn, adOpenStatic, adLockReadOnly
        If Not (oRS.BOF And oRS.EOF) Then
            'Student found
            If oRS.Fields("Gender").Value = "Male" Then
                cNode.ForeColor = vbBlue
                cNode.ForeColor = vbRed
            End If
        End If
End Sub


Comment by: Anthony Powers (18-8-2015 10:31:41)

Hi Jan,

This is what I'm looking for. Thank you so very much! You are wonderful!



Comment by: Josh Feuerstein (18-8-2015 19:53:10)

I've been emailing with the devs about some questions but I thought this one might get a more generally useful answer to all:

If my control is in TriState mode, and I check a box, is there any way to detect which checkbox was *actually* clicked by the user?
event cascades and triggers for every single node up and down its branch, and that can be less than helpful.


Comment by: Peter Thornton (19-8-2015 13:45:14)

Events to trap dependent TriState changes were added in v026, actually on request (see the Versions sheet in the Excel demo).

In typical scenarios which particular checkbox triggered the change is not important, but knowing all changes that have occured as a result is. The current arrangement can also help the developer, eg after programatically changing just one checkbox, all parents to the root and child checkboxes that may have changed as a result are returned in the chain of events. In turn a DB can be simply and fully updated in real time if required. Without that the developer would need to calculate the logic (not easy) or compare against record of previous checked nodes in the the entire treeview.

If the events are counter productive for your purposes (I'd be curious to know why) you could customize the treeview to only trigger the event for a single manually changed checkbox. Or, add an additional flag in the event to indicate it was the one that triggered the chain (possible but complicated). Alternatively in your own form record the time of the event but only if the time is more than say 0.2sec since the previous change; if more than 0.2sec assume the event is teh one clicked by the user as it will be the first event in the chain, otherwise it's dependent event. A third approach would be to use v025!


Comment by: Josh Feuerstein (19-8-2015 17:38:33)

The simple reason why is that the ultimate state is good, but I might want to provide some feedback to the user, or log which node was clicked for debugging purposes, etc.


Comment by: Peter Thornton (20-8-2015 18:33:17)

Typically (but see below) the node receiving user's checkbox change is the ActiveNode. So you can determine if a checkbox was changed directly by the user or due to its TriState dependency

' in the NodeCheck event
    If cNode Is mcTree.ActiveNode Then

To ensure the user changed checkbox node is the ActiveNode look at mctlCheckBox_Click() in clsNode. There are 3 If/End-If's, swap the 2nd and 3rd If's to ensure the node is active before NodeCheck event is triggered.


Comment by: McPegasus (2-9-2015 08:57:02)


How can I change the font size?

Thank you.


Comment by: Jan Karel Pieterse (2-9-2015 10:22:24)

Hi McPegasus,

The Excel demo contains a Font size control, check out the control's event to find out how to change the font size.


Comment by: McPegasus (2-9-2015 12:06:59)

Fantastic Jan!

Thank you very much. I am looking for a substitute to the TreeView and now, I prefer your system ;).

Just missing OLEDragDrop, OLECompleteDrag, etc.), but we will know how to live without it.

Built-in example: Treeview-25 compiled in 2003

Private Sub InitializeDemo1()


    With mcTree

        Call .NodesClear ' if reusing an exisiting treeclass

        .AppName = AppName

.TreeControl.Font.Size = 16



Comment by: John Bingham (7-9-2015 05:35:39)


Nice work!

In design view the tree sub-form insists on being at the front; This can be very inconvenient when the tree is located on a tab control, and many other controls are actually sharing the same piece of screen real-estate.

Is there any way to control/prevent this?




Comment by: Peter Thornton (7-9-2015 13:22:12)

Hi John,
In a quick look it seems it's not possible to prevent the subForm from being at the front in design view. As a workaround for use in design, try this in a normal module and run from the VBE

' adapt for Office 64
Private Declare Function ShowWindow Lib "user32" (ByVal hwnd As Long, ByVal nCmdShow As Long) As Long

Sub ToggleSubFormVis()
Static ShowHide As Long
    ShowWindow Form_subTreeView.hwnd, ShowHide
    ShowHide = IIf(ShowHide = 0, 1, 0)
End Sub

Keep in mind we are Excel guys here and this is the first thing that came to mind, maybe the experts in the Access forums may have a more conventional solution!


Comment by: Tom (11-9-2015 22:25:11)

Great job guys! I was looking for a small treeview example due to the 32/64 'broken' dll stuff.
This is not a small example, this is great work!




Comment by: David Reid (18-9-2015 02:26:39)

Could you please help me to understand why your Access Treeview sample is not loading properly? I'm getting the error message 438 (Ojbect doesn't support this property or method) at the line: lngIndex = ctl.TabIndex in the subTreeView module. That problem then cascades into other errors. I just downloaded the file a couple of minutes ago and my best guess is that a reference is not set--but I don't see that any are missing. Thank you.


Comment by: Peter Thornton (18-9-2015 13:16:36)

Strange the code breaks there as that line is under 'On Error Resume Next'.
In your VBE Tools/Options/General do you have 'Break on all errors' checked? By default that should be 'Break on unhandled errors'.


Comment by: David Reid (18-9-2015 18:16:22)

You're right, Peter. All is good after I set the default back to 'Break on Unhandled Errors". Thank you.


Comment by: H.KILIÇ (4-10-2015 04:02:57)

JKP you are real web-hero.
I exported your codes to AutoDesk Inventor 15, and willing to try making a VBA TreeList Control; something like treelist in order to specially duplicate my large project's part&assm files; on Win8-64, need some suggestions. Thanks for the treeview codes. Te&#351;ekkürler.


Comment by: Simon (14-10-2015 16:48:56)

In the "Instructions" tab of the Excel files it has the following instruction:

Add a reference for the Treeview Frame container
Set mcTree.Control = Me.frmTreeControl

However, the line should be:
Set mcTree.TreeControl = Me.frmTreeControl

Please can you update. Thanks.


Comment by: Thomas Koester (15-10-2015 13:55:08)


I would like to scroll the treeview with the mouse wheel (I am on Access).

Do you think that it would be possible? How would you do it?

It would be sufficient if you could get me started with some hooks and hints. I am already fairly familiar with your code due to some other custom patches.



Comment by: Jan Karel Pieterse (15-10-2015 14:49:22)

Hi Thomas,

Have a look at this thread on MrExcel:


Comment by: Thomas Koester (19-10-2015 08:11:32)

Hi Jan,

thanks a lot. That's what I was looking for.



Comment by: Jaap van der Sijp (20-10-2015 09:18:39)

If you use icons in the tree view and FullWidth=False, then the icons are placed in the label control. When clicking the label the background colour gets changed (default blue), but the all white pixels in the icon also become blue. Going to FullWidth=True solves that , but may not be what you want. An alternative is to edit the image and change all white pixels (FFFFFF) to "nearly" white (FEFEFE). Then only the text part background will turn blue. this looks a lot cleaner.


Comment by: Peter Thornton (20-10-2015 21:00:06)

Thanks for flagging the typo in the instructions sheet, the demo has been updated.

As you say with fullwidth=true icons appear in their own image controls, but with fullwidth=false icons are embedded in the node label.

With fullwidth=false, normally which RGB pixels in the icon adopt label's backcolor (eg when active and given vbHighlight) depend on which, if any, RGB was defined as the mask colour when the image was saved.

It seems the demo icons were given a white mask, I'm sure there was a good reason at the time!


Comment by: Paul Katz (24-10-2015 22:27:05)

Hi - thank you for developing this control and making it publicly available. It seems that I have a good business need for it. I'm hoping you can answer a couple of questions before I dive in to decided if it's the right control to use.

I'm developing an Excel app, and have data which will source the TreeView control in a worksheet. The size of the table is about 7 columns x 4000 rows. The data are sorted left to right (root, lvl1, ..., lvl6). Users will be expected to navigate the tree and drag/drop different node selections to list boxes on my form.

(1) Will the control reasonably manage data of this size?
(2) Do you have a BuildTreeFromTable() type routine to automatically populate the control from this Excel range (or would I need to program that myself)?

Thanks again for your time,


Comment by: Jan Karel Pieterse (25-10-2015 14:24:49)

Hi Paul,

That number of nodes should not be a problem for the control. But it is easy enough to test by just adding them all to a single rootnode.

Then if you're happy with the performance, writing a bit of code that reads your table and builds the tree is fairly straightforward. Perhaps if you expand all comments on this page you'll find some sample code?



Comment by: Pavlo (12-11-2015 21:20:02)

Does this tree view allows word wrap, if node text is too long?
If not, are there any chances to add such feature?


Comment by: Peter Thornton (16-11-2015 12:56:55)

Hi Pavlo,
Normally treeviews do not display word-wrap or multi-line text, however the horizontal scrollbar should automatically adjust to accommodate the longest text in any node.

If your source text includes vbLf and/or vbCrLf (or in Mac vbCr), replace these with "¶ " (Chr(182) + space) before populating, and convert back if reading.

Try manually editing a node (EnableLabelEdit=true, then dbl-click or F2 on a node) and entering or pasting some long multi-line text. Then F2 again and have second look. We've done a lot of work to try and handle this nicely!


Comment by: Brain S. (28-11-2015 03:20:07)

First, thanks for your great control and alternative to the common controls mess.

I'm trying to use your treeview in Access 2013 and it works fine with one exception. I have it inside a tab control page. When I select a node all other controls within the tab page flicker like they're being repainted a few times. It's very distracting. There is no flickering if the treeview is placed outside the tab control. I really need to place the treeview inside the tab control so moving it out isn't an option.

Any suggestions on how to eliminate the flicker?



Comment by: Peter Thornton (2-12-2015 10:32:24)

Hi Brian,
If you want to send me (or put on a file sharing site) a non sensitive file that replicates what you describe I'll have a look. My address is in the headers.


Comment by: Jeff Silcock (9-12-2015 15:17:50)

Been using your TreeView control for a while now and it is brilliant! - thanks very much. Wanting to add a second control, and with this one would like to be able to expand/collapse all the nodes with one click. I presume this must be possible, but would be glad of some steer how to do it please.


Comment by: Jan Karel Pieterse (9-12-2015 16:42:44)

Hi Jeff,

I think what you need is the ExpandToLevel method of the clsTreeview class, which you can call from within your userform.


Comment by: Joel (9-12-2015 19:51:26)

Thank you so much for developing this tool. I only just learned that the ActiveX treeview control is not supported in 64 bit Access when a user of one my applications found that it was no longer working. This is a real help.

I can see that you can have multiple instances across multiple forms, however is it possible to have multiple instances in the same form? I typically use a single master form with a tab control. I would like to place the subTreeView form in multiple tabs on the same form but so far haven't been able to.


Comment by: Joel (9-12-2015 21:06:46)

Just to follow up from my previous question re multiple treeviews in a tab control...
I can get two treeviews to load in the same form, however the click event (mcTree_Click) is only applied to one of the controls.

How does the mcTree_Click event become associated with the control? I would like to associate a different click event with the second control.

Many thanks if you are able to help.


Comment by: Jan Karel Pieterse (9-12-2015 21:15:25)

Hi Joel,

You will have to define another variable for the second treeview, e.g. mcTree_2 and add it's click event (and any other events you need) to the userform's code.


Comment by: Joel (9-12-2015 21:46:10)

Many thanks Jan. Your solution worked perfectly.


Comment by: Jeff Silcock (10-12-2015 12:29:41)

Hello Jan that only works so far. Yes if the tree is closed and you call ExpandToLevel(2), the + gets changed to - on each top level, but you have to then click on the first node which closes that node but leaves all the others expanded. If you then issue a ExpandToLevel(0) all - get changed to +, but the lower levels on node remain visible until you again click the first one, which closes all other but expands that one!!

I must be missing something if you can help please


Comment by: Jan Karel Pieterse (10-12-2015 22:59:18)

Hi Jeff,

Apologies for misguiding you a bit. After the expandtolevel you must force the tree to reload by using

mcTree.BuildRoot False


Comment by: Jeff Silcock (10-12-2015 23:08:35)

Jan - Thanks, spot on, except that I had to make the BuildRoot routine Public before I could refer to it


Comment by: Jan Karel Pieterse (11-12-2015 12:01:10)

Hi Jeff,

Sorry about that, I forgot that BuildRoot is private and that I should have advised to use the Refresh method.


Comment by: Joel (5-1-2016 19:14:27)

Hi Jan,

I just found a small bug. In the Get Previous subroutine in the clsNode class module, the set statement should read:
    Set Previous= .ChildNodes(I - 1)
rather tham
    Set NextNode= .ChildNodes(I - 1)

Many thanks again for developing this.


Comment by: Peter Thornton (6-1-2016 15:47:02)

Hi Joel,
Indeed there was bug in Get Previous but it was fixed in build-026. If you're using the Access version (hmm we must get around to updating it) you might want to get the Excel download, grab the two class modules and at the top of the clsTreeview change "Excel" to

#Const HostProject = "Access"

See the "Versions" sheet for other changes


Comment by: Pete Danes (8-1-2016 00:32:18)

This looks insanely useful, but I haven't been able to make use of it. Your demo works beautifully, but I have been unable to import this stuff into another database. I can't import UserForm1, nor do I even see how that happens. All my databases have only three object categories: Microsoft Office Access Class Objects, Modules, and Class Modules. Your demo database also has a Forms category. Where does this come from? How do I get it into my database? My import efforts lead to an error: "Line 2: Property OleObjectBlob in ufTreeView could not be set." logged in the Temp folder. The database also says Out of Memory when I try this. The Forms category exists in Excel and Word, but not normally in Access, and I don't know how to get it there.


Comment by: Jan Karel Pieterse (8-1-2016 12:08:10)

Hi Pete,

The Demo database has a "How do I?" button on its startup form, which tells you how to implement it in your own database. It includes some pointers on the userform bit too.


Comment by: Konstantin (18-1-2016 20:46:08)

Good afternoon. I work with the program SolidWorks. This is a grand CAD software. But in terms of tools for user attributes (user properties), it is extremely unfortunate.
And above all, what I was looking for the last 15 years - a TreeView in Excel. Thank you very much. I still have to sort out the tool c. But the main thing for me are opportunities, which are still not there.
I understand that you do not have to know what SolidWorks. Therefore, I say that because of user data formed an overview of engineered project, which add and use, and accountant and economist, and the storekeeper and buyer, and the technology and master in production. With your instrument, I can make the right data available to all. And while I do not have to persuade everyone to buy a license. This is a huge deal.
And the question. Do you plan to expand your item for ListView element layering data. After all, as I understand it, at the moment TreeView - it's the same hierarchical ListView, but in one column.
And, if possible expansion of the TreeView, the second request - possibility of multi-line entry in the cell. This feature is especially painful for custom properties SolidWorks.
P.S. I know that there is a Microsoft Hierarchical FlexGrid, but connected to the VBA project it is difficult.
You can use the sheet like Excel, but these quickly grow beyond the screen. Additionally, the program itself is maximized. While the basic form to fill in the properties of the screen takes and leaves the visual access to the working model of the project in SolidWorks.


Comment by: Dominik (25-1-2016 21:27:17)


how is it possible in Access to call the

Private Sub mcTree_Click(cNode As clsNode)

from a button?

I want to click the button and show for example my knod "ABC899" in my treeview. And i also want to the mcTree_click event to run the action behind my knod.


Comment by: Jan Karel Pieterse (26-1-2016 07:28:40)

Hi Dominik,

You can, if you pass to it the node you "clicked":

Dim cNode As clsNode
'Some code that sets the right Node, such as:
Set cNode = mcTree.ActiveNode
mcTree_Click cNode


Comment by: Paul Charbonnet (27-1-2016 18:58:47)

Can your control be compiled into an OCX using VB6. I tried, but it is likely dependent on objects that are part of Excel VBA and not VB6


Comment by: Jan Karel Pieterse (27-1-2016 19:37:08)

Hi Paul,

There is no point in using our control in VB6 as VB6 is strictly 32 bit and can hence use its own treeview control.


Comment by: Paul Charbonnet (27-1-2016 21:37:18)

The MSCOMCTL.OCX that contains the treeview has serious compatibility problems with Windows 8 and above


Comment by: Jan Karel Pieterse (28-1-2016 07:35:25)

Hi Paul,

Got it. I'm afraid we have no plans to port the thing to VB6. But you are free to use our ideas to try and port it yourself. I think the base principles would be very much the same.


Comment by: Joseph Volence (29-1-2016 19:50:34)

I have this working very well in Access 2010, but there is a slight delay when building a large tree that I did not experience with the mscomctl tree.

I have the tree subform in a popup form, and this causes the whole form not to display until the tree is built.

Is there a way to build the tree async in the background, and then display when it is fully built - while, in the meantime, allowing the rest of the form to be usable?

I toyed with the idea of pre-loading the popup form and setting it to "visible=false" before it is used, but I can't get the form to stay hidden. Once the tree is loaded, it unhides the form.    I also tried the "acHidden" argument of docmd with the same result.



Comment by: Jan Karel Pieterse (29-1-2016 20:55:34)

Hi Joseph,

I will ask our Access expert to have a look at this. In the mean time, we are also developing a high performance treeview control. This one will not be for free however.

If you are interested, let us know, so we can arrange a beta version for you to try.


Comment by: Yossi Dukes (3-2-2016 21:14:53)

Hi, I am very interested in your treeview control. I'd like to be part of the BETA program if possible.


Comment by: Jan Karel Pieterse (4-2-2016 11:09:44)

Hi Yossi,

You can already use the control. as far as I'm concerned it is past beta stage and ready for production use.


Comment by: Valton (5-2-2016 19:28:11)

Very good development. They are at another level of programming.


Comment by: Anthony Warner (22-2-2016 03:51:04)

Hi Jan, I am very interested in your high performance treeview control. I'd like to be part of the BETA program if possible and If you are at a production level I am very interested in purchasing it once it has been checked out.


Comment by: Anthony Warner (22-2-2016 03:58:15)

Hi Jan,

I overlooked in my previous comment that me end use of your production model "high performance treeview control" will be in access as I am experiencing extended load times in my forms with the winforms referenced coding of the demo.
thank you very much for having addressed the treeview issue in access as the MSCOMCTL.OCX has been extremely difficult to revise in production front ends as access has matured.

Many Thanks
Anthony Warner


Comment by: lukasz (24-2-2016 03:15:19)

Hi Jan,

Have you found a way to speed up adding many (over 5000) elements to the userform?


Comment by: Jan Karel Pieterse (24-2-2016 08:28:18)

Hi lukasz,

Yes we have, but that version is not available for free. Contact us at the email address below if you'd like to do beta testing.


Comment by: Gianluca (26-2-2016 15:34:49)

i'm geting in trouble because
'Private mUF As ufTreeView'
user-defined type not defined...
can you support on this?
I'm using access 2016 64 bit.
Thank you


Comment by: Jan Karel Pieterse (26-2-2016 16:01:17)

Hi Gianluca,

Have you studied the docmentation that is in the Access demo file (the "How do I?..." button)? Especially the part about modStartup.


Comment by: Antonio Salva (10-3-2016 22:30:56)


On clsNode code, Build 025, there is a bug (error) in the Previous Property Get procedure.

Yours is:

With Me.ParentNode
        For Each cNode In Me.ParentNode.ChildNodes
            i = i + 1
            If cNode Is Me Then
                Exit For
            End If
        If i > 1 Then
            Set NextNode = .ChildNodes(i - 1)
        End If
    End With

And it must be:

Set Previous = .ChildNodes(i - 1)

Best regards.

Antonio Salvá (Spain).


Comment by: Jan Karel Pieterse (11-3-2016 10:28:27)

Hi Antonio,

Thanks, we know and was fixed in the current build.


Comment by: Mike C (NZ) (15-3-2016 22:50:19)

A great piece of work. Wish I had this 10 years ago working with treeview in MS Access

Well done guys.


Comment by: Jan Karel Pieterse (21-3-2016 07:08:26)

Hi Brent,

Thanks for the tip.


Comment by: Loïc Le Roux (18-4-2016 16:44:06)

Hi and thanks for sharing your work.

I'm far from being an expert but I'm trying to use this treeview in a project with Access 2016 64-bit and i'm facing some issues.

The main one is that the subform doesn't see its parent (in the following function), when I open the main form, and then closes by itself.

Private Function IsSubform() As Boolean
    On Error Resume Next
    IsSubform = (Not Me.Parent Is Nothing)
End Function

The demo works well but I can't figure what's wrong with my configuration.

Any help would be greatly appreciated.

Best regards,


Comment by: Loïc Le Roux (20-4-2016 10:47:29)


Thanks for publishing my comment but I finally found my mistake and I think you can remove it.

The issue was entirely my fault as I had pasted the line

Private mOtherFrm As Form_frmDemo

without changing form name.

I'm going deepest in using your treeview and i'm still very impressed by the work you done.

Keep on the good work !

Best regards,


Comment by: Jan Karel Pieterse (20-4-2016 11:28:46)

Hi Loïc,

I'm glad you found the problem!


Comment by: Thomas Magdahl (29-4-2016 22:56:47)


Is there any way to change the ImageExpanded of a node.
I am using the expanded icon to show the state of a node.
It is not working and if it should I am not beeing able to figure it out.

        icono = "DiscardedIcon"
        mcTree.ActiveNode.ImageExpanded = icono

If I reload the tree structure with the new icon reflecting the new state it displays ok. I am only trying to avoid replace the node with a new one, that has the same info but different expanded icon.




Comment by: Jan Karel Pieterse (30-4-2016 11:38:45)

Hi Thomas,

When you create a node, you can set its ExpanderImage property, to which you pass the collapsed and expanded icons. The control then automatically adjusts the image on click. See the demo file, routine called "InitializeDemoIcons" in ufDemo.


Comment by: Thomas Magdahl (2-5-2016 13:37:56)

Hi Jan,

I know; it works fine when I buid up the tree at program start, from data on an Excel worksheet. At that time I figure out the state of the excel row (very simple, based on the date) and generate its associated node with the correct colapsed and expanded icon. My problem is with the code I provided in the previous post, which happens when the user interacts with the node of the displayed tree. He changes the state of the active node (date changes at the excel row) and the expanded icon of that node has to change acordingly. Sorry if I missunderstood your answer and it only can be done at node creation as in the demo?, but then the property is available and I suspect it should work and I am doing a mistake. At the demo the propperty is only used for retrieving the value but it is not changed at run time with the nodes already created.

I started to use your tree class a year ago and left the project by side for other needs. I started this days to continue on it, and I am again surprised by how good it is. My only wish would be to have 32x32 icons, but it is a minor issue for me. If it already had 32x32 I would probbably desire 64x64 :-)


Comment by: Jan Karel Pieterse (2-5-2016 13:55:47)

Hi Thomas,

Theoretically it should suffice to refresh the tree after changing the expanded state of a node. But you do have to add the right icons to the nodes when you create them.

I expect if you create those icons yourself you can use any size you like :-)


Comment by: Thomas Magdahl (2-5-2016 14:22:45)

Hi Jan,

Sorry for being such a pain in the neck.

If you add the line surrounded by asterixes in the demo code and at run time (with icons) double click on a node and type 456, I expect the expanded icon to change to "GreenTick".
If a refresh is necesary you can run the code and add a child to another node, and the ImageExpanded will be still the same and not "GreenTick", despite a refresh that must have be requested.

Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)

' Validate user's manually edited node here

    If Len(NewString) >= 3 Then
        If NewString Like ("*123*") Then
            MsgBox "123 is not allowed !" & vbCr & "but try 456", , AppName
            Cancel = True    ' undo user's change
        ElseIf NewString Like "*456*" Then
            NewString = Replace(NewString, "456", "789")
            cNode.ImageExpanded = "GreenTick"
        End If
    End If

End Sub




Comment by: Jan Karel Pieterse (2-5-2016 18:01:15)

Hi Thomas,

Seems there is some roomm for improvement :-)
This appears to work:

cNode.ImageExpanded = "GreenTick"
'Kludge to update the icon
cNode.Expanded = Not cNode.Expanded
cNode.Expanded = Not cNode.Expanded


Comment by: Thomas Magdahl (2-5-2016 19:19:07)

Hi Jan,

Worked out fine. Thanks for your kind help.
Yo can not imagine how important your code (and the calendar you recomend) are for the portability of my simple application.




Comment by: Jan Karel Pieterse (3-5-2016 08:56:34)

Hi Thomas,

You're welcome!


Comment by: Jan Karel Pieterse (3-5-2016 16:58:15)

Hi Thomas,

Seems I overlooked an important method and property. Changing the image of a node is as simple as:

cNode.ImageExpanded = "GreenTick"


Comment by: Tom (30-5-2016 09:04:35)

Hi gentleman,
Thanks for your hard work!
I'm currently integrating XLS and Selenium Webdriver into Excel VBA, using your TreeView as running instance (or workbench).
Very nice to have a pleasant GUI.

One question: do you happen to have an alternative for cutcopy by keys, but using some draganddrop function?

Kind regards,



Comment by: Jan Karel Pieterse (30-5-2016 18:00:56)

Hi Tom,

The pro version of the treeview features better performance and drag and drop. It is in Beta right now.


Comment by: Damian Mamani Castro (11-6-2016 11:53:10)

it is good.


Comment by: David (12-6-2016 17:49:25)

Hi, There is a way to move nodes between siblings (up/down)

I tried

If Not mcTree Is Nothing Then
        Set cNode = mcTree.ActiveNode
            If Not cNode Is Nothing Then
            idx = GetColIndex(cNode)
            Set cParent = cNode.ParentNode
            mcTree.Move cNode, cParent, idx - 1

but it don't work


Comment by: Peter Thornton (14-6-2016 10:28:56)

Hi David,

Your example should work, though obviously need to ensure vBefore:=idx-1 or vAfter:=idx+1 are between 1 and count of siblings.

When done (after moving any nodes or anything else that's going to change what's displayed) need to call mcTree.Refresh

FWIW our long awaited Pro version (alpha due this week) includes built-in nudge up/down, promote/demote sideways functions, amongst far more important features!


Comment by: Bill Dowton (17-6-2016 14:34:14)

A brilliant piece of work and I'll definitely make a donation! :) I have the control working but am struggling with the icons. If I reference the ones in the demo everything works fine but I don't know how to create a frame with images although I've spent several hours trying :( I've also tried loading my images into the frImages but this doesn't help either. Can you help?
Kind regards
ps I'd be interested in the Pro version. Any idea when it'll be available?


Comment by: Peter Thornton (18-6-2016 14:16:06)

Hi Bill,

For some reason adding pictures to image controls in a Frame in Access don't seem to stick. Try doing same in a temporary form in say Excel, then select and copy the image controls to the Frame in Access, can do all in one go.

The treeview 'pro' and listview alternative are at alpha stage, contact us off-line if interested to test either.


Comment by: Bill (29-6-2016 16:06:28)

Hi Peter

I now have the control working (almost) perfectly. I say "almost" because I can't get the icons to resize. I've tried resizing them myself, changing font sizes, node height etc. but they just stay the same. Any idea what I could be doing wrong?


Comment by: Jan Karel Pieterse (29-6-2016 16:57:06)

Hi Bill,

You should create larger icons yourself and update the picture properties of each icon in the icon frame on the userform.


Comment by: Peter Thornton (29-6-2016 17:55:51)

Hi Bill,
It depends what you mean by "get the icons to resize". If you mean size the control (label or separate image control depending on 'fullwidth'), change these constants to suit:
mcIconSize and mcIconPad (in points, typically in Windows 1 pixel = 0.75pt)

But if you mean resize the actual image you need to size the original source to suit and update as Jan Karel suggested.

FWIW sizing of icons to the supplied image is handled automatically in the 'pro' version. Did you get my reply in response to your enquiry about the 'pro' ?


Comment by: Pete Danes (30-6-2016 16:03:30)

Is there a hard-coded limit in this control? I am working on an implementation that has around 20,000 nodes, up to 23 levels deep. Can I expect any difficulties with this?


Comment by: Jan Karel Pieterse (30-6-2016 16:08:03)

Hi Pete,

No limits in the control, but with that many nodes the pro version is probably your best bet.

I'll forward your details to Peter Thornton.


Comment by: Tony Matyas (8-7-2016 08:45:26)

maybe this small code modification can provide some help using TreeView Demo Version 026's documentation sheets:

When I tried clicking or sorting procedures/properties drop downs on the documentation sheets (clsNode, clsTreeView) this always resulted in 1004 Error (the name object doesn't refer to a range; the reference to a constant or a formula Fails, ...). I don't know this to be an exceptional error, however.
My Work around simply changes 2 code lines in module modDocNav from .RefersToRange to .RefersTo
' -----------------------
' a) PT, original code DrpDwnProcedures_Change
' -----------------------
' sProc = ActiveSheet.Names("ProcsList").RefersToRange(idx, 1)    
sProc = Range(Sheets(ActiveSheet.Name).Names("ProcsList").RefersTo)(idx, 1)

' -----------------------
' b) PT, original code SortProcListing
' -----------------------
' Set rng = ws.Names(sName).RefersToRange
Set rng = Range(Sheets(ws.Name).Names(sName).RefersTo)


Comment by: Peter Thornton (8-7-2016 16:06:22)

Hi Tony,
Not sure why you're getting that error. AFAIK it's been working for most users for a long time with no problem. Could you send me the file so I can have a look. Also say which Excel version you're using.


Comment by: Alan Santos (22-7-2016 23:35:24)

Hi!Excellent job! In the TreeView I have a doubt:

How can I adjust for when I start the Form to return the tree that was exported for the last time and that is in the "dump data" sheet?

Thanks a lot and congratulations.


Comment by: Peter Thornton (1-8-2016 15:45:16)

Hi Alan,
Thanks for your comments but not sure I understand your question - are you saying you want to reload the data that was dumped by the "dump data" example in Excel?


Comment by: Tony Matyas (2-8-2016 08:42:14)

Hi Alan,

a hint to clarify your problem: it seems to me the Data Dump procedure has been deactivated in the 026 Demo Version just for temporary testing, so that no data dump is executed when clicking to the related command button. If you have a look at the ufDemo cmdGetData_Click, you should possibly find two inserted code lines, the second one stops the procedure with an 'Exit Sub'.


Comment by: John Murphy (5-8-2016 14:41:54)

I have commented out the 'Exit Sub' in 'cmdGetData_Click' procedure (as per Tony Matyas' suggestion) and 'dump data' now works beautifully but, how do I get it back?

Thanks and congratulation on a brilliant piece of work!


Comment by: Jan Karel Pieterse (5-8-2016 18:42:12)

Hi John,

There is no code in the demo to read the range back into the treeview I'm afraid.


Comment by: Jan Karel Pieterse (8-8-2016 14:34:02)

Hi John,

You can read the data as dumped from the tree with code like this:

Private Sub cmdReadData_Click()
    Dim vData As Variant
    Dim lLevel As Long    'levels are in columns
    Dim lRow As Long
    Dim nRoot As clsNode
    Dim nNode As clsNode
    Dim nParent() As clsNode
    Dim lPrevLevel As Long

    With mcTree
        vData = ActiveSheet.UsedRange.Value
        ReDim nParent(1 To UBound(vData, 2))
        For lRow = LBound(vData, 1) To UBound(vData, 1)
            If Len(vData(lRow, 1)) > 0 Then    'new rootnode
                Set nRoot = .AddRoot("Root_" & Format(lRow, "00001"), CStr(vData(lRow, 1)))
            End If
            For lLevel = LBound(vData, 2) + 1 To UBound(vData, 2)
                If Len(vData(lRow, lLevel)) > 0 Then
                    If lLevel = 2 Then
                        Set nNode = nRoot.AddChild(, CStr(vData(lRow, lLevel)))
                        Set nParent(lLevel) = nNode
                        Set nNode = nParent(lLevel - 1).AddChild(, CStr(vData(lRow, lLevel)))
                        Set nParent(lLevel) = nNode
                    End If
                    lPrevLevel = lLevel
                End If
    End With
End Sub


Comment by: John Murphy (8-8-2016 16:16:27)

That's absolutely BRILLIANT!!!

I can now make changes within the TreeView, dump them and then reinstate them, which is exactly what I wanted.

Thanks very much for your help and efforts, and for responding so quickly.

Thanks a million!


Comment by: Jan Karel Pieterse (8-8-2016 16:43:43)

Hi John,

You're welcome!


Comment by: Adam Taylor (8-8-2016 22:36:07)

Hi - GREAT product, thanks for sharing. I have an Access-related issue in relation to this. I've got the treeview shown as a subform, and sometimes if it falls over it can revert to showing with the flags. It's working great other than strange behaviour in the event of an unhandled error in my application.

Is there a way to check for this happening and protect against it? Some simple command that can switch it back on? Or do I need to close and re-open the form as seems to be the case at present?

Thanks in advance,



Comment by: Jan Karel Pieterse (9-8-2016 09:23:17)

Hi Adam,

My guess is that the treeview reverts to the default settings when your VBA code runs into a runtime error, but I can't really tell :-) I'm surprised it even keeps showing as I'd expect a code reset to remove the tree altogether?


Comment by: Peter Thornton (12-8-2016 15:02:05)

Hi Adam,
Can you repro' when "it falls over". Add something to flag or debug when the subform's open and close events fire.

In some scenarios the subform seems to want to close and immediately reopen, eg print preview and report view. If that happens the userform that contains the treeview will have gone which might explain what you describe.

If this sounds familiar to any Access folk and in particular how to handle this scenario please contact me off-line!


Comment by: Tony Matyas (23-8-2016 16:23:25)

the All VBA Treeview shows an icon part on the left and an editable node caption part on the right.

As I use XML files as data base and want to display also the corresponding XML node tags, I would like to enlarge the treeview by inserting a tag label, preferrably displayed between these two parts.

/Icon Part/     | /additional Tag Label with fixed length showing the XML tag/ | /Node Caption/

It would be of great help to receive some code assistance how to integrate this new Tag Label control into your clsNode and clsTreeView classes.

The easier task would be to shift the clsNode controls and related EditBox to the right, but how to provide for a consistant representation of this new Tag Label and ist display?

Gratefully yours


Comment by: Peter Thornton (24-8-2016 11:45:26)

Hi Tony,

That's certainly possible: [icon] / [checkbox] / [tag-label] / node-caption. If applicable tag-labels could be included individually only for nodes only which have a tag caption.

It would be too much to give full details here but to point you in the right direction look at how the respective node controls are added, and adapt for your tag labels. Although it's relatively straightforward there are some small details to include, if this is for a professional project it might be cost effective if we implement it for you.


Comment by: Scott Cordwell (2-10-2016 23:36:56)

Great work guys. I've been using the tree view in Access 2007 using the runtime for a while now. I just installed onto a Windows 10 PC that has Access 2013 retail on it as well. I'm getting a "System Error &H8007007E (-21470224770)" when opening the form with the treeview on it in the runtime environment. Then vba generates a "2926 - Because of your security settings and current security policy, this control has been disabled...." error. The front end works fine if opened using Access 2013 Retail? I've checked trusted locations etc, with no result. Any tips would be appreciated. I'll be getting the commercial version when released.


Comment by: Jan Karel Pieterse (3-10-2016 07:04:31)

Hi Scott,

Are you saying you have two Access versions installed side by side? In my experience this often leads to problems with the references. I'd advise to try whether it works on a single-install of Access 2013.


Comment by: Scott Cordwell (3-10-2016 07:37:01)

Hi Jan,
Yes, I use Sagekey Software's Access Runtime install builder to install the front end application and runtime on all PCs regardless of whether they have Access Retail on them or not. Works really well (for years) and has never adversely affected any existing Access installs. I suspect it's either a permissions issue or an FM20.dll version problem on this particular PC. I'll have to do some more digging.


Comment by: Jan Karel Pieterse (3-10-2016 16:45:20)

Hi Scott,

OK, please let us know if you keep having problems or if you find the root cause.


Comment by: Kieran Shirey (12-10-2016 02:16:04)

My license has expired. Is it possible to move from Access 2013 to Office 365 with a database that has a Treeview Control in it?


Comment by: Jan Karel Pieterse (12-10-2016 06:53:49)

Hi Kieran,

you should be able to provided you install the 32 bit version of Office 365 (default). To be future-proof, perhaps now is the time to bite the bullet and implement our treeview ?


Comment by: Bill Cawley (17-10-2016 11:40:31)


I have programmed - on and off - for over 30 years, yet I still cannot find how to install a Treeview addon to my Excel, even when following the rules conscientiously. Nor do Microsoft provide any examples that I could emulate.

Your example download works fine. You have saved me a great deal of time.


Comment by: Gestec Video (2-11-2016 18:55:31)

I'm using your treeview control with Access 2016, but it doesn't work properly.
The problem is that the captión of some nodes are hiden when I expand or collapse a node, but when I click on the caption of the hidden node, it appear.

The icon to expand and collapse doesn't respond when I click over it.
I have to chose de option 'expander icon'.

The problem also happen with your demo.


Comment by: Jan Karel Pieterse (2-11-2016 19:41:10)

Hi Gestec,

This is a known (temporary) problem with Office 365. Microsoft is working on a fix which should be published to the people which are in the fast track update schedule within the next couple of days.


Comment by: Bruno MARCHAL (6-11-2016 06:35:31)


First of All Thanks for all the Job Done !

It seems it doesn't work with an accdb file
I've tried to include the reference of Form 2 which is located in another folder but it can't compile the code due to problem of Uftreeview definition that is defined in user32.dll I assume.

Any tips?

I intend to Donate cause i am also interested in Drag & Drop Features. How does it work?



Comment by: Peter Thornton (7-11-2016 13:14:22)

Hi Bruno, thanks for your comments!

I'm not sure why it's not working as an accdb for you. I can simply convert the demo mdb file to an accdb with SaveAs. Or, create a new accdb and drag the modules from the mdb. To add the Userform see the CheckUF function. Also get the v26 class modules in the Excel download if you haven't already. If stuck contact me off-line and I'll return an accdb (my address is in the headers).

We've only implemented Drag & Drop in the 'pro' version (main feature is performance with much larger treeviews). This is currently available as beta in Excel & Word, and soon for Access.


Comment by: Kerry (9-11-2016 04:14:17)

First I wanted to say thanks a lot for creating the treeview control. It is a very useful functionality, and I wish MS had the control control available by default for 64 bit. I was wondering if you have code that can be used to populate the treeview control you created recursively. Thanks again.


Comment by: Peter Thornton (9-11-2016 11:43:13)

Hi Kerry, thank you for your comments!

Without knowing how your source data is arranged and the hierarchical relationships are defined, it’s difficult to suggest a generic recursive approach to populate the treeview.

If you can’t find an example to adapt maybe we can sort out something privately for you.


Comment by: Rob Sloan (17-11-2016 02:04:04)

This looks truly amazing. Hoping to likewise tackle the recursive issue to populate the tree view. Have a somewhat simple table: PK_Id, Name, Parent_Id (with Self Join to PK_Id) which can go X number of levels deep. I will take a look for some examples. But if you have any ideas, that would be great!


Comment by: Peter Thornton (17-11-2016 14:57:04)

Hi Rob,
Even with your description afraid I can't visualise how your data is organised, or even if you actually need a recursive function.

If you're new to recursive functions perhaps the key thing to consider is when variables should be be passed ByRef or ByVal. Typically with hierarchical structures the 'Level' counter should be ByVal as it increments, so it can reset after a given branch has completed.

There's a recursive function in the demo for reading the treeview. If stuck you can send me an example of what you've got off-line and I'll take a look.


Comment by: Rob Sloan (22-11-2016 16:53:04)

I was able to use a recursive CTE in SQL (the backend for this database) and populate the treeview. The last couple things I am stuck on are 1) creating new properties for the clsNode that would only show the checkbox at the root node and 2) if possible, making certain root nodes read only (cannot unselect check box after marked as checked on a "for each cnode in mctree.nodes"). This is going to work wonders!


Comment by: Peter Thornton (30-11-2016 13:17:50)

Hi Rob
#1 Adapting to show checkboxes only for certain nodes is quite difficult but not impossible.

#2 Simplest way to prevent un-ticking a checkbox would be something like this, in the NodeCheck event on the form

Private Sub mcTree_NodeCheck(cNode As clsNode)
    With cNode
        If .Checked Then
            .Tag = "Checked"
        ElseIf .Tag = "Checked" Then
            .Checked = True ' explain to the user?
        End If
    End With
End Sub


Comment by: Damon (12-12-2016 22:23:21)

I've just started implementing this treeview and all I can say is "wow! You have saved my life!". I have three questions/comments:

1) I read in the comments above that for use in Access we should be able to copy the 026 modules into our project while using the startup, subform and userform from 025. I'm just starting my implementation and I'm wondering if anyone has successfully done this or whether I'm setting myself up for more headaches than simply using 025 as-is.

2) I want to use this treeview to display a folder/file structure and I'm wondering what I should use for the keys. I'm thinking about using the actual file's full path as the key so that they remain unique. Alternatively I could simply use the filename as the key and then when I want to access the file, use the FilePath method to build the paths as needed. Am I making a big mistake with either approach? I'm talking about a folder/file structure that is not very complex, maybe 3 or 4 levels deep and a total of a couple of dozen files, so performance isn't mission-critical.

3) I'm thinking also of implementing a "FullCaptionPath" method that takes an optional separator argument so that I can easily build a user-readable "Path of Captions" from a given node. Perhaps an optional separator can be used for the regular FullPath option so that we can extract the path in whatever format we want?

Public Function FullCaptionPath(Optional separator as String = " ") As String
' PT, get all the grand/parent Captions
' assumes use of caption

    Dim s As String
    Dim cNode As clsNode

    On Error GoTo errDone
    s = Me.Caption
    Set cNode = Me

    While Err.Number = 0
        Set cNode = cNode.ParentNode
        s = cNode.Caption & separator & s

    FullPath = s
End Function

Thanks again so much!


Comment by: Jan Karel Pieterse (13-12-2016 06:50:13)

Hi Damon,

The best thing to do is open the example Access file and look at the "manual" which can be reached from the "How do I...?" button on the startup form (or open the frmHelp form).


Comment by: Jan Karel Pieterse (13-12-2016 06:53:15)

Hi Damon,

As to your other questions:

2. I guess it doesn't make much of a difference. Though using just the filename risks duplicates.

3. This is precisely why our treeview is so flexible: you can add any functionality you like :-)


Comment by: (19-12-2016 16:39:27)

Sorry - the "Contact Us" link isn't working 'cause I don't have an email client registered at this location. And I thought there may be value in sharing this.

I'm interested in the beta tests - but.

I'm an Access developer and rarely work with Excel.
Is the listgrid control something that would be usefull to a Access only person like myself?

As for the treeview - friggin brilliant so far.



Comment by: Jan Karel Pieterse (19-12-2016 16:55:31)

Hi BC,

I'll add you to the beta program for sure. Just send an email to i n f o AT j k p - a d s . c o m.
I have no idea whether the listgrid would be useful to you. I would guess Access forms would do the trick nicely for displaying records.


Comment by: Peter Nguyen (5-1-2017 20:03:41)

Hi Rob,
Would you post your code that populate the treeview from SQL backend?



Comment by: Christian (6-1-2017 23:09:44)

Hello Jan,

I have implemented your treeview successfully in my access database. After adding a header and footer to my form it doesn't work any more. Run Error '-2147221494(8004000a)':
Refresh: ' TreeControl' frame is not referenced
After deleting the header and footer the treeview works again. What should I do?



Comment by: Peter Thornton (7-1-2017 12:50:58)

Hi Christian,
I can't reproduce your error after adding a header or footer, not sure why it fails for you. What should happen is this:

In the subform's load event an MSForms Frame is created and a reference retained mfrTreeControl. When the Treeview is created the next line should pass this reference to the treeview, in the demo/subform see Property pTreeview.

I suspect what's happening for you is some action is causing your mfrTreeControl reference to lose scope, after the subForms' load event and before this line:
Set mTreeview.TreeControl = mfrTreeControl

Anything that causes your project to recompile while the form is running would result in variables losing scope.


Comment by: Christian (9-1-2017 13:38:47)

Hi Jan,

I have solved the problem with the header and footer. Now I try to adjust the icons for my application. At first I select the image on the frame frImage. Then I chose properties. Under picture I try to change the picture, but it doesn't work. All other fields, like Name, can be edit.
Is this the wrong way to adjust the images/icons?


Comment by: Peter Thornton (9-1-2017 13:56:09)

Hi Christian,
For some reason it can be difficult or impossible to apply the picture to the MSForms image controls in Access. The workaround is create your images in a temporary userform in Excel or Word, and copy them into the Frame in Access. Be sure to name image controls as required and with unique names.


Comment by: Julian Rodriguez (13-1-2017 21:49:50)

Hi Peter:
First of al allow to join to all the congratulations you've recieved so far for this brilliant piece of work. It is elegant, functional and I am sure that for many of us a relief after years of fighting with the problems of office and windowa versions compatibiliy of their treeview included ui the Microsoft web commons dll (mcotl).
I have implemenyed your treeview in my appplications and so far goes so nicely. However, I noticed that when the nodes caption is wider than the subform treeview let say when expanding the tree, the lef-roght scrll bat shows up which is correct but, when I collapse back the tree and the caption of the nodes are within the limits of the subform, the scroll bar does not dissapear and stays on. Curiously this does not happen witn the up and down scroll bars. Is this behaviour normal? Is it a bug?
Than you very much for your support and of course your code and for allowing us to use it


Comment by: Peter Thornton (14-1-2017 14:30:23)

Hi Julian, thanks for your comments

It’s not a bug but I agree not ideal. For the vertical scrollbar it’s relatively simple to size the scrollheight according to number of visible nodes, or turn on/off if the total height of visible nodes is more or less than the height of the treeview.

There’s much more to consider with the horizontal scrollbar. As written it displays and sizes to fit the widest node displayed in the lifetime of the treeview. That might be as a result of expanding nodes, or scrolling down and finding a wider node, or editing a node.

In the scenario you described you might reasonably expect the scrollbar to adjust according to the node expanded in front of you, but what about wider nodes further down tree. Also, the widest node might not be in the highest expanded level, a level-1 node with a long caption might be wider than a level-3 node with a short caption. The scrollbar could be adjusted in real time as the tree is scrolled vertically to fit the current view, but would give an unsightly appearance while scrolling vertically.

The best solution might be to size the scrollbar to the widest node in the current overall expanded configuration of the treeview, including out of site nodes. Indeed that’s possible, but more code than you might imagine and in a large treeview might compromise performance.


Comment by: Julián Rodríguez (15-1-2017 19:52:01)

Hi Peter and Christian:

1. Peter I understand what you explained and I think we can live with it. But what felt strange to me was that the horizontal scroll bars stay in a totally colapsed tree with all the nodes within the treview control in number and size. Anyway, as I told you not big deal and I will perfectly will live with it. It is a wonderful control that you have gifted to all of us.

2. Christian: I think, if I have not missunderstood you, that I have run with the same problema many times. The way I get around it is as follows: A. I get into de editor VBA tool window, look for the forms in the left side tree, and remove the ufTreeview object without exporting it. B. I close the aplication and open it again pressing the shift key so it goes to the Access window without executing macros and without openning forms. C. Then I run the startup macro which ask me if I want to create a new userform required for the correct functioning of the treeview. I say yes and it creates it. D. Then I close again the application and I am asked if I want to save the changes in the UF ufTreeview. I say yes and get out. E. I open normally the application and when open the demo form I close it, open the subform subTreeview which shows me the frame control with the my image controls and then it lets mi to change the image again by looking for it whenever I have the iamge I want to link to the image control.
I do not know why it does this acces. It happened to me whenever I got a crush in my application and it is like something gets corrupted in the userform or so. I am not an expert in programming and I don't understand very well this behaviour. The important thing is that I can get around it! :)


Comment by: Christian M (31-1-2017 20:07:30)


Well done, what a wonderful control. In the process of updating the code to add data columns to the right of the tree I noticed what I think is a typo. Shouldn't msngTopLabel be .Width instead?

'clsTreeView - Build 0.26
Private Sub BuildRoot(bInit As Boolean)
With cRoot.Control
If msngTopLabel < mcFullWidth Then
    .Width = mcFullWidth
    .AutoSize = False
End If

Also, would you like me to post my updates when I'm done?


Comment by: Peter Thornton (1-2-2017 01:29:07)

Hi Christian M,
Thank you for your comments!

Typo is a nice way of putting it but looking afresh not sure what the 'If msngTopLabel < mcFullWidth' was supposed to accomplish.

If the treeview is not resizable during runtime, could change the Const mcFullWidth = 600 to a module level variable and assign its value as the treeview width (TreeControl) if the FullWidth option is set.

It depends what you mean by "updates" but you're welcome to send them to me off-line. Others have also added columns.


Comment by: James (1-2-2017 11:51:01)

Is there any way to increase the Treeview line thickness?


Comment by: Jan Karel Pieterse (1-2-2017 13:37:21)

Hi James,

You could play with the width and height of the label controls we've used for the lines perhaps. Those are controlled by this line in clsTree:

                    With cRoot.HLine
                        .Top = msngTopHV + mlVisCount * msngNodeHeight
                        .Left = mcLineLeft
                        .Caption = ""
                        .BorderStyle = fmBorderStyleSingle
                        .BorderColor = LineColor    'vbScrollBars
                        .Width = msngIndent
                        .Height = mcPtPxl * [yourscalefactorgoeshere]
                    With moRootHolder.VLine
                        .ZOrder 1
                        .Width = mcPtPxl * [yourscalefactorgoeshere]

. Note that making this wider will cause the lines to becone rectangles so you might want to change the label backcolor property too.


Comment by: Scott Braley (2-2-2017 06:11:49)

I just want to say that this tool is amazing. I do have one concern though before I try implementing this in my project. I've always understood that MS Access limits you to a certain number of controls that can be added/deleted over the lifetime of a form. Is that not the case? Thanks for your time.


Comment by: Jan Karel Pieterse (2-2-2017 10:50:33)

Hi Scott,

So far we haven't had any complaints in that direction so I think we can safely say it works just fine.

Should you run into a problem anyway, we do have a pro version which uses less controls to display the tree. You can register for the beta program to try it.


Comment by: Marcos Ximenes Carvalho (2-2-2017 22:23:31)

Mr. Pieterse

I live in Brazil.
What i need to do to change the icons?

Thank you, very much


Comment by: Jan Karel Pieterse (3-2-2017 11:51:58)

Hi Marcos,

You mean how you should add your own icons or how to use different ones of the icons we already provided?

To use different icons, simply remove the ones we provided from the icon frame and insert new image controls into the frame and make sure their content is the way you like it. You can also of course replace the image sin the existing image controls. Make sure you name the picture controls properly as that is what the class uses to select the right icon.


Comment by: Marcos Ximenes Carvalho (5-2-2017 19:39:45)

Dear Mr
Thank you very much for the material sent, but I could not put my icons in trieeview.
Can you send an example?



Comment by: Peter Thornton (6-2-2017 15:40:50)

Hi Marcos,
If you are using Access it can be difficult to add or edit the image controls. One way is to add and edit the image controls in Excel (on a userform), apply the 'picture' and simply copy them into the Frame in Access. You can copy/paste multiple image controls in one go.

Also see this comment above
Julián Rodríguez (1/15/2017 7:52:01 PM)
I haven't tried Julián's suggestion but it looks interesting.

In the 'pro' (currently beta) images can be stored as 'blobs' in a table.


Comment by: Arran Spencer (7-2-2017 19:47:30)

Hi MVPs,

You mention that it is important to make sure that all instances of the classes are destroyed. I would like to use the classcounts code you have included in the demo to check that all instances are in fact destroyed in my implementation (I am using the queryclose event as per the demo). So I have some questions...

How do I set debugmode to 1 to use classcounts?
What happens if all instances are not destroyed?

PS. The tree view tool is amazing, far better than I expected, and easy to implement (and although some of the code is hard to understand for a learner like me, I have started to improve my own VBA skills by studying your code so thanks for that too!)



Comment by: Jan Karel Pieterse (8-2-2017 10:56:23)

Hi Arran,

You can enable the class termination checks by adding a conditional compile argument to the project properties (tools, VBA project properties, add DebugMode = 1 to the last textbox of the dialog)


Comment by: Alexandre (7-3-2017 11:46:48)


    I am using your ClsTreeView and ClsNode classes to have a treeview on my form. However, when I close the form and it gets unloaded, I get an "automation error" or "Object required error". I saw that some sheet objects, which are normally not empty, get emptied. If I remove the line calling this form, my code works. Have you ever seen this issue? Could you please help?
    I can send you screenshots via email.




Comment by: Jan Karel Pieterse (8-3-2017 09:50:46)

Hi Alexandre,

Sure, send your screenshots (perhaps your file as well, so we can trouble-shoot?).


Comment by: Kaman (17-3-2017 16:12:04)

Hey guys, I am having a problem trying to get the mcTree_KeyDown, mcTree_Click events to fire, am I doing something glaringly obvious wrong? It's working in your frmDemo in the same database.



Comment by: Peter Thornton (19-3-2017 13:27:32)

Hi Kaman,
Did you declare a withevents reference to the treeview, eg

Private WithEvents mcTree As clsTreeview

Select mcTree in the top middle dropdown and event stubs in the top right dropdown


Comment by: Daniel Dumitru (27-3-2017 15:30:54)

What you guys created is pure genius. I thought I knew VBA, but I'm barely keeping up with understanding how to use the code you created. I can't even begin to fathom how much work you put into this! Congrats!
Do you think you can help me with something? I'm trying to create a file manager, and I got almost everything working, except the trigger
Private Sub mcTree_Click(cNode As clsNode)

Clicking or opening a node does not seem to do anything. Using another trigger like click on the frame does bring me the selected value from the treeview
Set cNode = mcTree.ActiveNode


Comment by: Jan Karel Pieterse (27-3-2017 15:45:36)

Hi Daniel,

I assume you have this in the declaration section of the userform's code module:

Private WithEvents mcTree As clsTreeView

And this somewhere where the form is initialised:

Set mcTree = New clsTreeView


Comment by: Daniel Dumitru (27-3-2017 16:35:26)

Hi Jan,

Yes I do. I have the
Private WithEvents mcTree As clsTreeView
, in a form, and in a different module I have a public function:
Function addRoot(frmI As Frame, targetFolder As String)

Set mcTree = New clsTreeView
With mcTree
        'SET TREE = FRAME
        Set .TreeControl = frmI

Is it a problem that the function where the clsTreeView is not in the form?


Comment by: Daniel Dumitru (27-3-2017 16:40:02)

It does have to be defined in the Form, now it's working.
Thank you!
Set mcTree = New clsTreeView
- should be defined in the form, not in a separate module.


Comment by: Jan Karel Pieterse (27-3-2017 16:50:54)

Hi Daniel,

Indeed in the form, as that is where form code belongs :-)


Comment by: Filia (15-6-2017 15:34:32)

Hi, I cannot import Form_subTreeView into my project (Access 2016). Message: The module name 'Form_subTreeView' is invalid". Any idea why?


Comment by: Jan Karel Pieterse (15-6-2017 15:57:05)

Hi Filia,

I'm afraid I don't know!


Comment by: Martin (16-6-2017 02:58:20)

You mention:

Access 2003
Access 2010 (32 bit)
Access 2010 (64 bit)

What about 2007, 2013 & 2016?


Comment by: Jan Karel Pieterse (16-6-2017 15:03:02)

Hi Martin,

It should work on those versions as well, the ones mentioned are the ones we tested on.


Comment by: Peter Thornton (22-6-2017 09:38:07)

@ Filia,
I've just tried the following with Access 2016 64 bit:
- Open a newly downloaded treeview demo v025, all worked fine.
- Drag the subForm from the treeview demo to a new project, no problem.

@ Martin,
>What about 2007, 2013 & 2016?
I can confirm working in Access 2007, 2013-32, and 2016-64.
I believe others have tested in 2013-64 and 2016-32.

The current version 025 was made with 2003. We held back from updating to 026 as we can no longer compile in a way that will work for 2003. However as time has gone by we may update the 025 mdb to 026 as an accdb.


Comment by: Filia (29-6-2017 16:08:00)

Well, I got it to work through trial and error in Access 2016. The only problem remaining is that when text in the control extends under the vertical scrollbar and I drag the horizontal scrollbar to the right to view it, the vertical scrollbar doesn't refresh so I get 2 vertical scrollbars (original position on load and new position resulting from dragging).
When I populate the control, my last two commands are Refresh and then ExpandToLevel(x). Any help to solve this will be appreciated.


Comment by: Peter Thornton (1-7-2017 15:54:01)

Hi Filia,
I don't know how two vertical scrollbars can be displayed at the same time because normally only one can exist.
However moving the horizontal scrollbar will not do anything to help show nodes 'below' the vertical scrollbar.

If you want you can send me what you have off-line and I'll take a look.


Comment by: Julian (6-8-2017 13:10:54)

Hi, there. I have implemented your wonderful treeview in my aplication and so far I was running it on Windows 7 or higher + Officce 2007. However I understood that your treview was also ready for access 2003. Well as some users of my aplication still run it on XP and access 2003 I downgraded my aplication to a version for them and I have run into an strange problem. Whenever at the begining when it has to pass the trusted property it fails to do so and tells me it has not found that object (?¿) and it does not create the uftreeview userform. I really do not understand very much about this. I tied tou use your demo compiled by the way in access 2003 and it just crash ass soon as you enter in the mdb file.
Do you think there is something I am doing wrong? It is not compatible?



Comment by: Peter Thornton (7-8-2017 10:34:07)

Hi Julian,

I have similar problems as you describe with Access 2003. Not sure but possibly because my 2003 is now in Windows 10 with multiple later Office versions. These all 'share' the VBA run-times, these have been updated with various security changes which maybe cause problems in the now unsupported 2003.

If your Access 2003 is in a similar setup could you ask one of your XP/2003 users to download a fresh copy of the Access 025 demo and try it.


Comment by: Julian (18-8-2017 19:16:15)

Hi Peter, thanks for the suggestion. I will do that and see what happens.

Thanks so much


Comment by: TungSon Nguyen (20-8-2017 16:41:39)

I am trying to use your treeview control for my project.
there are two point I would like your help to make me clear:
1. I could not get the tooltip option, especially when my node text is longer then the width of the treeview-container frame.
2. Is this possible to get the FullPath of a node with the node caption instead of the node key?

I am looking forward to receiving your reply.
Thank you in advance and Best regards.


Comment by: Jan Karel Pieterse (21-8-2017 08:21:05)

Hi TungSon,

In class clsNode, find the routine called AddChild.
Search for: ".Caption = vCaption"
Below that line, add:
        .ControlTipText = vCaption


Comment by: TungSon Nguyen (22-8-2017 13:03:11)

I am following your instruction but it does not work and I got an error message: "Method or data member not found". By the way, what i found is ".Caption = sCaption" instead of ".Caption = vCaption"
What should i do now?
Thanks and regards.


Comment by: Jan Karel Pieterse (22-8-2017 16:10:01)

Hi TungSon,

If you email your file I'll have a look.


Comment by: TungSon Nguyen (23-8-2017 07:36:22)

I got the clue: the one i am using is Ver 015. i down loaded the latest one from your page, V026, then followed your instruction and it working now miracly.
i will send you my file when i finish coding.
Thank you for your sharing and instruction.
Best regards.


Comment by: Jan Karel Pieterse (23-8-2017 09:56:51)

Hi TungSon,

Great, glad you got it solved.


Comment by: Dean Arnold (26-8-2017 15:20:09)

In the clsNode code for mctlControl_Click() it states that "NodeClick will raise the click event to the form". How do you take advantage of this? What I am trying to do is to jump to a record when it is selected on the tree (I have subTreeView and a subRecordView on a form).

I thought it would fire the On Click event of the parent form...I could then derive the selected record number from the selected node's sKey and jump to it.


Comment by: Peter Thornton (26-8-2017 18:37:09)

Hi Dean,

In your form or even a class modules declare a WithEVents reference to the clsTreeview, eg

Private WithEvents mcTree As clsTreeview

Select mcTree in the middle dropdown and in the right select 'Click' This will add the Click even stub to your form code. When you clickk on a node with the mouse or navigate with keyboard arrows the Click event should fire. cNode will refer to the clicked node. For your objective I guess your first line will include -



Comment by: Dean Arnold (27-8-2017 04:14:36)

Thanks...makes sense and works great. Two more questions, if you don't mind.

I assume if I want the dblClick event to do something similar (e.g., open the selected record for editing when the node is double clicked) I would need to first modify the mctlControl_dblClick() routine to behave similar to the mctlControl_Click() routine.

Secondly, is there a function that collapses the tree to a certain level? When I initially load the tree I would not wish to show four levels of detail.


Comment by: Peter Thornton (27-8-2017 11:51:28)

The dblClick event is already trapped in the node class to start Edit-mode (if enabled). Follow from where Click is trapped in the Node class to 'Event Click' and replicate with the dblClick, but best not with EnableLabelEdit

The default .Expanded property for nodes is True, so change any to False as required while populating but before calling Refresh.

Alternatively, yes there is a method to expand the tree to a specified level, look at .ExpandToLevel in the documentation (in the Excel demo)

mcTree.ExpandToLevel lExpansionLevel:=1, bReActivate:=False ' don't activate before calling refresh


Comment by: Wolfgang Backes (30-8-2017 10:20:44)

Congratulation for this very neat and admirable piece of software.
After adding a root node without caption I lateron got error 13 (type mismatch) in Sub BuildRoot. The error is caused by the code line
                    .Caption = cRoot.Caption
due to the fact, that cRoot.Caption is not available here.
Of course, there are plenty of trivial ways to circumvent the error, for example
1) Without changing your source code: the client program could be required to supply a value for paramter 'vCaption'in method 'AddRoot' of class clsRoot. But in my opinion an optional parameter should never be compulsary.
2) I think a caption is always supposed to be of type String. Therefore, adding an appropriate default value of "" for the optional parameter vCaption solves the problem.
3) You could change the culprit line by e.g.
.Caption = IIf(IsMissing(vCaption), "", vCaption)


Comment by: Peter Thornton (30-8-2017 14:17:46)

Hi Wolfgang,
I think you're the first person in 4 years to stumble on that! It didn't occur to us anyone would include an empty caption;)

The Caption as displayed of course is always a string, but as a property might want it retained as a string or numeric, or something else, so I think best to keep it as a Variant. Your suggestion 3) is probably the best way to handle it for all scenarios, or almost the same

If Not IsMissing(cRoot.Caption) Then .Caption = cRoot.Caption


Comment by: Julian (1-10-2017 16:29:29)

Hi Jan:

I use your treeview in acces based application using the last version available (BUIL 025). It works really great except for one issue. I would like to be able to scroll the treeview with the mouse wheel (I am on Access) but it does not work in this version.

Reading past comments on your web I saw that this problem had already been issued by Thomas Koester back in 2015. You suggested him to adapt a piece of code to have it working and apparently (he was an expert programmer) he succeded in doing that. I followed that link and tried to adapt that code to my application but beeing honest I am far from having enough knowlege to do that and I failed several times.

Is there any way you could help me with that. Is there other alternatives (2 years have past and maybe there is something new)



Comment by: Jan Karel Pieterse (2-10-2017 07:58:51)

Hi Julian,

Wheel scroll is implemented in our (paid) pro versio of the treeview. If you are interested, send us an email (see address near bottom of page).


Comment by: TungSon Nguyen (10-10-2017 09:13:21)

I am using your treeview with my app.
And now, I am asking for your support on my problem:
On the treeview already created, can I add a Parent node for a Root node? If yes, would you please show me the way?

Thank you and best regards.


Comment by: Jan Karel Pieterse (10-10-2017 13:25:25)

Hi TungSon,

That would probably take removing the rootnode, adding a new rootnode and then adding your removed rootnode to the new root.


Comment by: Gustavo Ruiz (20-10-2017 05:46:25)

Great job! Had a few problems a year back with several windows updates that broke MSCOMCTL.OCX and forced me to do several "tricks" to get it work with older OCX version under Win7 64bit, treeview under MS Access let me show the proper office app main menu based on user permissions (activating nodes accordingly).

This week I had to migrate to Office 2016 just to found out that the installed version was 64bit, although there was no need for it but some IT guys see 64 bit on the OS and think that Office should be 64 bit aswell even if this mean losing functionality in several Excel files based on 32bit addins/VBA code :)

Anyway, older treeview has gone and now its replaced by your excellent MSForms Treeview. I will donate to you as soon as I finish this message.

If anyone is doing this on Access be prepared to add several PtrSafe keywords on your code and convert Long to LongPrt on some of the parameters related to :)



Comment by: Jan Karel Pieterse (20-10-2017 14:48:16)

Hi Gustavo,

You're welcome!

And BTW, you can find the API stuff here too:


Comment by: AYAMNASH (13-11-2017 14:16:39)

I have been trying to get my data table

but i could not because i'm beginner in vba code
i have one table (table1) for g/l accounts
field one (fatheraccno)type number
field two (fatheracc)type text
field three (subaccno)type number
field two (subacc)type text
how i can get my table to your subform in your project ?
could you write the code?
thank in advance



Comment by: Jan Karel Pieterse (14-11-2017 09:58:49)


What does your data look like?


Comment by: ayamnash (19-11-2017 19:52:51)

realy i don't understand what does you mean
any way ...
i will ask my question in another way
i have accounting data base it is working perfectly
but it has not chart of account (treeview )
The problem was existence two tables
the first table for( accounts category ) the second table for (account names)
Many experts told me it is not good to build chart of accounts with two tables
Because in case of deletion, modification or addition it will be difficult to modify the two tables
So I want to create a new database for new project based on one table .
I expect the table to have four main fields
field one (fatheraccno)type number
field two (fatheracc)type text
field three (subaccno)type number
field four (subacc)type text
subaccno subacc fatheraccno fatheracc
1 Assets
11 fixd assets 1          Assets
1101 cars          11          fixd assets
1102 buildings 11          fixd assets
11021 building1         1102         buildings
11022 building2         1102         buildings
1103 lands          11          fixd assets
12 current assets                              1201 cash          12          current assets
1202 bank          12          current assets
1203 debitors 12          current assets

how i can drop this table to your subform?


Comment by: Jan Karel Pieterse (19-11-2017 20:31:09)

Hi ayamnash,

The demo treeview contains code that demonstrates how to add nodes and sub nodes. It should not be very hard to figure out how to add the information in your table to the tree.


Comment by: Mathias Dumke (20-11-2017 10:32:20)

very good job.

My Question is, how i can scroll in the treeview.

Mathias Dumke


Comment by: Peter Thornton (21-11-2017 18:40:08)

It's difficult to make wheel scroll reliable in the 'free' version. If you really need it scroll is included in the 'pro' version, see just below the top of this page.


Comment by: Ivan Petrik (26-11-2017 11:51:09)

Dear JKP,

I would like to ask you how to add new image to the form frame. I tried to load new image to the frame but property Picture remains "(None)". Could I ask you for your help? Many thanks.

Best regards

Ivan Petrik


Comment by: Jan Karel Pieterse (26-11-2017 16:31:03)

Hi Iwan,


- You insert a new image control
- Name that control the same as how you want to refer to the picture in code
- Copy the image you want in the control
- Select the control and click in it's Image property
- hit control+v


Comment by: Ivan Petrik (27-11-2017 21:42:33)

Thank you for response. Now it's finaly working. The problem was in the way how to edit the frame. If I open frmDemo in design mode, click on the SubTreeView and try to add some new image onto the frame, the properties are read only. But If I open the SubTreeView directly, the frame is available to edit.

Great job Jan.


Comment by: edd (4-12-2017 17:36:45)

Hi, my data will end up creating a ragged structure with some levels as deep as 27. Will this treeview work with the recursive loading used in the Microsoft article as below;

Whilst I was able to use the above, I am not sure I know enough to implement this without someone pointing me in the right direction...

Ps this is great stuff!!! :)


Comment by: Peter Thornton (5-12-2017 10:17:47)

Hi edd,

There's no defined limit for the number of Levels though for usability 27 seems a lot.

That recursive example would need slightly adapting but it should work, at a glance you'd need to change:

objTree As TreeView > WithEvents objTree As clsTreeview ' at module level
nodCurrent As Node > nodCurrent As clsNode
objTree.Node.Add > objTree.NodeAdd
tvwChild > tvChild

You'd first need to create the Treeview and write any properties you want included, and not need the Set objTree = Me!xTree.Object line.

Refer to the documentation in the Excel download and update the Access v025 to 026

If you've got a large tree, particularly with those 27 levels, you might find our 'pro' version more suitable.


Comment by: Elen Glimois (5-12-2017 22:10:16)


What a great work I found on your web site !!!

I am wondering if your treeview support multiple columns. If it does, how does it work? I would like to implent it on MS Access 2013.

Thanks a lot


Comment by: Jan Karel Pieterse (6-12-2017 14:13:23)

Hi Elen,

Our treeview does not support columns. But there is a listgrid control that we sell which has capabilities that might help?


Comment by: Volker Lorenz (30-1-2018 15:50:34)

Hi Jan, I modified your frm_demo and after a short time it worked great! Than I copied clsNode, clsTreeview, modStartup, modDemo and the form into my Access application and at the line: Set mcTree = Me.subTreeView.Form.pTreeview the error occured: Error Load Nr:40036 Discr: Die Methode 'Form' für das Objekt '_SubForm' ist fehlgeschlagen DLLError:0 Context: 104436
Can you please give me an advise, what I have done wrong?



Comment by: Jan Karel Pieterse (30-1-2018 17:14:39)

Hi Volker,

In the Access Demo's startup form there is a "How do I?" button which displays important information on how to implement the treeview in Access.


Comment by: shinzi&#12288;asano form japan (18-2-2018 02:25:43)

Nice to meet you
About shown Treeviev
I thank for precious information.

About contents, there might be a better method particularly, but succeeded in hierarchy indication when I did it as follows.
I inform it until a report.

Will you come better than machine translation?

Dim cNode(3) As clsNode '(a): maxLevel
Set cNode(1) = cRoot.AddChild(cRoot.Key & "A", "NodeA")
&#12288;Set cNode(2) = cNode(1).AddChild(cRoot.Key & "_1", "nodeA_1")
Set cNode(3) = cNode(2).AddChild(cRoot.Key & "_1.1", "nodeA_1.1")
     cNode(3).AddChild cRoot.Key & "_1.1.1", "nodeA_1.1.1"
     cNode(3).AddChild cRoot.Key & "_1.1.2", "nodeA_1.1.2"

Set cNode(2) = cNode(1).AddChild(cRoot.Key & "_2", "nodeA_2")
     cNode(2).AddChild cRoot.Key & "_2.1", "nodeA_2.1"

Set cNode(1) = cRoot.AddChild(cRoot.Key & "B", "NodeB")
Set cExtraNode = cNode(1).AddChild(cRoot.Key & "_3", "nodeB_1")
     cExtraNode.AddChild cRoot.Key & "_3.1", "NodeB_1.1"


Comment by: Peter Thornton (18-2-2018 10:43:30)

Hello Shinzi,

One of the objectives of the simple demo is to illustrate it is not necessary to add nodes in sequential order, several approaches are possible. One of the 'pro' Treeview version demos illustrates an approach that works with an array cNode(0-9), one for each level, like your example.

In passing note your declaration cNode(3) is actually cNode(0 to 3).


Comment by: Kerry (13-3-2018 13:03:46)

Thanks for providing the control, it fills a real need that Microsoft should have. I want to have the tree control start collapsed, but when I try to have
.ExpandToLevel 1
in the initialize section, I get an error of 'Object variable or With block variable not set'. Can you provide any suggestions as to what I am doing wrong? Thanks for any information you can provide.


Comment by: Jan Karel Pieterse (13-3-2018 14:11:00)

Hi Kerry,

After adding a node, set its expanded property to the value you need, E.g.:
cRoot.Expanded = False

will show just the rootnode, collapsed.


Comment by: Zoltan (23-3-2018 14:27:46)

Is there any posibility to check if a Node already exists before to add it to the tree? Somtimes I have to update some values of a node and not to add it again.


Comment by: Jan Karel Pieterse (23-3-2018 15:02:41)

Hi Zoltan,

You can traverse through the nodes already there using:

For Each cNode In mcTree.Nodes
    'Check for cNode.Caption or cNode.Key


Comment by: Woo (10-4-2018 13:06:52)


I download "treeview sample Access database" file.
And I insert following code into on Load event of "frmDemo" form like this.

Private Sub Form_Load()

    '=========== Original Code ===========
    AppName = GCSAPPNAME

    '=========== Inserted Code ===========    

    If Not mcTree Is Nothing Then
    End If

End Sub

Problem is..
I can't move between nodes when mainform load event finished.
"Me.subTreeView.SetFocus" code doesn't seem to be working.

I wanna move between nodes when mainform load event finished.

How can I work around this..?

Sorry for my poor english.


Comment by: Peter Thornton (10-4-2018 17:10:15)

Hello Woo,
It's not clear if you say you've removed all the code in the Load event, I hope not!

If I follow you want to show the treeview in the form's load event, and SetFocus to the treeview so you can immediately start navigating between nodes with the arrow keys, or Tab out or F2 Edit, without first clicking the treeview with the mouse(?)

When the Load event fires the subForm's window doesn't yet exist, so SetFocus doesn't work.

In Excel the Activate fires after the form has fully loaded, but not it seems in Access. Try adding a timer, something like this -

Load event
' Me.subTreeView.SetFocus
     Me.TimerInterval = 500 ' milliseconds

Private Sub Form_Timer()
    Me.TimerInterval = 0 ' cancel the timer
End Sub

In the subFrom change Sub moSubControl_Enter from Private to Public

Maybe Access experts can suggest a better way, we are Excel guys!


Comment by: Woo (11-4-2018 02:23:52)

Hello Peter.

I executed your solution. It works well.
Thank you for your help and explanation.

As you said, It seems that there is no form_load_finished event in access.

Additionally, I find another imperfect solution.

--- another workaround ---

1. Main form(form load event)
Insert following code at the end of the original code

2. Main form: Add textbox(with tab index = 0, tab stop = true)

3. in added textbox(got focus event)
Insert following code.
SendKeys "{TAB}"

Have a good day, Peter.


Comment by: Yuan Yao (15-5-2018 20:59:46)

Hi Peter, this is absolutely a masterpiece to me! Thanks for all the work!

A quick question, how do i dump only the data I checked/ticked?


Comment by: Peter Thornton (16-5-2018 10:07:38)

Thank you Yuan Yao, but note of course Jan Karel is also co-author

In the demo form, adapt Sub GetData1 or GetData2 like with something this

    If cParent.Checked = -1 Then
        lCt = lCt + 1
        rng(lCt, lLevel) = cParent.Caption
    End If


Comment by: Simon (26-5-2018 12:30:30)


Been using your treeview for a while now in some PPT projects. Have to say is brilliant and I have even managed to customise it a bit, added drag drop and a small/large option for the icons/text.

I have a new weird issue I can't pin down.

With the macro running if any of the methods that alter the frame's controls collection (add or remove) are triggered then the VBA Toolbox appears on the screen

I found a mention of this relating to something else and their solution was to make sure the VBA Toolbox was on the same screen as the VBA Editor and close it before saving. Tried that and it works sometimes but not others.

The VBA Toolbox doesn't always come back on the same monitor either (I have 3 monitors).

If I then test the created Add-In on a single screen the Toolbox appears on the screen.

Tried using a FindWindow sub to pick up the Toolbox so I could force it closed but not able to find it.

Has anyone had this issue?

It may be something to do with my machine but fresh install of Office (2013) hasn't cleared it.

Any help or clues appreciated!




Comment by: Jan Karel Pieterse (27-5-2018 11:31:30)

Hi Simon,

I would try removing the VBE entry in the registry (export its key first!!). It would be something like:


AFAIK the VBA editor will add it back with its default settings.

The 7.1 might be different for 2013.


Comment by: Simon (29-5-2018 11:14:08)

Hi Jan Karel

Thanks for your quick response and suggestion. Tried that. 7.1 was the highest key avail so I backed it up and deleted it. 2013's VBA reset to defaults and new key was created. So all good there.

Unfortunately it hasn't shaken the problem.

I decided to start from scratch and tried a test VBA add-in with a form with buttons that altered the form's controls collection and added a simple treeview (usaing same version of classes as main project). Still no toolbox.

Going back to my project, I realised I also have a treeview as part of the "open" code as an option - the user sees the source templates "folder structure" as a treeview. That code used AddChild etc., same treeview classes but no toolbox there.

So, the issue is something to do with the main form where users click to add nodes to their "chosen slides" list (treeview) selecting from the "available slides" (treeview).

Still can't imagine quite what is going on to make a toolbox appear though! I appear to have created a problem in VBA.

I will continue to investigate!




Comment by: Simon (29-5-2018 11:44:29)

Hi again

OK, pinned down the issue I think.

Stepping the code I found it wasn't adding the node that causes it but part of the refresh "wrapper" I put around a user choosing a slide. Specifically setting focus onto the Chosen node (frame "fraChosen.SetFocus")

I destroyed the frame and added a new one and the problem seems to have gone away.

I swear last week when I stepped it, the toolbox appeared right on the Controls.Add line so maybe the registry edit has made a difference too.

VBA not doing what it should do regardless!

Thanks for your help



Comment by: Jan Karel Pieterse (29-5-2018 12:53:52)

Hi Simon,

I guess this is just "one of those things" we have regularly when developing in Excel VBA :-)


Comment by: Peter Thornton (31-5-2018 08:49:26)

Hi Simon,

Try deleting (or rename) your toolbox file, for your Powerpoint look for It should rebuild itself but you'll need to re-add any 'additional controls'.


Comment by: Arlyn (10-6-2018 14:21:15)

In playing around with the control for an Access database I noticed that the scrolling feature does not work. Is that something that just needs to be turned on, or does that control simply not work for this?


Comment by: Peter Thornton (11-6-2018 09:27:12)

Hi Arlyn,

Wheel scroll is not implemented in the free version, it's not included in the original MSComctl.ocx version either. If it's important for you might be interested to look at our 'pro' treeview, more details at the top of this page.


Comment by: Sasha (9-7-2018 07:37:05)

I use MS Access 2007, on 32-bit Windows 7 machine.
When trying to use access version of Your treeview, I'm getting a error message :

'Run-time error : 2926
Because of your security settings and current security policy, this control is disabled. To modify your policy and enable the database, use the Message Bar.'

What to do to make it work ?
Thanks in advance.


Comment by: Jan Karel Pieterse (9-7-2018 09:55:29)

Hi Sasha,

WHen you open a database, often you get a yellow bar beneath the ribbon which contains a button to enable content. Click that button.


Comment by: Peter Thornton (9-7-2018 10:19:07)

Hi Sasha,
Just to add, the demo form includes an MSForms.Frame control with a number of image controls as one way to store image objects for icons in the treeview. The demo also includes three MSForms spin controls, these are only to change node height, node width and font size and only to illustrate the treeview.

If your security settings disable controls without notification, in the demo form comment all lines that refer to mfrImages (the frame control) and axSpinButton,1,2&3 (the spinners). You will not be able to run demo2 with icons demo, but there are other ways to store and retrieve icon images. The treeview is not a control and it doesn't need these controls to work.

These MSForms controls are considered as "built-in" in other Office applications such as Excel and Word. Simplest way to enable them in Access is to change your security settings to show a warning prompt with controls.


Comment by: Sasha (9-7-2018 10:42:36)

Thanks for quick response.
All MS ACCESS 2007 settings in Trust Center are adjusted to accommodate
work with macro's and vba code without restrictions.

Code crashes with error 2926 in Form_Load procedure of the frmDemo form:

    ' if the subForm contains a frame of images
    Set mfrImages = Me.subTreeView.Form.Controls("frImages").Object

Any ideas on why this happend?


Comment by: Sasha (9-7-2018 13:28:12)

Hi Peter,
I followed your instructions and commented out all references of the mfrImages instances, and managed to create new form with subTreeView as subform.
I then copied all the code from frmDemo to new form, and did some deleting mainly because I defined TreeView properties directly in the code and not by having the check boxes and combo boxes on the form to select values for the properties from.
Everything is looking good, beside fact that loading of subTreeView subform causes message from Acess stating that my security settings disabled control on this form.
That is if I open my form with subTreeView as subform directly.
If I do it by calling the code from modStartup where I did change form name to my form name, then I do not get the security warrning message.
Maybe I'm still missing something?


Comment by: Peter Thornton (11-7-2018 16:06:52)

Hi Sasha,
If your security settings block all controls I don't think you needed to create an entirely new form, just remove all code references to the MSForms controls (the Frame of image controls on the subForm and spin-buttons on the main form), and delete the controls.

Indeed you'd have got warning trying to open the subForm if you hadn't deleted the Frame, but interesting you could open it with code.

Just to clarify, it's not security's Macro settings but ActiveX settings which if disabled blocks these controls.

In passing, get the Excel download with v026, copy code in the class modules, and debug/compile.


Comment by: Stefano (16-7-2018 09:24:14)

Hi Peter,
thankyou for sharing this great alternative.
I'm trying to convert my ActiveX TreeView into this project, but I'm stuck with the references of my accdb.
All the events of the form "subTreeView" give this error:
"The Expression On Load you entered as the event property setting produced the following error: User-defined type not defined".

I am missing something?
I am on Win7 x64 and running Ms Office 2013 32bit.

This is the list of references of my accdb:
MSComctlLib | C:\windows\SysWOW64\MSCOMCTL.OCX
Office | C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\MSO.DLL
DAO | C:\Program Files (x86)\Common Files\Microsoft Shared\OFFICE15\ACEDAO.DLL
Scripting | C:\Windows\SysWOW64\scrrun.dll
MSForms | C:\windows\SysWOW64\FM20.DLL
SHDocVw | C:\Windows\SysWOW64\ieframe.dll
stdole | C:\Windows\SysWOW64\stdole2.tlb
Access | C:\Program Files\Microsoft Office 15\Root\Office15\MSACC.OLB
VBA | C:\Program Files (x86)\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL



Comment by: Peter Thornton (16-7-2018 15:35:12)

Hi Stefano,

The only additional reference our Treeview requires is to MSForms, and it looks like you've got that.

Have you tried Debug / Compile? Often that will highlight compile errors which would trigger that message, but there might be other things.

If/when you've successfully adapted best to remove the ActiveX control from your form and the MSCOMCTL reference. I suspect the SHDocVw would fail fail in Access 64, not sure if there's an alternative but that's a different matter.


Comment by: Yair (2-8-2018 14:25:34)

Hi guys,
This is amazing - converted it to Access 2016 and it works beautiful out of the box!
Is there a way to change it to display the lines RTL, so it can be used for Hebrew content as well?
Thanks again for the great work!


Comment by: Peter Thornton (3-8-2018 12:38:32)

Hi Yair,

Userforms have a RightToLeft property. I can't apply the setting but maybe you can - in the subForm's Load event

    Set mUF = New ufTreeView
    mUF.RightToLeft = True
    Debug.Print mUF.RightToLeft ' ?

If that sticks for you let me know if it makes a difference, and if it does please send me a screenshot!

Otherwise maybe you can flip the text before applying it to captions, eg

s1 = "ABCDE"
n = Len(s1)
s2 = Space(n)
For i = 1 To n
    Mid$(s2, n - i + 1, 1) = Mid$(s1, i, 1)


Comment by: Yair (3-8-2018 15:56:44)

Hi Peter and thank you for your reply.
I tried applying the RTL property to the mUF (in both places in the load event) and also changed the forms's orientation property to RTL but it didn't help.
The problem is not flipping the text (this is handled well by Windows and Office), but to display the tree lines going from the right side toward the left side.
I did put in the code the confirmation lines and it reported back OK:
Debug.Print mUF.RightToLeft
Debug.Print Me.Orientation


Comment by: Peter Thornton (3-8-2018 17:03:22)

The documentation about exactly what the userform's RightToLeft property actually changes is very vague, except apparently showing vertical scrollbars on the left - does it?

If as you say Windows handles the RTL text in Label captions, perhaps all that's left to do is to flip all the controls to the right side. If controls' Left values are still measured from the left, the mirrored left might be something like (frControl.ScrollWidth - orig-Left). Or if ScrollWidth is zero use InsideWidth. However I've no idea how a horizontal scrollbar would impact things. Also you say (just) the lines, are you saying the Label captions get automatically flipped but not the lines, if so maybe it's only the Lefts of the lines that need recalulating. As I can't test the RTL property I'm guessing in the dark!

Try experimenting in say Excel. On a userform place some Labels on a Frame control, see what it takes to flip them, particularly with a horizontal scrollbar.


Comment by: Jan Mellema (4-8-2018 09:50:37)

Goede morgen uit Leipzig!
ik zoek naar een mogelijkheid bij treeview met mouse-over additionele en verdiepende informaties te geven over de node die zo wordt geraakt.
Is dat mogelijk/hoe kann dat mogelijk worden gemaakt?


Comment by: Jan Karel Pieterse (4-8-2018 20:35:19)

Hoi Jan,

Je zou zelf een eigenschap kunnen toevoegen die de controltiptext vult van de controls van de node.


Comment by: Ian Digby (15-8-2018 11:21:59)

This treeview control is absolutely great. Much faster to render a tree than the 32 bit MS Treeview control. Similar in syntax, so easy to learn if you've used the latter, and very thorough and feature-rich.

I will be making a Paypal donation, and thanks for an awesome effort and for generously making it available.

"The earth is but one country, and mankind its citizens" - Baha'i Writings.


Comment by: Jan Karel Pieterse (16-8-2018 10:42:24)

Hi Ian,



Comment by: Luigi (31-8-2018 21:46:03)

Hi guys,

this is absolutely amazing control!
I am using it with Access 2016 64bit and it works beautifully and it is very fast.

I know this is a trivial issue, but I am struggling with trying to get the treeview border to look flat rather than sunken. I have tried changing the border property to "transparent" and specialeffect property to "flat" everywhere but with no success...any help?

I'd like to make it look like the XER toolkit in the example list of your website.

Thank you


Comment by: Jan Karel Pieterse (3-9-2018 10:23:30)

Hi Luigi,

You change that by changing the appearance of the frame you placed on the userform yourself (the one called frTreeControl in the Excel demo).


Comment by: Hartmut Gruenhagen (12-9-2018 09:22:51)

A suggestion to enhance the FullPath function for some compatibilty with the fullpath property in the ms treeview control:

Public Enum ndPathType
    ndKeys = 1
    ndCaptions = 2
End Enum

Public Function FullPath(Optional ByVal ndType As ndPathType = ndKeys) As String
' PT, get all the grand/parent keys
' assumes use of key

Dim s As String
Dim cNode As clsNode

On Error GoTo errDone
Set cNode = Me

If ndType = ndKeys Then
     s = Me.Key
     While Err.Number = 0
         Set cNode = cNode.parentNode
         s = cNode.Key & "\" & s
     s = Me.Caption
     While Not cNode.parentNode.Caption = "RootHolder"
         Set cNode = cNode.parentNode
         s = cNode.Caption & "\" & s
End If

FullPath = s
End Function


Comment by: Jan Karel Pieterse (12-9-2018 13:48:07)

Hi Hartmut,



Comment by: Louise Dinkelmann (27-9-2018 15:50:48)

Good day.

I have used the control and it works fantastic. However when I install a tested, working copy of my Access database on another laptop I get Access error 2683 There is no object for this control when I try to open the form with the treeview. What could be the reason for this error? Any help will be appreciated.



Comment by: Jan Karel Pieterse (27-9-2018 16:34:05)

Hi Louise,

The error might be due to ActiveX controls disabled in security settings, prompted by the Frame and image controls on the demo form used to replicate an 'ImageList'.

If changing the security setting fixes but normally can't do that, there are other ways to store images as illustrated in the 'pro' demos.


Comment by: Blaine (1-10-2018 14:40:42)

Good day,

I have one query about the enter activity. When the tree is active and after you press enter on the keyboard, all other action is unavailable(nodeclick,rightclick...) How to stop it from not working?

Many thanks


Comment by: Jan Karel Pieterse (1-10-2018 15:11:07)

Hi Blaine,

I am not sure I understand?


Comment by: Blaine (1-10-2018 16:45:02)

Hi Jan,

So when I open the tree and click one node it is all fine. And then I press enter, and the tree seems to collapsed and all the click and right click stop working.

Is there any changes i can make to avoid enter button?


Comment by: Jan Karel Pieterse (1-10-2018 17:02:43)

Hi Blaine,

You can of course make sure enter is not handled by the treeview. You can do so by editing the routine TreeControl_KeyDown in clsTreeView and disabling this bit:
    ' PT toggle expand/collapse with key Enter
    If KeyCode = vbKeyReturn Then
        If ActiveNode.Expanded Then
            KeyCode = vbKeyLeft
            KeyCode = vbKeyRight
        End If
    End If


Comment by: DJISOR (6-10-2018 12:07:20)

hi, great job !

just a little issue with access 2013-windows 7 (64 bits) :
the lines in autoexec macro must be :


and not

[CurrentProject].[Application].[Version]>=12 (error 13 Type Mismatch)
same in form_frmHelp form_load sub :
If Application.Version >= "12" Then and not
If Application.Version >= 12# Then


Comment by: Peter Thornton (8-10-2018 10:37:40)


Normally the 'string number' should coerce without a problem for the comparison with the numerical value. Does the following work for you in the Immediate window:
?"13.0" > 12

If that works what does this return:

If it includes any non numerical characters that would explain why you get the mismatch error, unusual though.

Whatever the reason we can't change it as we can no longer compile an MDB that will work for 2003, which is also why the v.025 in the current Access download was not updated to the slightly later v.026.

Time has moved on so perhaps it's time we update it to an ACCDB for use in 2007 or later, in which case the If >12 app' version checks would not be required and can be removed.


Comment by: DJISOR (8-10-2018 23:59:49)

for me : ? "13.0" > 12 -> type mismatch

curiously, the Luigi comment (8/31/2018 9:46:03 PM) says "I am using it with Access 2016 64bit and it works beautifully ".

thanks guys !


Comment by: Jan Karel Pieterse (9-10-2018 07:09:17)


This is a problem with the implicit conversion from string to number. You obviously are not using the period as the decimal separator and hence VBA fails to convert "13.0" to a valid number. The way around this is to use the Val function around the version string, which takes as many characters as it can to construct a number from the string (if the decimal separator differs, it just takes the whole number):

Val(Aplication.Version) > 12


Comment by: Filipe Jose Roque Caetano (12-10-2018 11:47:30)

Hello from Portugal.
Thank you for this fantastic treeview control.

I've been able to change the way that you populate the treeview from a table i MS Access, although I can only go up to level 6 (after that level of disaggregation the treeview don't display that nodes anymore)

Here it is for anyone that find it useful.

'Populate the treeview with data from a table
Private Function GetDataFromTable()

On Error GoTo Error_Trap

    Dim rst As DAO.Recordset

    Set rst = CurrentDb.OpenRecordset("qryMenu")

    Set mcTree = Me.subTreeView.Form.pTreeview
    With mcTree
        'Add Root nodes
        If rst.RecordCount > 0 Then
            While Not rst.EOF
                If rst.Fields("Parent") = 0 Then
                    Set cNode = .AddRoot(rst.Fields("ID"), rst.Fields("Designation"))
                    cNode.Bold = True
                End If
            'Add Child nodes
            For Each cNode In .Nodes
                While Not rst.EOF
                    If rst.Fields("Parent") > 0 Then
                        If cNode.Key = rst.Fields("Parent") Then
                            cNode.AddChild rst.Fields("ID"), rst.Fields("Designation")
                        End If
                    End If
            Next cNode
        End If
        'Display the tree
    End With
    Set rst = Nothing

Exit Function

MsgBox ("Error Code:" & Err.Number & " Description do Erro:" & Err.Description)
Resume Error_Exit

End Function


Comment by: Filipe Jose Roque Caetano (12-10-2018 11:48:04)

--Continued from previuos comment--
The structure of the table is something like this:

ID | Parent | Designation
1 | 0     | Root1
2 | 0     | Root2
3 | 0     | Root3
4 | 1     | Root1_Child1
5 | 2     | Root2_Child1
6 | 2     | Root2_Child2
7 | 2     | Root2_Child3
8 | 6     | Root2_Child2_Child1
9 | 8     | Root2_Child2_Child1_Child1
10 | 3     | Root3_Child1


Comment by: FILIPE JOSE ROQUE CAETANO (12-10-2018 12:02:46)

Hello again.

I forgot to mention that the recordset is based on a query "qryMenu" that, in turn, is just a way to sort the table that is showed on the last post.



Comment by: Peter Thornton (12-10-2018 17:37:23)

Hi Filipe,

There's no defined limit to the number of levels, I can't see from your code why you can't create more than 6 levels, something in the data perhaps(?).

If your nodes will always be added in sequential order, or at least such that any parent is created before any of its child nodes, you might find it easier to use the treeview.NodeAdd rather than node.AddChild.

Add root nodes in the normal way with AddRoot. Be sure to give each a unique key, its ID perhaps (if necessary converted to a string if the ID field is numeric). Then for all other nodes

Set cNode = mcTree.NodeAdd(vRelative:=rst.Fields("Parent"), _
                    vRelationship:=tvChild, _
                    sKey:=rst.Fields("ID"), _


Comment by: Filipe Caetano (15-10-2018 09:47:52)

Thank you so much Peter for looking at my code.
I'll try to implement your suggestion.
As for passing level 6, I think it should in fact be a problem with my data / table, but I can not figure out how to overcome this problem.
As I enter more levels in the treeview, it will display them, but I can not register those levels in the table. It's a strange thing, considering that up to level 6 I have no problems.
The table and the data are a simple structure, like the one presented in one of my previous posts.



Comment by: Peter Thornton (16-10-2018 12:06:09)

Hi Filipe,

It's probably something obvious in your data/table, but sometimes they can be the hardest things to find!

Try debugging your code, eg
Set cChild = cNode.AddChild( arg's)
With cChild
    Debug.? .ParentNode.Key, .Level; .Caption
End With


Comment by: CARLOS ANTONIO (16-10-2018 20:22:27)

Really this is a job "SPECTACULAr", my congratulations!
It is impressive the level of knowledge of those involved through the codes developed and the final result of the solution presented.

After that I learned that I have not learned anything yet from VBA ...

In my limitations of the tool and my knowledge, it would be possible for those involved to provide a small code of what a tree of three nodes would look like in TreeView. Taking as an example a simple spreadsheet with 3 columns. Where column 1 being the YEAR, column 2 being the MONTH, and column 3 being the VALUE OF SALE. In this case you would create a parent node being a year, then a child node for the month, and then add the value for each month related to the YEAR of the parent node.

I do not know if I was clear in my comment, and if they can make the code available here to clarify the doubts of the other colleagues, or if they can send me in my e-mail.

Right now, thank you very much for the material provided and applied knowledge.
It will be very useful for my applications


Comment by: Filipe Caetano (18-10-2018 12:39:43)

Thank you once again Peter.

I'll try your sugstion a see if that do the trick!



Comment by: Jan Karel Pieterse (18-10-2018 17:00:05)

Hi Carlos,

Perhaps this comment helps:


Comment by: Filipe Caetano (23-10-2018 21:40:02)

Hello again :)

I have been wondering if it is possible to display a count of nodes next to each parent node.

Thank you very much!


Comment by: Jan Karel Pieterse (24-10-2018 09:44:11)

Hi Filipe,

Do you mean the number of child-nodes of a node? What to do with children of child-nodes, do they count with only their parent or also with the parent of their parent node?


Comment by: Filipe Caetano (24-10-2018 09:58:15)

Hello Jan.

Thank you for showing interest in my question. What I am trying to do is recursively count the number of grandchild nodes whose child nodes will add up to that count and, in turn, parent nodes will add up the count of child nodes. However, I do not want to duplicate counts, for example:
Parent node (7)
> Child node (3)
>>Grandchild node
>>Grandchild node
>>Grandchild node
> Child Node (4)
>>Grandchild node (2)
>>>Grandgrandchild node
>>>Grandgrandchild node
>>Grandchild node
>>Grandchild node
>>Grandchild node

Thank you in advance for any assistance on this matter


Comment by: Jan Karel Pieterse (24-10-2018 10:28:39)

Hi Filipe,

Code like this adds the # of childnodes to the parent nodes:
Private Sub CommandButton1_Click()
    Dim cNode As clsNode
    For Each cNode In mcTree.Nodes
        If Not cNode.ChildNodes Is Nothing Then
            cNode.Caption = "(" & cNode.ChildNodes.Count & ")" & cNode.Caption
        End If
End Sub


Comment by: Filipe Caetano (24-10-2018 10:31:26)

Thank you very much Jan.
I didn't thought it was so simple :)

Best regards


Comment by: Filipe Caetano (24-10-2018 17:21:26)

Hi Jan,

Thank you very much. I was able to add the nodes count to the treeview, but I'm not happy with aesthetics.

Instead of showing the count at the beginning of the node's caption, is there any way to put that count at the right side limit of the treeview's frame? Example:

| Node Bla         (3)|
| Node Bla Bla     (2)|
| Node Bla         (10)|
| Node Bla Bla Bla (5)|

Best regards


Comment by: Jan Karel Pieterse (24-10-2018 17:45:19)

Hi Filipe,

The challenge will be to figure out where to put the number. First of all, you should set a non-proportional font for the entire tree, otherwise there is no way you can accurately align the characters. Then you would have to determine the maximum length of all nodes captions in the same level of the tree (with the same number of parents) and use that value to decide how many space characters need to go between the current caption and the node count.


Comment by: Filipe Caetano (24-10-2018 17:54:10)

Thank you very much Jan.

You have put me in the right track once again.

I will try to develop your idea, trying to overcome yet another problem that will be the indentation of nodes captions.



Comment by: Eduardo Bernal (27-10-2018 19:48:04)

Hi everybody
My name is Eduardo. I´m from Spain.
First of all, my congratulations for this great and powerful code.
After reading the comments I´m trying to insert the treeview in my database using the code provided by Filipe Caetano (thanks Filipe!), in order to associate each node in the treeview with a record from a table. For now, it is working, but I´m not able (I´m just a beginner in access vba code) to edit and change records in the table by changing the node caption in the treeview. This functionality would be great to edit and update any data in the main table just changing the caption node in each case. If someone has found a way to do so, please mail me an example to study and understand the code. Sorry for my pour English and thanks in advance,    


Comment by: Jan Karel Pieterse (29-10-2018 11:11:45)

Hi Eduardo,

I hope someone will respond to your question as I don't happen to have examples lying around :-)


Comment by: Matthew Nicoll (30-10-2018 23:49:14)

Thanks Jan, for helping us all with your TreeView code.
With clsTreeView build 026, in Excel 2010 VBA on Windows 7
I successfully replaced the Microsoft treeview control
in my Excel VBA project. I had one problem, described
below, and I made a few simple additions, which I'll put
in following comments.

I got error "Object variable or With block variable not set"
on statement: With ActiveNode.Control
in Sub     : ActiveNodeColor
in class mod: clsTreeView

... because ActiveNode.Control was = nothing.

Here are my relevant VBA statements:

Dim rootnode As clsNode
Set csTree = New clsTreeView
With csTree
    Set .TreeControl = Me.frTreeControl    ' tell it where to live
    Call .NodesClear
    .CheckBoxes = True
    .RootButton = False
End With
Set rootnode = csTree.Nodes.item(1)
rootnode.Checked = True ' causes a crash if csTree.Refresh not done before this.

I got around the problem by adding:

before statement:
rootnode.checked = True


Comment by: Matthew Nicoll (31-10-2018 00:04:45)

Here are a few simple enhancements I made to Build 026 of clsTreeview and clsNode.
(Trivial for those familiar with class modules, but possibly useful for others!)

A. To clsNode I added the following property, so that I don't
have to check for "ChildNodes Is Nothing" before every
reference to ChildNodes.Count:

Public Property Get ChildCount() As Long
    If Me.ChildNodes Is Nothing Then
     ChildCount = 0
     ChildCount = Me.ChildNodes.Count
    End If
End Property

B. To clsTreeView I added two events to correspond to the MS treeview
Expand and Collapse events, as follows:

1. at the top clsTreeView, add statements:

    Event NodeExpand(cNode As clsNode)
    Event NodeCollapse(cNode As clsNode)

2. in Sub NodeEventRouter, add 3 statements to
the "If cNode.Expanded" block as follows:

    If cNode.Expanded Then
        <10 lines of code omitted>
        RaiseEvent NodeExpand(cNode)     'new
    Else                                 'new
        RaiseEvent NodeCollapse(cNode)    'new
    End If

In my form module, I catch these events with:

Private WithEvents csTree As clsTreeView
Private Sub csTree_NodeCollapse(node As clsNode)
End Sub

Private Sub csTree_NodeExpand(node As clsNode)
End Sub


Comment by: Jan Karel Pieterse (31-10-2018 10:27:23)

Hi Matthew,

You call NodesClear and then try to modify Nodes(1), which of course is not in the tree as it is empty. So you need to first add nodes to your tree.


Comment by: Jan Karel Pieterse (31-10-2018 10:28:17)

Hi Gary,

After changing such a property, make sure you refresh the treeview:


Comment by: Jan Karel Pieterse (31-10-2018 10:31:49)

Hi Matthew,

Thanks for the code!


Comment by: Matthew Nicoll (31-10-2018 18:41:37)

Hi Jan,
Regarding my yesterday's comment regarding "Object variable or With block variable not set" ...

My apologies: I identified the wrong statement as the one causing the error, then I made a mistake copying VBA into the comment!
This is what the the VBA snippet should be:

Dim rootnode As clsNode
Set csTree = New clsTreeView
With csTree
    Set .TreeControl = Me.frTreeControl    ' tell it where to live
    Call .NodesClear
    .CheckBoxes = True
    .RootButton = False
End With
Set rootnode = csTree.AddRoot(sKey:=strID, vCaption:="ChartSet " & CSname)
Set csTree.ActiveNode = rootnode ' causes a crash if csTree.Refresh not done before this.


Comment by: Peter Thornton (1-11-2018 09:34:56)

Hi Matthew,

I haven't had a chance to go through your code but calling Set .ActiveNode will error if .Refresh has not been called to physically create the node controls.

You could include error handlers in ResetActiveNodeColor and SetActiveNodeColor, or in each check the control exists before attempting to change its format properties, and/or only call ActiveNode after the node controls exist with Refresh.


Comment by: Filipe Caetano (5-11-2018 13:26:24)

Hello Eduardo.
Just change “DesignationField” and “TableName” with your own object names.
Hope that helps!

Private Sub mcTree_AfterLabelEdit(Cancel As Boolean, NewString As String, cNode As clsNode)

    'Get the active node
    Set cNode = mcTree.ActiveNode
    'Update table setting it's Designation Field to the changed node's caption
    'where "ID" field relates with node's key
    CurrentDb().Execute "UPDATE TableName " & _
                        "SET DesignationField='" & NewString & "' " & _
                        "WHERE ID=" & cNode.Key & ";", dbFailOnError

End Sub


Comment by: Aleksandr (9-11-2018 12:23:46)

Hello, first of all thanks for the great treeview element .
Meantime i've got 1 error and 1 constraint with the treeview.

1. When i initialize userfrom and treeview element on it and want to check some elements that was previously selected element behave improperly by checking not only selected nodes and siblings and sometimes all elements within the list .

for checking im using following code :

if some_trigger_met then cNode.Checked = True

2. I have significant slowdown of treeview initialization on 400 rows. Mainly its occur with .Refresh . Is there any way to increase performance ? .




Comment by: Peter Thornton (9-11-2018 17:09:18)

Hi Aleksandr,

1. Your method should correctly show the intended nodes as checked. However if you include the 'triState' option, all descendant nodes of a checked node will be checked, and its parent nodes back to the root will show checked (if all their childnodes are checked) or 'tri-state' checked) if some but not are checked.

2. Normally 400 nodes shouldn't take too long to refresh but the time gets exponentially slower with more nodes. For near instant performance even with several thousand nodes you might want to try our 'pro' Treeview (see above).


Comment by: Eduardo Bernal (11-11-2018 11:58:32)

Hi everybody,
Finally I could get data from a query in the treeview, by adapting the code provided by Filipe Caetano (thanks again Filipe). Its is working properly so far, showing all the parents nodes(and childs nodes) in each level of the treeview as it was expected, but..., when I add a criteria in the query in order to filter the results to be showed in the treview, suddenly it continues working but only shows the main node and his childs (two levels of teh treeview) an do not show his childs nodes from the third level in advance. ¿any idea to solve this?,


Comment by: Eduardo Bernal (11-11-2018 18:33:32)

Hi Filipe!, your code for afterLabelEdit works perfectly.
Thank you very much for the code.


Comment by: Jan Karel Pieterse (12-11-2018 10:21:47)

Hi Eduardo,

Can you perhaps show us some relevant code?


Comment by: Filipe Caetano (12-11-2018 16:35:37)

Hello Eduardo.

I'm happy to have helped.
As for your question, see if you are not breaking the chain structure of nodes and their parents when you filter records in the query. if somehow your filter does not include the entire chain hierarchy of a given node, it will fail. For example:

Root> Child> grandchild> grandgrandchild

If you filter the query and it does not include the grandchild, you will lose the reference to the child and the Root node.

If this is not the problem, please be more specific and, if you can, show an example.

best regards


Comment by: Eduardo Bernal (12-11-2018 17:30:23)

Hi Jan,
I have tried with Filipe code to display nodes from a table, just renaming some parts to suit it to my database, and by creating a recordset from an existing table (tNormativa)using a SQL WHERE clause as a criteria to display only the records that match with a combobox (cbxCategoria) value in the form:

Private Function GetDataFromTable()

On Error GoTo Error_Trap

    Dim rst As DAO.Recordset
    Dim sql As String
    Dim cNode As clsNode

'cbxCategoria is linked with "Categoria" field from "tNORMATIVA" table:    

    sql = "SELECT * FROM tNORMATIVA WHERE Categoria='" & Me.cbxCategoria & "'"
    Set rst = CurrentDb().OpenRecordset(sql)
    Set mcTree = Me.subTreeView.Form.pTreeview
    With mcTree
        'Add Root nodes
        If rst.RecordCount > 0 Then
            While Not rst.EOF
                If rst.Fields("Parent") = 0 Then
                    Set cNode = .AddRoot(rst.Fields("ID"), rst.Fields("Designation"))
                    cNode.Bold = True
                End If
            'Add Child nodes
            For Each cNode In .Nodes
                While Not rst.EOF
                    If rst.Fields("Parent") > 0 Then
                        If cNode.Key = rst.Fields("Parent") Then
                            cNode.AddChild rst.Fields("ID"), rst.Fields("Designation")
                        End If
                    End If
            Next cNode
        End If
        'Display the tree
    End With
    Set rst = Nothing

Exit Function
MsgBox ("Error Code:" & Err.Number & " Description do Erro:" & Err.Description)
Resume Error_Exit

End Function


Comment by: Eduardo Bernal (12-11-2018 17:45:42)

(continue my last comment:)

...doing so, review works but only displays main nodes and the first level of child nodes, despite existing more child nodes that field Categoria matches with cbxCategoria values, but they are from the third level in advance in the treeview.

Nevertheless, If I use a parameter query (qryNormativa) existing in access to create the recordset (DAO), inserting a parameter in "Categoria" field as a criteria to filter just the records matching with "cbxCategoria" value in the form
like this:
    Set rst = CurrentDb().OpenRecordset("qryNormativa", dbOpenDynaset)

Then it doesn't work, and display error code 3061 (few parameters)

Or else, with the same code, if I use a SQL sentence to define my recordset, but without WHERE Clause, like this:

Set rst = CurrentDb.OpenRecordset(sql)

Then it works properly and displays every node in every level, but it does not filter by cbxCategoria field.

Sorry so much for the mistakes, my vba is as poor as my english.


Comment by: Filipe Caetano (12-11-2018 17:48:11)

Hello Peter Thornton and Jan Karel Pieterse.

Is it possible to have some of the caption of the node showing a different color as if it were a rich text? I ask this because I have the nodes displaying accounting balances in the mouse movement event next to the node's own caption and would like the negative values &#8203;&#8203;to be displayed in red.

Thank you very much.



Comment by: Peter Thornton (13-11-2018 10:47:14)

Hi Filipe,

The Label control caption does not support mixed font colours or anything like 'rich text'. Although nothing is impossible it would be extremely difficult!


Comment by: Filipe Caetano (13-11-2018 17:55:09)

Another contribution I'd like to make is sharing how to search within the treeview.

On the form that holds the subTreeView subform add a textbox and on it’s change event call the following function to search a string on the treeview.

Private Function SearchTreeview()

    Dim cNode As clsNode
    Dim cParent As clsNode

    For Each cNode In mcTree.Nodes
        If InStr(1, cNode.Caption, Name of Textbox, vbTextCompare) Then
            'Sets the backcolor of all corresponding nodes to light yellow
            cNode.BackColor = RGB(255, 230, 153)
            'Expands all parent nodes within the same hierarchy as the current node
            Set cParent = cNode.ParentNode
            While Not cParent Is Nothing
                cParent.Expanded = True
                Set cParent = cParent.ParentNode
            'Checks whether the current node has child nodes and, if so, expands it
            If Not cNode.GetChild(-1) Is Nothing Then
                cNode.Expanded = True
            End If
            'For non-matching nodes, collapses them and changes their back and fore
            'colors to match the ones they had before
            If Not cNode.GetChild(-1) Is Nothing Then
                cNode.Expanded = False
                'Blue color for parent nodes
                cNode.ForeColor = RGB(86, 118, 157)
                'Gray color for child nodes
                cNode.ForeColor = RGB(110, 110, 110)
            End If
            'White color that match with treeview's backcolor
            cNode.BackColor = RGB(255, 255, 255)
        End If
    Next cNode
    'Display all changes made above
    'Clear memory
    Set cNode = Nothing
    Set cParent = Nothing

End Function



Comment by: Jan Karel Pieterse (14-11-2018 10:06:11)

Hi Filipe,

Thank you!


Comment by: Eduardo Bernal (17-11-2018 12:54:01)

Hi Filipe!,
¿any idea for your code to use treeview with data from table (your post 10/12/2018 11:47:30 AM) with a recordset with a parameter query (or a recordset filer with a criteria clause (WHERE) in SQL sentence?
Thank you!


Comment by: Eduardo Bernal (18-11-2018 17:55:49)

I keep on trying with Filipe`s code to display data from tables (access), and the problem starts with treeviews with just one root node. For instance, a table with just one root and three levels:

ID | Parent | Designation
1 | 0     | Root1
2 | 1     | Root1_Child1
3 | 2     | Root1_Child1_Child1
4 | 2     | Root1_Child1_Child2
5 | 1     | Root1_Child2
6 | 5     | Root1_Child2_Child1

Treeview only displays:
Root1 (1st level)
Root1_Child1 (2nd level)
Root1_Child2 (2nd level)

But, the same table with two nodes root o more:

ID | Parent | Designation
1 | 0     | Root1
2 | 0     | Root2
3 | 1     | Root1_Child1
4 | 3     | Root1_Child1_Child1
5 | 3     | Root1_Child1_Child2
6 | 2     | Root2_Child1
1 | 6     | Root2_Child1_Child1

Treeview will display all levels:

Root1 (1st level)
     Root1_Child1 (2nd level)
         Root1_Child1_Child1 (3nd level)
         Root1_Child1_Child2 (3nd level)
Root2 (1st level)
     Root2_Child1 (2nd level)
Root2_Child1_Child1 (3rd level)

¿any idea to solve this?


Comment by: Jan Karel Pieterse (19-11-2018 10:34:21)

Hi Eduardo,

This should work:

Private Function GetDataFromTable()

    Dim rst As DAO.Recordset

    Dim cNode As clsNode
    Dim cNodeExists As clsNode
    Dim bNodeExists As Boolean
    Dim lNewNodes As Long
    Dim bNodeExists As Boolean

    Set rst = CurrentDb.OpenRecordset("qryMenu")

    Set mcTree = Me.subTreeView.Form.pTreeview
    With mcTree
        'Add Root nodes
        If rst.RecordCount > 0 Then
            While Not rst.EOF
                If rst.Fields("Parent") = 0 Then
                    Set cNode = .AddRoot(rst.Fields("ID"), rst.Fields("Designation"))
                    cNode.Bold = True
                End If

            'Add Child nodes
                lNewNodes = 0

                For Each cNode In .Nodes
                    While Not rst.EOF
                        If rst.Fields("Parent") > 0 Then
                            If cNode.Key = rst.Fields("Parent") Then
                                bNodeExists = False
                                For Each cNodeExists In mcTree.Nodes
                                    If cNodeExists.Key = rst.Fields("ID") Then
                                        bNodeExists = True
                                        Exit For
                                    End If
                                If bNodeExists = False Then
                                    cNode.AddChild rst.Fields("ID"), rst.Fields("Designation")
                                    lNewNodes = lNewNodes + 1
                                End If
                            End If
                        End If

            Loop Until lNewNodes = 0

        End If
        'Display the tree
    End With
    Set rst = Nothing

End Function


Comment by: Eduardo Bernal (19-11-2018 17:49:02)

Great Jan!
it works perfectly!, I´m very grateful,
Thanks a lot.


Comment by: Harun KILIÇ (9-12-2018 18:52:41)

Hi. I'm trying to develope an assembly duplicator for Autodesk Inventor, I used your codes for custonm treeview control. You have mentioned that mouse wheel scroll hook is available with in paid version.
How much is it? Thanks.


Comment by: Jan Karel Pieterse (10-12-2018 09:37:08)

Hi Harun,

You mention Autodesk, are you planning to use the treeview in Excel VBA or in another program? The pro controls are only available in Excel and Access.


Comment by: Harun KILIÇ (10-12-2018 18:28:12)

Hi Jan, I already used your treeview codes in Inventor VBA.
This is the screen capture of userform;

May be you can create a dll only for wheel scroll hook methods; for a fee; if possible. Thanks.


Comment by: Jan Karel Pieterse (11-12-2018 10:25:52)

Hi Harun,

Your image URL leads to a site which displays as "Forbidden", so I have removed it from your post.
In which application did you implement the treeview precisely?


Comment by: Danny (22-12-2018 03:38:06)

It's amazing job you did and shared to us.
I'm grateful.
I have a question that we can use the button &#12304;Allow Edition&#12305; to edit the name of label,but how to save the the data when we finish edit.

Is there have this function in this Demo Treeview or need to programming.

Briefly, I want to know that if the user (not programmer) "after label edit".

How can they save the data?
Because If the user presses the button &#12304;Demo1Treeview&#12305;,
then all the things starts again!!

Thanks and Merry Xmas.


Comment by: Peter Thornton (22-12-2018 12:33:22)

Hi Danny,

You can trap individual edits in the 'AfterLabelEdit' event which returns the node object and the new edited caption. You could automatically update the source data with the new caption in real time in this event.

You can update all data by looping nodes with a recursive type function. You could provide a button for your user to update some or all data, see the example in the demo behind the 'Dump Data' button. Alternatively you could trap the form's QueryClose event and save all data when the form closes.


Comment by: ANDRÉ LUIS RODRIGUES DOS SANTOS (28-12-2018 15:56:56)

Good afternoon,

I've implemented some situations to view hierarchically without treeview and it worked fine, I just have one question?

Note also that the mause wheel was not able to scroll the expanded data view in the treeview, was this implemented or is it not currently possible?


Comment by: Peter Thornton (28-12-2018 16:58:45)

Hi André,

It is very hard to make wheel scroll reliable in the 'free' treeview and best not to try implementing it unless for your own use only.

Wheel scroll is implemented in the 'pro' treeview.


Comment by: ANDRÉ LUIS RODRIGUES DOS SANTOS (29-12-2018 14:59:21)

I noticed that when I click 2 times on one it does not open the children, it has how to do this without using with object. ShowExpanders = false?


Comment by: Peter Thornton (31-12-2018 12:13:04)

Hi André,

I'm not sure I follow, do you mean you want to be able to double click on a node to collapse/expand and not show expanders?

If so, in the mctlControl_DblClick event comment the existing code (to enter edit mode) and copy the code from the mctlExpander_Click event.

If that's not what you mean please explain with more detail.


Comment by: Guillermo (21-1-2019 20:40:32)

Hello Peter and Jan.
Thanks for your product. It's great for me!
I detected a problem only in Access (no Excel), different versions and machines. It occurs in all applications, also in the "Treeview-26.mdb" demo.
While you are working with treeview, if you change the active application (for example, using the taskbar go to Windows Explorer), when you return to the treeview application (click again on the taskbar), it will not get active before the first click, you must click three times on the taskbar for it.
That happens when you have operated the treeview (for example, expand/contract a node) before switching to another application. Therefore, if you go out from the treeview application without any action, the problem does not occur.
Again, thank you very very much!
Best Regards,


Comment by: Peter Thornton (22-1-2019 11:45:50)

Hi Guillermo,

I understand what you are describing, particularly while developing and switching to the VBE. In the 'free' version it's complicated to change that behaviour due to the particular arrangement of windows with Access, it's OK in the 'pro' version.


Comment by: Guillermo (23-1-2019 18:03:41)

Thanks for your reply.
Due to the issue that I have posted and other features (mousewheel scroll, add/edit image in Access, etc) I'm interested in the ‘Pro’ version.
How can I do to test/acquire it?
Best Regard,


Comment by: Barati Mohammad Reza (10-2-2019 22:31:36)

I've used your ACCESS TREEVIEW but I can not create a search that
when I search for a word and find two or more similar words, it will first focus  the first one and then by click a button focus  next and next
Please guide me


Comment by: Peter Thornton (11-2-2019 10:33:48)

Guillermo contacted us via the 'Pro Treeview Enquiry' link above, it is now fully implemented in his application.


Comment by: Peter Thornton (11-2-2019 10:43:57)

Hi Barati Mohammad Reza,
See 'Extended Project Explorer' in the examples link. Note this example uses an earlier treeview version.

Find match and find next is included in the 'pro Treeview'


Comment by: Omid (16-2-2019 09:20:00)

Thanks for your codes!
Is it possible to create a rtl Tree? does it support it?
Best regards,


Comment by: Jan Karel Pieterse (16-2-2019 11:45:15)

Hi Omid,

What is an rtl tree?


Comment by: Omid (16-2-2019 13:26:27)

Thanks for reply!
I mean right to left view for non-English languages.


Comment by: Peter Thornton (18-2-2019 18:47:19)

Hi Omid,

I am a co-developer of the treeview. It does not support 'right to left' view and we have no plans to adapt it. It would be difficult without a 'rtl' system available.

Peter Thornton


Comment by: MDrost (5-3-2019 16:02:12)

Thank you, this works very smooth!... Good documentation too.

Now browsing for a datetimepicker ;)


Comment by: Jan Karel Pieterse (5-3-2019 16:13:48)

Hi MDrost,

There is a link to a very good one on this page!


Comment by: Bernd Reber (9-5-2019 02:00:00)

I got excited to see this, but sadly I'm running into a lot of errors. It starts when I try to open the demo DB with Access 2003: "Userform ufTreeView does not exist in this VBA Projet. Create it?" When I do this, run-time error 2683 occurs (there is no object in this control) and the debug line that comes up is
Set mfrImages = Me.subTreeView.Form.Controls("frImages").Object

When I comment this line out, it next stops at
Me.axSpinButton1.Value = 20

with the same error 2683
Also, the code can't be compiled. This renders the entire demo unusable. Have I missed something? Hope you can enlighten me.


Comment by: Peter Thornton (9-5-2019 10:20:00)

Hi Bernd,

The demo form includes some embedded MSForms controls, so your security settings will need to enable ActiveX controls.

If that's not the problem contact me off-line and I will send you an earlier version which hopefully will work for you.


Comment by: Robert Helgeson (9-5-2019 23:05:00)

First, thank you for the MS Access treeview control. I was able to easily incorporate the functionality into my code. Is it possible to make the mouse scroll wheel work inside the treeview control?


Comment by: Baudi Deckers (10-5-2019 11:44:00)

Hi, many thanks for your excellent tutorials, as this on the Treeview control.
My question: I have to load over de 1.600 records in the tree. I use the DoCmd.Echo to hide (and accelerate) the loading. But when the loaded tree appears after a few seconds, the tree is rather centered, instead of fully on the left; and on the last record.
I would like the tree to appear on the first record and as left as possible.
Any advise ?
I appreciate!


Comment by: Peter Thornton (10-5-2019 11:46:00)

Hi Robert,

Although possible I don't recommend including wheel scroll, and definitely not in 64 bit. If interested it is included in the 'pro' version (see above).


Comment by: Peter Thornton (10-5-2019 12:01:00)

Hi Baudi,

Normally the tree and nodes should appear where and as expected, I'm not sure why for you it becomes centered or quite what you mean by 'on the last record' (unless you set the last node as the activenode). If you want you can send me a screenshot off-line.

As far as the treeview is concerned I wouldn't expect use of DoCmd to make a difference reduce load time. In the free version time is mainly involved with the creation of controls as individual components, with 1600 nodes that could be several times that quantity and would take a while. FWIW the pro is designed differently, 1600 nodes would take an eye blink to create.


Comment by: Andrew (30-5-2019 11:16:00)

This is a great and very welcome solution. 2 questions:
1. How do I allocate different background colours to nodes based on specific data values for that node eg. status
2. How do I open a related pop-up form based on the node index.

Best Regards


Comment by: Salim Talal (30-5-2019 22:31:00)

Am trying to download but error popping up and terminating download.


Comment by: Jan Karel Pieterse (31-5-2019 11:50:00)

Hi Salim,

Which error do you get during the download?

You can find example code in the demo file. The NodeRemove example is in the ufDemo form's code, in the mcTree_KeyDown event.


Comment by: Jan Karel Pieterse (31-5-2019 16:09:00)

Hi Andrew,

The clsNode class has a BackColor property you can use to set the node color.

If you click a node, the tree's click event is triggered, giving you the clicked node, see ufDemo in the demo file:

'This gets fired when a node is clicked
Private Sub mcTree_Click(cNode As clsNode)


Comment by: Andrew (7-6-2019 11:40:00)

Really impressed by your treeview solution!

I highlight the selected node by setting .Bold to true. Is there a more direct way to set the previously selected node to .Bold = False ? At the moment I am recreating the entire tree but then the focus is lost on the selected node.

Best Regards


Comment by: Jan Karel Pieterse (7-6-2019 13:34:00)

Hi Andrew,

Does the current background color highlight work not for you?


Comment by: Bob H. (26-6-2019 21:11:00)

I'm using the free version of TreeView for MS Access. I added a Search routine so the user can type in the name of the last child node and select it. It works fine except the child node is outside the frame. Is there a way to automatically move the selected node to the top of the frame?


Comment by: Peter Thornton (27-6-2019 10:12:00)

Hi Bob,

Assuming you have set the found-node as the activenode try this:

mcTree.ScrollToView mcTree.ActiveNode


Comment by: Bob H. (27-6-2019 13:32:00)

Thank you for your response. It worked well. 1 more question: How can I set the focus to the treeview when the form is initially displayed?


Comment by: Jan Karel Pieterse (27-6-2019 15:53:00)

Hi Bob,

You can set focus to the frame which contains the treeview.


Comment by: Peter Thornton (27-6-2019 16:09:00)

Bob, just to add, in Access try this


It needs the other stuff in the example code behind the demo's subform


Comment by: Bob H (29-7-2019 17:12:00)

Is there a way to default the treeview to expand to a certain level. Currently it is fully expanded and I would like it to expand to level 2.


Comment by: Peter Thornton (29-7-2019 17:52:00)

Hi Bob,

By default the Expanded property of nodes is True. While populating if you retain a reference to each new node (if not sure this node level is >2 uncomment the If line)

' If cNode.Level >= 3 Then
cNode.Expanded = False

A different way which you can do just after populating and before calling Refresh, or during runtime and also call Refresh

    mcTree.ExpandToLevel 2
    ' mcTree.Refresh ' to update


Comment by: Carlos (30-7-2019 17:07:00)

what do we need to add to this code to turn it into a Virtual treeview? I have hundreds of Nodes and it can be really slow to upload.
Many thanks


Comment by: Peter Thornton (30-7-2019 20:06:00)

Hi Carlos,

Not calling .Refresh would give you a sort of virtual treeview, but without knowing your overall objective hard to suggest more than simply that.

If you're dealing with larger treeviews you might consider the pro Treeview, it can easily handle tens of thousands of node. It also supports virtual treeviews, mainly to facilitate a multi-level undo for treeviews. If interested see the enquiry link above.


Comment by: Mads Berge-Lind (13-8-2019 08:36:00)

I am super happy with the 'pro' Treeview and its developers!

The control is very quick, and it has all the functionality I need. I am running up to 24 simultaneous trees with thousands of nodes each, and drag and drop between them.

The support is just fantastic. Peter has modified the control over a weekend to add functionality. Having this kind of support is great.

Thank you!


Comment by: Peter Thornton (13-8-2019 13:15:00)

Hi Mads,

Thank you for this nice feedback:)


Comment by: Jamie (4-9-2019 21:49:00)

In Access, I have the Treeview subform as per the example and the "Public WithEvents mcTree As clsTreeView" in the Parent form. I do everything I wanted to do perfectly, from the parent form. However, I have another subform on the same parent (call it a sibling form to the Treview subform??) and I want to adjust the TreeView nodes from this subform. How do I reference the existing Treeview from the sibling subform.


Comment by: Peter Thornton (5-9-2019 12:17:00)

Hi Jamie

With the mcTree reference in the main form declared as Public, in the subForm refer to it simply as 'Me.Parent.mcTree', for example -

Dim cNode as clsNode
Set cNode = Me.Parent.mcTree.ActiveNode
cNode.Caption = "new caption"


Comment by: Filipe Caetano (6-9-2019 11:10:00)

I need to create a child node inside another node. My code is:

CurrentDb().Execute "INSERT INTO TBL_Categories (Parent, Designation) VALUES (" & cNode.Key & ",'New Category');", dbFailOnError

Set cChild = cNode.AddChild(sKey:=DLookup("ID", "TBL_Categories", "Parent=" & cNode.Key & "             " AND Designation='New Category'"), vCaption:="New Category")

Then I let the user enter edit mode to change the node caption from "New Category" to something else

With mcTree
.EditMode(mcTree.ActiveNode) = True
.ActiveNode.EditBox True
End With

I have a function that will create the path to that child node:

Private Function BuildCategoryDescription() As String
Dim i As Integer
Set cNode = mcTree.ActiveNode
Do While Not cNode.ParentNode Is Nothing
i = i + 1
If i = 1 Then
     BuildCategoryDescription = "[" & cNode.Caption & "]" & BuildCategoryDescription
     BuildCategoryDescription = "[" & cNode.Caption & "]" & " > " & BuildCategoryDescription
End If
Set cNode = cNode.ParentNode

End Function

But it keeps adding the "New Category" string instead of the one user edited.

How can code continue only after user edit child node caption?



Comment by: Peter Thornton (6-9-2019 11:44:00)

Hi Filipe

I don't quite follow all of what you are doing but to add a childnode to the activenode and put it in editmode ready for editing adapt the following

Set cChild = mcTree.ActiveNode.AddChild(, "New Category")
Set mcTree.ActiveNode = cChild
'' set keyboard focus to the treeview if necessary
' Me.subTreeView.SetFocus ' < Excel
' Me.frTreeControl.SetFocus ' < Access
cChild.EditBox True


Comment by: Filipe Caetano (20-9-2019 12:27:00)


Is there a better alternative (simpler or faster) to navigate to the node above/under the active node, than this?

Navigate to the next/under node:

        intIndex = cNode.Index
        For Each cNode In mcTree.Nodes
            If cNode.Index = intIndex + 1 Then
                Set mcTree.ActiveNode = cNode
                Exit For
            End If

Navigate to the previous/above node:

        intIndex = cNode.Index
        For Each cNode In mcTree.Nodes
            If cNode.Index = intIndex - 1 Then
                Set mcTree.ActiveNode = cNode
                Exit For
            End If



Comment by: Jan Karel Pieterse (20-9-2019 15:20:00)

Hi Filipe,

I recommend using the NextVisibleNodeInTree function of the tree class. The only problem is that this function currently is private so you would have to change that to public or Friend to use it.

Your current method may select an entirely different node than you expect, depending on in what order the nodes have been added to your tree.


Comment by: Peter Thornton (20-9-2019 15:22:00)

Hi Filipe,

With small treeviews it's simple and fast to loop the entire collection as your example. However do not look for the next Index value, nodes might not not be added sequentially but even if they were will not necessarily be adjacent depending on the .Expanded state. Instead look for .VisIndex, for example:

intVisIndex = cNode.VisIndex
For Each cNode In mcTree.Nodes
If cNode.VisIndex = intVisIndex + 1 Then

You could also use the built-in NextVisibleNodeInTree function, if calling from outside the clsTreeview change it from Private to Friend.


Comment by: Filipe Caetano (20-9-2019 15:26:00)

Thank you very much for your precious help.

Filipe Caetano


Comment by: Filipe Caetano (21-9-2019 13:27:00)


I have read all comment searching for help, but with no success.

How do I use the new methods in v026 (Treeview.ImageAdd and Node.ImageUpate) to add or update images, on MS Access, to the image control or directly to the treeview's nodes?

I can't seem to understand how do I can add my own images to image control, or add images to treeview nodes directly from a file path.

Thanks again for your help.

Filipe Caetano


Comment by: Peter Thornton (22-9-2019 14:13:00)

Hi Filipe,

Did you try what I suggested in my reply to Christian on 2017/1/9 -
"...create your images in a temporary userform in Excel or Word, and copy them into the Frame in Access"


Comment by: Filipe Caetano (23-9-2019 10:12:00)

Hello Peter.

Thank you for your help.

But instead of resorting to this manual process, is it possible to update the images by using a path to them on the disk, registering it in a table, and then calling each image for each node (all done in MS Access)?

Filipe Caetano


Comment by: Peter Thornton (23-9-2019 18:55:00)

Hi Filipe,

In the 'pro' treeview you can store images in a table as binary and the pass direct to the treeview which in turn will convert the binary to image, as you suggest. However it would be complicated and a lot of work to include in the free versionn. Simpler to save the image back to file (from the table) and load directly to the treeview with

mcTree.ImageAdd LoadPicture(sFile), sUniqueName


Comment by: Filipe Caetano (23-9-2019 23:02:00)

Thank you Peter.
Sorry, I do not understand what you mean with "save the image back to file from the table. I know you are a real altruist person and I don't want to be a "pain in the ass" :)


Comment by: Peter Thornton (24-9-2019 11:49:00)

Hi Filipe,

With images stored as binary in a table you can write them back to file and load into the treeview, something like this:

'with ImageName and Blob as the field names for the image-names and binaries
sName = rst![ImageName]
sFile = "C:\" & sName & ".bmp"
byteArray = rst![Blob]
ff = FreeFile
Open sFile For Binary Access Write As ff
Put #ff, 1, byteArray
Close #ff
mcTree.ImageAdd LoadPicture(sFile), sName

In passing, with the 'pro' Treeview no need to round-trip to file, simply:

mcTree.ImageBinaryAdd byteArray, sName


Comment by: Filipe Caetano (1-10-2019 09:59:00)


Is it possible to raise an event (for example mcTree_MouseUp or mcTree_AfterLabelEdit) and use it in a normal module instead of using it in a form module?

Because I have multiple tree controls, I've tried to move their specific events to a single module and delete them from form modules, but then they don't get triggered.

Thanks in advance.

Filipe Caetano


Comment by: Peter Thornton (1-10-2019 12:09:00)

Hi Filipe,

You can raise additional events like MouseUp though AfterLabelEdit is already included, see NodeEventRouter in clsTreeview and it's all there for you.

Events can only be trapped in class or form modules (a type of class module). Code in an event stub can of course call a routine in a normal module but normally that's not a good approach, at least do not maintain treeview or node references in a normal module.

If you have a small number of treeviews trap their events in the same form or class instance with mcTree1, mcTree2 etc. If you have several treeviews create a separate 'WithEvents' treeview class.


Comment by: Filipe Caetano (2-10-2019 10:27:00)

Hi Peter.

I don't think I understood everything you wrote but I'll try to follow your directions.

Thank you very much once again.

Best regards,
Filipe Caetano


Comment by: Filipe Caetano (3-10-2019 17:17:00)

Hi Peter.

Let me ask for a simple example.

if I put this on a normal module:

Public TreeView As clsTreeview

Public Sub TreeView_Click(cNode As clsNode)
Msgbox cNode.caption
End Sub

How can I trigger the click event on the TreeView without having to put this on the form module:

Private WithEvents mcTree As clsTreeview

Private Sub mcTree_Click(cNode As clsNode)
Call TreeView_Click(cNode)
End Sub

Best regards,
Filipe Caetano


Comment by: Peter Thornton (4-10-2019 09:58:00)

Hi Filipe,

As I mentioned last time it is not possible to trap events in a normal module. You can only use WithEvents in a class module, form modules and object modules like Excel Worksheet modules are types of class modules. Maintaining a reference to a treeview instance in a normal module serves no purpose, except perhaps occasionally for special objectives.

Also as I mentioned last time, if you have multiple treeviews you can handle their events with an array or collection of class modules, without trapping any events in the form. With the treeview it is important to ensure all 2-way parent child (clsTreeview <> clsNode) references are fully cleared when done, this needs extra care if trapping events outside the form to ensure TerminateTree gets called for each instance when closing.


Comment by: Filipe Caetano (4-10-2019 11:26:00)

Thank very much Peter.

Best regards,
Filipe Caetano


Comment by: Joe Kane (9-10-2019 14:31:00)

This is very nice work, I salute your implementation.

I am considering the use of the treeview for my application. One of the functions that appears to be missing is a way to search the tree for a node, or test if a node exists.

I will be building the tree from data that has the paths flattened (So I will probably implement an inverse function to the fullpath method of clsNode.

So as I'm processing paths, I'd only want to create new nodes/child nodes if they don't already exist.

It looks like the code will throw a runtime error if you try to add a node with a key that already exists. So I suppose I can trap around that.

I can also maintain my own list of nodes as I create them, probably in a simple dictionary, but it is some added overhead.

I see that Nodes returns a collection object. This always frustrates me about collections (no native "Exists" method).


Comment by: Jan Karel Pieterse (9-10-2019 15:54:00)

Hi Joe,

Thanks for the kudos :-)
You're right, there is no NodeExists property or function. The good thing is that given that you get access to the source code it is quite easy to add the functionality yourself, simply add a routine to the clsTreeview class and you're good to go. I agree though, it is a collection so there is no Exists property and you still have to traverse the entire collection to find out if it is in there. We wanted to keep this VBA-only, so without any refs outside of the default Office ones.


Comment by: Filipe Caetano (9-10-2019 22:23:00)

I know how to get all node's parent nodes by doing this:

    Dim cNode As clsNode
    Dim cParent As clsNode
    Set cParent = cNode.ParentNode
    Do While Not cParent Is Nothing
        Debug.print cParent.caption
        Set cParent = cParent.ParentNode

But how do I loop through all node's child nodes recursively?
With this code I can only list the first sublevel child nodes of some given node:

    Dim cChild As clsNode
    For each cChild in cNode..ChildNodes
        Debug.print cChild.caption
     next cChild

I would like to have something like "CheckTriState" that when we check a node, all child nodes and their own child nodes are also indefinitely checked.

Thank you very much.


Comment by: Jan Karel Pieterse (10-10-2019 11:59:00)

Hi Filipe,

You loop through childnodes recursively as you said:

Sub LoopChildren(cNode As clsNode)
    Dim cChild as clsNode
    If Not cNode.ChildNodes Is Nothing Then
        For each cChild in cNode.ChildNodes
            Debug.print cChild.caption
            LoopChildren cChild
        Next cChild
    End If
End Sub

The (un)checking of childnodes is already available, try the Excel demo and you'll see it in action. The demo without icons uses tristate checkboxes, the demo with icons uses non-tristate checkboxes.


Comment by: Filipe Caetano (10-10-2019 13:32:00)

Thank you very much Jan.

It works!
Great solution.

Filipe Caetano


Comment by: Selami Demir (13-10-2019 18:23:00)

Dear authors of VBA treeview. I've used your control in one of my projects, which is an MS Excel VBA tool for wastewater treatment plant simulation. Your control helped me to easily design a userform on which simulation results are classified in treeview. I will publish my work in a scientific journal and I will add the following text to the acknowledgement. Is it Ok?


The acknowledgement text goes here:
The author would like to thank JKP Application Development Services and Peter Thornton ( for creating a wonderful, all-VBA treeview control, which was extremely beneficial for implementing the userform to view the simulation results.


Comment by: Jan Karel Pieterse (14-10-2019 10:04:00)

Hi Selami,

Yes that is perfect. Would you be willing to post a reference to the published article here, once it is available on-line?


Comment by: Marcus (28-10-2019 21:22:00)

verry nice tool. Thank you for your effort. Is it possible use it as a kind of file explorer. I used the MS treeview to navigate thru folders and files. How can I use your tool to do so?


Comment by: Jan Karel Pieterse (29-10-2019 11:52:00)

Hi Marcus,

It isn't very hard to replace the common controls treeview with ours, I suggest you study the demo Excel file and then just try to apply that to your own project. If you run into a problem, let us know and we'll try to help.


Comment by: Martin Liss (26-11-2019 19:48:00)

The font size of the ControlTipText seems smaller than in a traditional Treeview. Is it possible to make it larger?


Comment by: Jan Karel Pieterse (26-11-2019 20:25:00)

Hi Martin,

It should be the same as the tooltip for any other control on your userform. I think there should be a setting in Windows which controls that, but I have no idea which one.


Comment by: Martin Liss (26-11-2019 20:42:00)

Yes it may be the same font size as the buttons, etcetera in my userform, but the ControlTipText font size is definitely larger in a traditional Treeview then in your replacement. Everything else works great otherwise. Is there some way for me to send you a picture or two?


Comment by: Ron Mittelman (27-11-2019 00:41:00)

Great looking control!

I'm looking to replace the stock Switchboard Manager with a more versatile form, and thought a treeview would be perfect to represent the menu heirarchy.

I don't see any methods for moving a node up or down, but please correct me if there are these methods. Is it possible to move nodes, and if so is it possible to restrict moving the nodes only within the existing parent node?



Comment by: Ron Mittelman (27-11-2019 00:43:00)

Will the TreeView control work properly in Access 2016? I notice that version is not listed in the article.



Comment by: Jan Karel Pieterse (27-11-2019 09:37:00)

Hi Martin,

I can imagine the controltip to be different of the traditional treeview, this is likely due to the fact that it is part of an external library. Given that all controls of our tree are "internal", they share the properties of all built-in controls. There is no way to change the controltooltip formatting for just one (set of) control(s). But hey, consistency is important is it not?

You could program a mousemove event to show a label control on top of the tree which you can format any way you like. The challenge would be to make sure the label disappears in all cases when the mouse leaves the control, which is not easy to do!


Comment by: Jan Karel Pieterse (27-11-2019 09:42:00)

Hi Ron,

Addressing both questions...

Yes the treeview will work in that version of Access. I've updated the page accordingly.

Moving nodes (drag and drop) is something that is reserved to the pro version of the control, which is available for purchase. Let me know if you're interested.
Given that the free version includes all source code you can of course also implement drag and drop yourself :-)


Comment by: Eduardo Teles (11-12-2019 17:04:00)

Great tool Peter!

I'm unexperienced but on Excel i managed to Populate The "DataDump" sheet, but... I can't populate the Treeview from the "DataDump" Sheet back to treeView :(
I'm confidant that there's a way, can you please show me?

Thank you! :)


Comment by: J.K. Pieterse (11-12-2019 20:28:00)

Hi Eduardo,

Perhaps this comment helps?


Comment by: Jamie (14-12-2019 19:27:00)

1) How do I change the background colour of the tree? I know that the UserForm picks up the colour when it's added to the subTreeview form, but how do I reference it, once it's created.

2) I often get "XXXX Can't move the focus to the control subTreeview". My design is a main form with the SubTreeform on the left and another subform (ObjectWindow) on the right. When I select something in the tree I change the contents of the subform on the right. If I am in the middle of editing the data in the ObjectWindow and I click on the tree, that's usually when I get this error. I have tried to stop the focus from moving, but I have had no luck thus far.


Comment by: Peter Thornton (15-12-2019 11:16:00)

Hi Jamie,

1) Apply default formats (incl font) as required for the background, they will also be inherited by default by node labels:
mcTree.TreeControl.BackColor = RGB(240, 250, 255)

Apply before calling Refresh.

2) With the Access version there can be issues with focus, mainly while developing (if that's what you mean by 'Objectwindow') but also after alt-tab'ing windows, though usually not a problem in use. It's a lot of work to change and not viable to add to the free version, the 'pro' version is much better in this respect.


Comment by: Jamie (19-12-2019 18:14:00)

I have a need to open the tree to a specific node (rather than to a specific level). I have the node's key. I have tried:
Set .ActiveNode = .Nodes("key")
but it only appears to work if the parents of the node are all expanded.

Is there a way to have the tree expanded "down" to a specific node?


Comment by: Peter Thornton (19-12-2019 19:56:00)

Hi Jamie,

After setting the active node call .ScrollToView

Also see ScrollToView in the documentation and search this page for same.


Comment by: Jamie (2-1-2020 15:50:00)

Is there a way to disable the mcTree control? I have another subform, called "ObjectWindow" on the same parent form as the subTreeview subform (A sibling form, if you will). I don't want people be able to click on nodes in the tree while I am editing data in subform "ObjectWindow", so I would like to disable it.


Comment by: Peter Thornton (2-1-2020 16:11:00)

Hi Jamie,

mcTree.TreeControl.Enabled = False

If that might confuse the user maybe hide it completely with .Visible = False when going into some obvious different mode.


Comment by: Hervé Vuillaume (13-1-2020 11:16:00)

That you for your treeview, I'm currently working for implementing it in a big Word macro.
I use treeview because I need lists with icons.

But I have 2 remarks:
- could you add an option to totally remove the root node ?
- could you add also an option for having a double click if the treeview is not in edit mode ?

For the double_click, I had modified the function like this :

Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
' PT a node label has been double-clicked, enter edit-mode if manual editing is enabled
    Dim bDummy As Boolean

        If moTree.EnableLabelEdit(bDummy) Then
            moTree.EditMode(Me) = True
            EditBox bEnterEdit:=True
            If Me.Tree.AppName = "frmSelectDocType" Then
                Call frmSelectDocType.tvwFiles_DblClick
            End If
            ' moTree.NodeEventRouter Me, "Caption", tvDblClick
        End If
End Sub

Thanks in advanced and thank you for your code.


Comment by: Peter Thornton (13-1-2020 20:43:00)

Hi Hervé

#1 To delete or remove a root node use mcTree.NodeRemove cNode, where cNode refers to the root node (or any other node) you want to delete.

There's an example in the demo, select the node and press Delete on the keyboard, see the mcTree_KeyDown event.

#2 You've correctly uncommented mcTree.NodeEventRouter the node label's DblClick event. In NodeEventRouter in clsTreeview also uncomment the following
Else tvDblClick
RaiseEvent MouseAction

In the declarations uncomment:

And in your form add the MouseAction stub from the right dropdown which should now appear when you select mcTree in the left dropdown.

You can use the 'MouseAction' example to pass all events, if you prefer make and raise your own dblClick event.


Comment by: Peter Thornton (13-1-2020 20:46:00)

Re #2, I forgot:
In the declarations uncomment:
Event MouseAction


Comment by: Andrew de beer (6-2-2020 07:31:00)

Good Day.

Is it possible to change tr Font, Font Siza and Forecolor of the node labels?

Best Regards


Comment by: Peter Thornton (6-2-2020 10:32:00)

Hi Jamie,

In the Excel version apply your choice of Font properties to the tree-control frame, node labels will inherit the same properties.

In Access, referring to the demo adapt the ApplyDefaultFont routine in the subform code module. Call ApplyDefaultFont before adding any nodes, for example in 'Get pTreeview' just above.

Nodes also have a ForeColor property so you can change them individually.


Comment by: Richard Cawthorn (10-2-2020 12:05:00)

I'm having an issue with using icons. I have a 12x12 icon that does not show on the tree.

I've tried adjusting mcIconSize and mcIconPad, but to no effect (no visible change seen).


Comment by: Jan Karel Pieterse (17-2-2020 09:38:00)

Hi Richard,

Is the icon visible in an image control if you manually add it to the userform?


Comment by: Bernard (27-2-2020 11:15:00)

It would be wonderfull if the scroll wheel is supported when having a large tree.
For the rest the tree is working great !


Comment by: Jan Karel Pieterse (27-2-2020 13:03:00)

Hi Bernard,

Scroll wheel support is included in the pro version of the treeview.


Comment by: Claus Krog Mikkelsen (17-3-2020 10:03:00)

Hi, and thanks for a fantastic treview module.
I have a question:
I would like to change the node icon to another image from the image-frame to visualize a status-change of the node.
I have tried to:
- set a new .ImageMain
- set a new .ImageExpanded
- and then called a .Refresh
But nothing happens.
Is there a way to do that and still have the same node selected?
Best Regards Claus


Comment by: Jan Karel Pieterse (17-3-2020 14:17:00)

Hi Claus,

In the NodeAdd method, make sure you include both the ImageMain and the ImageExpanded arguments. The class modules will then handle display of the correct node icons.


Comment by: Claus Krog Mikkelsen (17-3-2020 22:17:00)

Hi Jan, thanks for a very quick answer - but I can't make it work.
This is what I do:
Set cNode = ParentSampleNode.AddChild(Sample_number, Sample_name, "iS1", "iS1")

and later trying to change the icons:
cNode.ImageMain = "iS2"
cNode.ImageExpanded = "iS2"

But I found a solution in the comment-list from 11-4-2014 16:35:10, doing this:
cNode.ImageMain = "FLGUSA02"
cNode.Expanded = Not cNode.Expanded
cNode.Expanded = Not cNode.Expanded

Is this still the way to make it work?

Best Regards


Comment by: Jan Karel Pieterse (18-3-2020 10:21:00)

Hi Claus,

Yes that is a good way to do it. If you assign a different Icon name to a node, it does not trigger reloading the icon, changing the expanded state does.


Comment by: Andreas Katits (21-4-2020 11:28:00)

I use the free version of the Treeview and I have a suggestion in your Code.

Module clsNode
Private Sub moEditBox_KeyDown

You make a distinction between Mac and Win because of NewLine. You can use just the constant vbNewLine. This constant works on every OS properly. It uses I think vbCrLf on Windows and vbCr on Mac.

Best Regards


Comment by: Jan Karel Pieterse (21-4-2020 13:00:00)

Hi Andreas,

Thank you!


Comment by: EJ (23-4-2020 10:20:00)

There might be some missing parts within the Access version?
I got messages like "User-defined Type not defined".
I running office 365, is that a problem?
Do you have any solutions?


Comment by: Peter Thornton (23-4-2020 13:42:00)

Hello EJ,,

I have just tested the current Access download in Office 365 and it all works fine for me. Are your security settings enabled for macros and ActiveX controls (for the MSForms Frame on the Access form with the images). If all enabled where are are you getting those messages?


Comment by: Ayush (15-5-2020 20:49:00)

I was trying to save the date, after updating the tree, but while reading it to populate tree, it is throwing error at
Set nNode = nNode.AddChild(, CStr(vData(lRow, lLevel)))


Comment by: Peter Thornton (16-5-2020 12:02:00)

Hi Ayush,

Does nNode refer to a node (clsNode) that already exists, if so-
What is the value of vData(lRow, lLevel) when the error exists. If lRow and lLevel are within the bounds of the array, can the element value be converted to a string with, for example Null and error values can't be.


Comment by: Brian (17-6-2020 02:29:00)

I have used MS Access Treeviews for years. My company just updated to 64 bit Office and everything stopped working. I found your tool with an internet search. I looked through your code and it looks very promising.

In looking at your code, it looks like a separate query is used for each level in the tree. I currently have a single table setup to control my trees. The structure is...
NodeID: numeric value with no duplicates
ParentID: numeric value - 0's are root nodes. Child nodes contain the NodeID for the root it belongs under (may be several levels deep)
NodeOrder: numeric value to sort Nodes of a given level in the correct order
NodeText: text to display for the node in the three
NodeObjectType: Form, Query, Report, etc. (used if clicking on a node will open another object)
NodeObjectName: name of Access table, form, query, etc. (used if clicking on a node will open Access object)
NodeImage: icon to display in tree

With rs
     While Not .EOF
         strKey = !NodeID & ";" & Nz(!NodeObjectType) & ";" & Nz(!NodeObjectName)
         If !ParentID > 0 Then
            TreeView0.Nodes.Add(getNodeIndex(!ParentID), tvwChild, strKey, !NodeText).ForeColor = 16711680 ', CStr(!NodeImage)
            TreeView0.Nodes.Add , tvwLast, strKey, !NodeText, CStr(!NodeImage)
         End If
End With

Is there a way to adapt this tool to load a treeview off of a single table set up in this manner? Using this method allows us to add new items to the tree very easily, with no worry as to whether we have enough levels built into the VBA code.

Thank you for your assistance.


Comment by: Peter Thornton (17-6-2020 10:30:00)

Hi Brian,

In the 'pro' version nodes can be added in any order, even before their before prospective parents were added. But if I follow, in your code it looks like child nodes are always added to an existing parent (except root nodes which are added with 'tvwLast'). If so, try replacing the following in your code:

TreeView0 > mcTree, or your name for the WithEvents clsTreeview reference.
Nodes.Add > NodeAdd
tvwLast > tvLast
tvwNext > tvNext


Comment by: Jan Karel Pieterse (17-6-2020 10:32:00)

Hi Brian,

You definitely can do that, you just need to adapt your current code to use the properties and methods of our tree rather than the common controls one. Instead of using AddChild, check out our implementation of the NodeAdd method, as that allows you to specify the key of the parent node.
I advise you to download the Excel sample as that has more extensive documentation.


Comment by: Brian (20-6-2020 01:15:00)

Peter and Jan Karel, thank you for your assistance. I got my treeview to populate and function as desired. I have one additional question. With the ActiveX treeview, I had buttons on my form to expand/collapse all nodes. Is there a way to do this with your tool without rebuilding the entire tree (I can make it work by rebuilding the tree and adding cNode.Expanded = True/False (depending on button pressed)? I would rather not have to rebuild the entire tree if there is another way to accomplish this task.

Thank you


Comment by: Jan Karel Pieterse (22-6-2020 10:28:00)

Hi Brian,

There is an "ExpandToLevel" method for expanding/collapsing the tree to a certain nodelevel.


Comment by: Raphael Love (8-8-2020 14:10:00)

Is there a way to let sub procedure from standard module to control mcTree?

Thank you for your assistance.


Comment by: Peter Thornton (9-8-2020 13:21:00)

Yes, several ways. You could maintain a reference to the form in the normal module, and call a public method in the form; either process the mcTree reference in the form or return a copy of mcTree to the normal module. Or you could maintain a copy of the mcTree reference in the normal module.

Whichever approach you go for it'll be important to ensure any references in the normal module (to the form or the treeview) are destroyed when the form closes, in particular ensure that TerminateTree gets called.


Comment by: Vas Rabani (27-9-2020 10:38:00)

I'm not sure but there seems to be a bug in the code for 'Dump Data' button

if you amend the code for the Sub GetData1 to display the node.key in the excel output

rng(lCt, lLevel) = cParent.Key & "|" & cParent.Caption

The root node key is not showing as RootNode1 but the key is displayed as a run date
example :

27/09/2020 09:23:50|Root Node1

Same behaviour exists when reading data - although the key has the right key on the front end, when dumping the data the date behaviour is showing

Thought it was odd. Is this an expected behaviour?

Great site ! and great implementation of the TreeView!


Comment by: Peter Thornton (28-9-2020 11:21:00)

Hi Vas,

Indeed that is not expected behaviour but I cannot re-create what you describe. For me when I include your line that adds the Key the following is dumped in the cell for the root node:

Root1|Root Node1

I can't imagine how but for you it looks like the Key is replaced with 'Now'. If you want to send me your version of the demo file I will have a look.


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.