An MSForms (all VBA) treeview

Pages in this article

  1. Features
  2. How To Use
  3. Examples

How to use

This page outlines the minimum steps needed to add this treeview control to your own Excel or Word VBA project. For Access the instructions are different, those can be found in the Access download.

The container control

Open the designer window of your userform and add a frame. This is where the treeview will be built. We recommend these properties (which are of course optional):

 Recommended properties for the container frame
Recommended properties for the container frame

Your userform would look like this:

 The container frame on your userform
The container frame on your userform

If you want images in your treeview, you'll have to add another frame (we called it frmImageBox) and then add Image controls within that frame. Set the Visible property of the frame to false to avoid it showing up on your userform. Like so:

 The images frame on your userform
The images frame on your userform

If you want to add images, make sure you name the new image controls properly, as it is the name of the imagecontrol you need to pass to the cNode class to get that image displayed. An easy way to get the images is to copy the entire frame with images from our userform to yours.

Class modules

Copy the class modules clsNode and clsTreeView to your project (you can simply drag them to your project). Your project (if you started with an empty workbook) would look like this:

 Project with class modules in place

The project with class modules in place

So far, things have been simple. The next parts are a bit more compex, but nothing too hard!

Code behind your userform

For the treeview to work, your userform needs some code, in particular these elements are necessary:

  • Some variable declarations that will enable you to address the treeview and have your form handle events
  • Initialisation code to:
    • add nodes to the tree
    • draw the treeview

That's it!

Variable declaration

Add this code to the declaration section of your userform:

'Add this to your form's declaration section
Private WithEvents mcTree As clsTreeView

That's it! No more variables needed!

Of course the name of the variable mcTree is totally up to you.

If you need another (independent) treeview on your form, simply add another frame to hold it and an additional variable in the forms declaration section (of course you name it differently than the other one) like the one shown here.


In the intialisation routine of your form, you need code that adds nodes to the tree and when you're done adding nodes, you need to set some properties of the treeview. Then you'll want the treeview to be displayed.

Adding an instance of the treeview to your form

Adding the instance is easy:

    'Instantiate a new instance of the treeview class and set a module level variable to hold it:
    Set mcTree = New clsTreeView

Now tell the tree class instance which frame is its container:

    With mcTree
        'The VBA treeview needs a container Frame control on the userform.
        'Just draw a frame on your form where the treeview should appear
        'Make sure the frame has no border and no caption
        'Set it to show both scroll bars. (Keepscrollbarsvisible to both)
        'Set it to the special effect "Sunken"
        'Note that node label formats such as back/fore colors and font adopt
        'the frame container's properties, so change if/as required.
        '(you can copy the frame named frmDemo from this userform )
        'Then pass this frame to the TreeControl of the treeview class

        Set .TreeControl = Me.frmDemo
        'Title for message boxes:
        .AppName = Me.AppName

Note that most of the code listed below is within the With mcTree ... End With structure.

Setting initial look

You'll want control over the look and feel of your treeview, here is some example code (this code comes immediately below the code sample show above):

        'Set some properties
        .CheckBoxes = True
        .RootButton = True
        .LabelEdit = 0 'default is 0 can be edited (like LabelEditConstants tvwAutomatic/tvwManual)
        .Indentation = 20 * 0.75 'defaults to 11.25
        .NodeHeight = 16 * 0.75 'defaults to 12
        .ShowLines = True
        'If your form has icons in an iconframe (called frmImageBox),
        'you could use icons for the expand and collapse buttons:

        Call .ExpanderImage(Me.frmImageBox.Controls("Win7Minus").Picture, Me.frmImageBox.Controls("Win7Plus1").Picture)

If your treeview needs to show images, add a frame control with Image controls inside. Lets call it frmImageBox. This is how you tell the class where the images are:

        Set .Images = Me.frmImageBox

That is just about all the plumbing you need to get started.

Adding nodes

First of all, a couple of variables are needed to add nodes:

'The root of the tree
Dim cRoot As clsNode
'A node
Dim cNode As clsNode
'An extra variable should you need to remember a certain node
Dim cExtraNode As clsNode

Next we'll start by building the rootnode:

        ' add a Root node with main and expanded icons and make it bold
        Set cRoot = .AddRoot("Root", "Root Node", "FolderClosed", "FolderOpen")
        cRoot.Bold = True

Note that the tree can have more than one rootnode, there is a special RootNodes collection to which you automatically add new roots by calling the AddRoot method.

As you can see, we assume there are two icons in the image frame called FolderClosed and FolderOpen respectively.

Now we want to add children to the root. This is the code from our demo form:

        'Add branches with child nodes to the root:
        'Keys are optional but if using them they must be unique,
        'attempting to add a node with a duplicate key will cause a runtime error.
        '(below we will include unique keys with all the nodes)
        Set cNode = cRoot.AddChild("1", "1 A", "FLGNETH")
        cNode.Bold = True
        'Add a 2nd branch to the root:
        Set cNode = cRoot.AddChild("2", "2 B", "FLGSWED")
        cNode.Bold = True
        'If you want to add more child branches to a branch later on, use a variable to store the branch.
        Set cExtraNode = cNode.AddChild("2.1", "2.1  level 2", "NOTE03", "NOTE04")  ' include an expanded icon
        cExtraNode.Expanded = False   ' this node will initially be collapsed,
                                      ' its child node controls will be created when expanded

        'To add a branches to a branch, make sure you set a variable to its 'main' or parent branch when created
        'Then use the Branch's AddChild method, here to create multiple levels

        Set cNode = cNode.AddChild("2.2", "2.2  level 2", "NOTE03", "NOTE04")    ' include an expanded icon
        Set cNode = cNode.AddChild("2.2.1", "2.2.1  level 3", "OpenBook")
        Set cNode = cNode.AddChild("", "  level 4", "Scroll")
        Set cNode = cNode.AddChild(" ", "   level 5", "GreenTick")

        'Now add another branch to the branch we stored earlier
        cExtraNode.AddChild "2.1.1", "2.1.1  level 3", "OpenBook"

        'Add a 3rd branch to the root, with a child node
        Set cNode = cRoot.AddChild("3", "3 C", "FLGUK")
        cNode.Bold = True
        cNode.AddChild "3.1", "3.1  level 2", "Scroll"

        ' and add a 4th branch to the root
        Set cNode = cRoot.AddChild("4", "4 D", "FLGUSA02")
        cNode.Bold = True
        cNode.Caption = "4 D  +" & mlCntChildren

        ' add a bunch of child nodes to the 4th branch
        For i = 1 To mlCntChildren  ' 15
            Set cExtraNode = cNode.AddChild("4." & i, "  4.1 " & Right$("000" & i, 4), "Scroll")
            '  add some alternate row colour formats
            If i Mod 2 Then
                cExtraNode.BackColor = RGB(255, 255, 220) ' pale yellow
                cExtraNode.ForeColor = RGB(180, 0, 0)     ' dark red font
            End If

Display the tree

Displaying the tree is as simple as calling one method:

    'Fill the tree


When the form goes out of scope (i.e. out of memory) you need to remove the treeview from memory:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
'Make sure all objects are destroyed
    If Not mcTree Is Nothing Then
    End If
End Sub


We've worked hard to create a reliable and performant treeview. If you encounter bugs, please let us know so we can work on them. Better yet: if you have fixed a bug you found, send us your updated code so we can add the fixes you made.

In any case, comments (and compliments) are welcome!




All comments about this page:

Comment by: Cyril (23-2-2013 18:04:03) deeplink to this comment

Thank you very much for your amazing work.

I'm actually working on a project with lot of xml datastorage files, I will improve your code on monday and send you feedback if necessary.

See you

Comment by: Mike (2-5-2013 08:13:30) deeplink to this comment

Thank you for your work!

In a Access-Database the ScrollBars are visible but doesn't work :-(

Thank you very much for your support.

Bye, Mike

Comment by: Jan Karel Pieterse (2-5-2013 09:42:53) deeplink to this comment

Hi Mike,

You're welcome!

We're working on a new build for Access with loads of fixes, so please be patient with us :-)

Comment by: Joost Halenbeek (2-6-2013 20:13:09) deeplink to this comment

Hello Jan Karel,
(Hallo, ben je net als ik Nederlands?)
Absolutely loving the first impression of your TreeView, great work.
I've been struggling with mscomctl TreeViews for some time now.
I can get them working, but they always have new surprises, for instance try placing them on a multipage control...argh
Your TreeView has passed the test so far, happy.
My question: I'm trying for a "flat" layout of my userforms (yes, a follower or fashion...) I managed to flatten the frame that the treeview is associated with, by setting its properties via the .TreeControl property, just before doing a .Refresh. (Is that a sound method?) Is it also possible to make the scrollbars flat? I mean like the Windows 8 style scrollbars?

Best Regards, Vriendelijke Groet,

Comment by: Jan Karel Pieterse (2-6-2013 20:33:40) deeplink to this comment

Hi Joost,

Yes, I'm Dutch (as you can probably tell from my home page here :-) ).

I don't think you can change the appearance of the scrollbars, as those are just the built-in ones the frame control shows.

But then again, I've seen people do stuff with userforms I'd never have held possible, so who knows...

Comment by: Billy Hamilton (4-6-2013 21:10:17) deeplink to this comment

The download page mentions build 024, but when I take the link, I get a zip file with build 023.

Comment by: Raymond Rooks (18-7-2013 02:25:20) deeplink to this comment

This looks like a really great tool. I am quite impressed with it.

I do have a question about the tri-state checkboxes, though. If you set them programatically, parent checkboxes aren't properly set grey (instead of black) if only part of the tree is selected. It must have to do with mctlCheckBox not being defined since the user didn't actually click on a box.

Is this working as intended? I am loading saved values onto a form, and it would be great to see in the parent if all values were selected, or just some...up to the root.

Comment by: Sami Souki (18-7-2013 13:50:40) deeplink to this comment

regarding the treeview control... how can i use the doubleClick event on nodes...

Comment by: Michael Bleau (18-7-2013 16:00:36) deeplink to this comment


Thank you for this. A question:

I am using Access 2010 and am looking for a frame container control. The closest I have found is something called Unbound Object Frame. It has no scroll bars however. On the other hand, your demo seems to use a subform control. Am I on the right track?

Any help is welcome/



Comment by: D. Spirydzionak (31-7-2013 11:18:03) deeplink to this comment

Hi guys,

Great work!
TreeView works fine for me (using it in Excel) and helps me a lot.
Thank you very much!

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


You should be able to find how how to do this using the MSAccess download on the previous page.

@Sami: I seem to recall I have given an example in one of the comments ione one of these pages. Make sure you click the show all comments link to see them all.

@Raymond: something to look at. I'll put it on the list...

Comment by: Daniel (4-9-2013 18:29:20) deeplink to this comment

Hi Jan

Thanks so much for this treeview... mscomclt make me crazy because my app don't work in others pcs ...

just one question: ¿who i do for select (highligth it) a last node inserted by code?

sorry because my english

Daniel from Colombia!!!

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

Hi Daniel,

Your English is just fine :-)
If you still have an object variable pointing to that node, like nNode, then you can use code like this to ensure that that node becomes the active node:

Set mcTree.ActiveNode = cNode

Comment by: Daniel (4-9-2013 21:56:22) deeplink to this comment

sorry a have a mistake

forget the last comment

thanks for you help!!


Comment by: Antonio (17-9-2013 14:20:55) deeplink to this comment

Nice control and code. Many thanks for your work.

I want to apologize if my English is not very correct. It's not my mother languange.

I've changed a little, enhancing the Double-Click of a Node and adding a new property "Children", to get te children nodes count.

If the node doesn't allow editions, then a Double-Click performs as Expander Click

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.Children Then
         End If
        End If

End Sub

Public Property Get Children() As Integer
Children = 0
If Not Me.ChildNodes Is Nothing Then _
    Children = mcolChildNodes.Count
End Property

Best regards.

Madrid (Spain)

Comment by: Jan Karel Pieterse (17-9-2013 16:00:21) deeplink to this comment

Hi Antonio,

Thank you for your comments!

Using your Children property is simpler than using ChildNodes.Count, because the latter needs an extra check to see whether the ChildNodes collection isn't nothing.

HOwever, this can be prevented by changing this (in clsNode):

Public Property Get ChildNodes() As Collection
    Set ChildNodes = mcolChildNodes
End Property


Public Property Get ChildNodes() As Collection
    If mcolChildNodes Is Nothing Then
        Set mcolChildNodes = New Collection
    End If
    Set ChildNodes = mcolChildNodes
End Property

Comment by: Graham (9-10-2013 18:06:29) deeplink to this comment

Just wanted to say thank you for the development of this, it's a brilliant bit of kit.

I was tearing my hair out trying to get a common control treeview working on all company pcs with very little success, so this has been a life-saver.

Comment by: Jan Karel Pieterse (10-10-2013 10:14:50) deeplink to this comment

Hi Graham,

Thanks for letting us know. Always nice to hear people appreciate our hard work.

We've put a lot of effort in making it look good, versatile and easy to implement.

Comment by: Ray Longmoor (16-10-2013 21:41:09) deeplink to this comment

I have started to use your treeview but I have run in to a problem

I have embedded the tree view in a tab control, the tab control has the tree view in a sub form

I use access 2010 adp project, The tree view works fine every time when I find records and it populates the tree from the records returned, the problem comes when I don't find records.

e.g. Server filter by form and set the criteria to Find no Records and return a blank form.

Now set Server filter by form again and modify the criteria so that the form finds records, now switch to the tab with the treevew control and all I see is the flags and check boxes.

Any help would be appreciated



Comment by: Ben (17-10-2013 14:11:11) deeplink to this comment

Ray -

It sounds like the treeview was not completely torn down and rebuilt.

Have you looked at the demo and observed how it tears down and rebuild when changing between different demo?

The other possibility is that you need to ensure that events responsible for doing this is actually firing.

If you are still not sure how to fix this or need further help, consider contacting

Best of luck!

Comment by: Ray Longmoor (17-10-2013 21:24:35) deeplink to this comment

Ben thanks for the reply

I have taken a look at your response but I am still not able to get the treeview to repopulate after no records have been found, the error I receive is
-2147221494 Refresh: 'TreeControl' frame is not referenced

For the life of me I can no see what is wrong

Any help would be appreciated


Regards Ray

Comment by: Paulo (6-11-2013 12:44:25) deeplink to this comment


I am very grateful for you have made available this control. He is excellent and will help me a lot!

I'm getting a compile error with the message: "Compile error. Constant expression required"..

The build is broken, but when running the form I have no problem.

The error occurs on line #438 of module clsNode. The statement is:

If ndOrder <> ndAscending ndOrder = -1 Then 'descending

I'm using build 025, MS Excel 2010 32-bit on Windows 7 Pro 64-bit.

Many thanks and please excuse me for my bad english.

Comment by: Jan Karel Pieterse (6-11-2013 13:22:04) deeplink to this comment

Hi Paulo,

Odd error. I doubt this is caused by the treeview itself.

Look in your VBAProject's Tools, References, are any marked as missing?

Comment by: Paulo (6-11-2013 15:30:53) deeplink to this comment

Hi Jan,

No reference missing.

This error occurs in the Sort function. I'm not using this function, but I would not remove it from the module clsNode, what I did was add the parameter "Optional ByVal ndAscending = 1" in this way:

Public Function Sort (Optional ByVal ndOrder The ndSortOrder ndAscending =, _
                     Optional ByVal ndCompare The ndCompareMethod ndTextCompare =, _
                     Optional ByVal ndAscending = 1) As Boolean

And now everything is good. :)

Many thanks!

Comment by: Jan Karel Pieterse (6-11-2013 17:44:36) deeplink to this comment

Hi Paulo,

Odd, because that ndAscending constant (in fact an enum) should be declared at the top of the class (it is in the Excel download as far as I can see):

Public Enum ndSortOrder
    ndAscending = 1
    ndDescending = 2
End Enum

Comment by: Paulo (6-11-2013 18:15:58) deeplink to this comment

Yes Jan,

And in my code is declared. But did some testing and I think I found the answer: initially this form I'm working on had the standard TreeView, I removed this and created a new object based on your TreeView, but I had not removed the reference (Microsoft Windows Common Controls 6.0 (SP6)).

I undid my change in Sort function, removed the reference, compiled again and received no error message.

Insistently, I enabled again the reference, compiled again ... but this time did not get any error message!

Finally, have disabled the reference. :)

I can only believe it was a conflict with reference to the MS TreeView.

Again, thank you for your help, patience and excellent resource available to us.

Paulo S. Quateli
São Paulo - BR

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

hi Paulo,

Excellent glad you could solve the issue.

Comment by: Sijpie (7-11-2013 21:22:58) deeplink to this comment

Jan Karel,
My form containing the treeView has other controls (text & list box) where the user can update details of items shown in the tree. After the update I want to activate the node that has been updated, so I use

Set mcTree.ActiveNode cMyNode

This puts a thin grey border around the node, but not the full select colour. Somewhere I recall to have seen that the full colour only appears if the container control for the tree has focus, but I cvan't find that comment anymore. But if i add

that doesn't make any difference.

By the way I like the results of the treeview, looks great. Once I have finished this part of the project some beer money will be winging its way...

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

Hi Sijpie,

The correct syntax for setting the activenode is:

Set mcTree.ActiveNode = cMyNode '(you omitted the equal sign)

In my implementation it suffices to use setfocus to achieve just what you describe, so I wonder whether something isn't quite right in yours?

Comment by: Sijpie (11-11-2013 09:38:58) deeplink to this comment

Thank Jan Karel,
I had confused myself here: the changes are made by the user entering data or clicking buttons in another part of the form, and so the other textbox has the focus, and I end up with a grey border as is correct. so yes the functions work as they should, just my brain doesn't...

cheers, Jaap

Comment by: Jeremiah (13-11-2013 17:08:18) deeplink to this comment

Ok, maybe I missed it in the coding. Is there a way to make the nodes click to open a form. Like Parent Node steps into a child node. The child node has 7 forms referenced. You click that and it would open the form. I know there is a way to build a dblclick function in tree view, but cannot find any goof references.

Comment by: Jan Karel Pieterse (14-11-2013 06:41:31) deeplink to this comment

Hi Jeremiah,

If you go into the clsNode class you can see how we implemented the click event. Specifically, the routine in question contains a call to a method in clsTree:

moTree.NodeClick Control, Me

In clsTree this method is structured like this:
Friend Sub NodeClick(ByRef oCtl As MSForms.Control, ByRef cNode As clsNode)

Within that method we call:

RaiseEvent Click(cNode)

Which has been declared at the top of that class:

Event Click(cNode As clsNode)     'Node clcick event

After adding your own new event with these steps you can then find your new event in the form's event dropdown by first selecting mcTree in the left dropdown at the top of your code window and then use the right-hand dropdown.

Comment by: Lord Vorp (28-11-2013 04:04:47) deeplink to this comment

I'm working with your TreeView to replace mscomctl.ocx (w0000t!!). The existing mscomctl code accepts a double-click on the root node caption/label, to expand/collapse the node (I'd like to duplicate this if I could, but it's not that big of a deal since the arrows work, too!). But it ALSO intercepts a double-click on a non-root node to equate to "choose THIS one, click SELECT and close the form"

Unfortunately, I'm having a devil of a time capturing the DblClick() event on the form.

Looking into the code for clsNode, I found mctlControl_DblClick() has behavior that IF EnableLabelEdit is true then switch into edit mode.

My form does NOT allow Editing the nodes, and this seems to be the correct location to intercept or, preferably, raise the event to the frame/form, but my knowledge of VBA events is insufficient, so far, to implement this.

If I have the correct event handler, how should I modify mctlControl_DblClick() to raise DblClick to the frame or form? and if it's the wrong spot, where should I go?

Thanks in advance!

Comment by: Jan Karel Pieterse (28-11-2013 07:41:48) deeplink to this comment

Hi Lord Vop,

OK, here are the steps.

1. In clsNode, replace the existing mctlControl_DblClick with:

Private Sub mctlControl_DblClick(ByVal Cancel As MSForms.ReturnBoolean)
    Dim bCancel As Boolean
    bCancel = moTree.RaiseDoubleClick(Me)
    Cancel = bCancel
End Sub

in clsTreeView, at the top, add:

Event DblClick(cNode As clsNode, Cancel As Boolean)

And somewhere between the other routines, add:

Friend Function RaiseDoubleClick(cNode As clsNode) As Boolean
    Dim Cancel As Boolean
    RaiseEvent DblClick(cNode, Cancel)
    RaiseDoubleClick = Cancel
End Function

Now you can add the dblclick event to your userform:

Private Sub mcTree_DblClick(cNode As clsNode, Cancel As Boolean)
    MsgBox cNode.Caption
End Sub

Comment by: Maik (28-11-2013 15:08:47) deeplink to this comment


I have two questions.
Is it possible to create the treeview without root node?

What is the best way to add a new record to the treeview and show the new node highlited?
There are two scenarios:
1) New node level 2 of en existing node level 1
2) New node level 2 with a new node level 1

With MSCOMCTL.OCX I delete the treeview and build it again.
I could do this also with your treeview, but how could I find the last record? On form IDs for level 1 and level 2 are available.

Kind regards

Comment by: Jan Karel Pieterse (28-11-2013 15:43:22) deeplink to this comment

Hi Maik,

If you look at the project in the Exceldownload, the demo form has an AddChild button which adds a childnode to the currently selected node in the tree.
I expect that should give you enough information to work out to do what you asked.

Comment by: Lord Vorp (3-12-2013 03:50:25) deeplink to this comment

That was just what I needed to proceed! Now I have a handler for mcTree_DblClick that fires on double-click.

However, I updated mctlControl_DblClick() in clsNode.cls to preserves the existing behavior

if edit is enabled
start editmode
if the node has children
    toggle expanded
    raise the event to the userform.
end if
end if

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 there are child nodes, toggle expanded state
        If Not mcolChildNodes Is Nothing Then
            If mcolChildNodes.Count > 0 Then _

            'Raise DblClick() to the form, and let the user decide!
            Cancel = moTree.RaiseDoubleClick(Me)

        End If

    End If

End Sub

Comment by: robert (28-12-2013 15:27:22) deeplink to this comment

Thank you very much for implementing and providing this helpful replacement of the treeview.

Here are my experiences / modifications:
- clsNode.Tag: modified setter / getter to take values of type object too (needed to store own node informations)
- propagation of the MouseDown event of clsNode to clsTreeview and fireing the new created event MouseDown at the clsTreeview (needed for usage of context menus)

Comment by: Abdul Rauf Alipuddin (31-12-2013 02:44:43) deeplink to this comment


I have downloaded your treeview demo and try it as part of my application. It is son very good tool! congratulation!. However, when I edit label beyond the frame - the text is not visible until I hit the enter and scroll right. Is it possible, while I am typing the text, when I reach the right border of the frame - it will be automatically go to the next line, not the next sibling or node.

Please advise.


Comment by: Jan Karel Pieterse (2-1-2014 06:46:53) deeplink to this comment

Hi Abdul,

Thanks for your feedback, we'll add it to the list!

Comment by: Jan Karel Pieterse (2-1-2014 10:45:57) deeplink to this comment

Build 026 contains the changes needed to allow editing of long texts while the textbox remains in view.

Comment by: Tony Matyas (10-1-2014 20:01:09) deeplink to this comment

Thank you for the brilliant piece of work!
My question: I would like to know how to show the UserForm in a non modal way (vbModeless), in order to allow editing or movements in the Excel sheets application at the same time as the active UserForm is shown.

Comment by: Jan Karel Pieterse (10-1-2014 20:14:02) deeplink to this comment

Hi Tony,

Since you already seem to know what argument to use (vbModeless) I am wondering what your problem is exactly?

This shows a userform modeless:

Dim frmForm As Userform1
Set frmForm = New Userform1
frmForm.Show vbModeless

Comment by: Drew Perkins (16-1-2014 07:35:54) deeplink to this comment

Excellent work! and thank you!

It would be nice to add a saving feature that would append a new tree to a table.

Comment by: Jan Karel Pieterse (16-1-2014 09:05:42) deeplink to this comment

Hi Drew,

Thanks for the suggestion.

I do think however that that does go a bit beyond what our aim was: replacing the common controls treeview with an all-VBA one. The common controls treeview has no such option.

Comment by: Geoffrey Grinton (22-1-2014 05:07:16) deeplink to this comment

Thanks for your work on this. It looks like a good way to go. However, a key use of the Treeview control in my applications relies on the use of drag-and-drop functionality. From my initial testing this does not appear to be implemented. Is that the case, or am I missing something?

Comment by: Jan Karel Pieterse (22-1-2014 08:41:40) deeplink to this comment

Hi Geoffrey,

You're right, drag and drop isn't in the demo. However, Peter Thornton has been working on an implementation of D&D.

I'll forward your question to him.

Comment by: Jarn (11-2-2014 18:42:55) deeplink to this comment

Really is great work! Works just as described. Haven't found any bugs. Still working through the features (Thank you!)

Comment by: Jan Karel Pieterse (12-2-2014 10:51:03) deeplink to this comment

Hi Jarn,

Thank you!

Comment by: Diego F. Pereira-Perdomo (4-3-2014 08:23:39) deeplink to this comment

This was something desperately needed for Access.
I'm just starting to implement it but I can say it's a beautiful work.
Many many thanks.

Comment by: Jan Karel Pieterse (4-3-2014 12:41:38) deeplink to this comment

Hi Diego,

You're welcome!
If your solution is ready, perhaps you can send us a screenshot showing your treeview implementation? So we can add it to the page with examples:

Comment by: Diego F. Pereira-Perdomo (5-3-2014 03:44:10) deeplink to this comment

Thank you Jan!
I sent an example and a screenshot to your email.

Comment by: Jan Karel Pieterse (5-3-2014 10:39:01) deeplink to this comment

Hi Diego,

Thanks. The sample has been posted.

Comment by: Diego F. Pereira-Perdomo (15-3-2014 16:10:20) deeplink to this comment

Hi Jan,

I'm using MsAccess 2013 x64.

Everything was working properly but now when trying to load the subTreeView form I receive an error:

Run-time error '-2147221005(800401f3)': Invalid class string
If I debug the code this line is highlighted

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

The reason I'm opening directly the subTreeView form is for isolating the error, since in the form I use the treeview other errors raised and could be missleading.

I tried removing the subTreeView and the ufTreeView but the error persists.

What can be happening and how can I solve it?



Comment by: Mark Drayton (12-5-2014 14:31:28) deeplink to this comment


I have to say this has been extremely useful - thanks for the effort!

I have a question about how to dynamically create the tree view from a worksheet containing parent-child format, for example: Col A - Parent | Col B - Child?

If I figure it out, i'll post up and share.


Comment by: Jan Karel Pieterse (12-5-2014 17:34:02) deeplink to this comment

Hi Mark,

It should not be very hard, something like:

Dim oCell As Range
Dim cNode As clsNode
Dim cChildNode As clsNode
Dim vPrev As Variant
vPrev = Range("A1").Value
With mcTree
    Set cRoot = .AddRoot("Root", "Root Node", "", "")
    For Each oCell In Range("A1:A100")
        If vPrev <> oCell.Value Then
            Set cChildNode = cNode.AddChild(oCell.Value, oCell.Value, "")
            Set cNode = cRoot.AddChild(oCell.Value, oCell.Value, "")
        End If
End With

Comment by: Mark Drayton (12-5-2014 22:30:19) deeplink to this comment

Hi Jan,

Thanks for you speedy response, the table I'm trying to convert is in this format (split by a pipe):

I'm struggling with the logic of how to capture the parent (sorry I'm new to this sort of thing in relation to treeviews).


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

Hi Mark,

This seems to do the trick:

Private Sub CommandButton1_Click()
    Dim cNode As clsNode
    Dim cParent As clsNode
    Dim cRoot As clsNode
    Dim cChild As clsNode
    Dim vData As Variant
    Dim lCt As Long
    Dim bFoundParent As Boolean
    vData = ActiveSheet.UsedRange.Value
    Set mcTree = New clsTreeView
    With mcTree
        Set .TreeControl = Me.frTreeControl
        Call .NodesClear
        .AppName = Me.AppName
        Set cRoot = .AddRoot("Root", "Root")
        For lCt = 2 To UBound(vData, 1)
            bFoundParent = False
            'Check if parent node exists
            For Each cNode In .Nodes
                If cNode.Key = CStr(vData(lCt, 1)) Then
                    bFoundParent = True
                    Set cParent = cNode
                    Exit For
                End If
            If bFoundParent Then
                Set cChild = cParent.AddChild(CStr(vData(lCt, 2)), CStr(vData(lCt, 2)))
                Set cNode = .RootNodes(1).AddChild(CStr(vData(lCt, 1)), CStr(vData(lCt, 1)))
                Set cNode = cNode.AddChild(CStr(vData(lCt, 2)), CStr(vData(lCt, 2)))
            End If
    End With
End Sub

Comment by: Mark Drayton (13-5-2014 11:18:11) deeplink to this comment

Awesome - Thank you very much!

Comment by: Jesus (17-5-2014 05:02:07) deeplink to this comment

Hello Thank you very much for your effort and to share.
As many i'm new using VBA. I'm try to follow your instructions to match my work with yours, but I don't be capable to follow you.
would you please explain little more for my.
Thank you again.

Comment by: Jan Karel Pieterse (18-5-2014 20:52:31) deeplink to this comment

Hi Jesus,

There is a lot of explanation in the example file.

Note that this is not something I would recommend for a beginner to start with :-)

Comment by: Navdeep (18-6-2014 15:53:00) deeplink to this comment

Hi Mark,

Can you please share the sample excel for above code as i am trying to do this its not working


Comment by: Yves (4-7-2014 13:43:10) deeplink to this comment

First of all, congratulations for this wonderful code to replace MS lacks on 64bits.

Do you have a way to easily display 1 node with all parents expanded (like 'ensurevisible' in MS treeview)?

This is great when initializing 1 tree.



Comment by: Jan Karel Pieterse (4-7-2014 16:09:07) deeplink to this comment

Hi Yves,


The cNode.Expanded property of a clsNode class instance (a node) determines -well, that it gets expanded if it has childnodes.

The cTree.ExpandNode method (of the clsTreeView class) expands a node when it is already created.

Comment by: Yves (4-7-2014 16:27:05) deeplink to this comment

Thanks Jan,
I saw this but I am wondering if possibility exist to have tree not expanded and to expand all parents of one node so he is visible.

-> If you load tree with 222 as default value, have the tree closed but 222 visible and selected (& centered with this value will be a must) as below:

100 (Having childs)
! 210 (Having childs)
! 220
! ! 221
! ! 222 <- selected
! ! 223
! 230 (Having childs)
! ...
300 ...

This is what EnsureVisible does in MS object...

Comment by: Jan Karel Pieterse (4-7-2014 17:17:37) deeplink to this comment

Hi Yves,

I expect that should happen automatically if you set the ActiveNode porerty to the node you want to have visible.

So lets assume you use similar variable names to the Example:

'Code here that sets cNode
'mcTree holds the instance of the tree control
Set mcTree.ActiveNode = cNode

Comment by: Yves (4-7-2014 20:23:06) deeplink to this comment

This is working!

Thanks for your help.


Comment by: Sujoy (15-7-2014 05:35:54) deeplink to this comment

Congrats !!! It is a good tool.

The properties of node, such as bold, italic... are not available.

I had to create properties for each of them...

Kindly make the changes to enable those properties of node element.


Comment by: Jan Karel Pieterse (16-7-2014 16:31:35) deeplink to this comment

Hi Sujoy,

Why don't you email your current version so we can include this?

Comment by: Abdul Rauf Alipuddin (22-9-2014 04:31:44) deeplink to this comment

Hi Jan,

For you infor, my last comment was "Comment by: Abdul Rauf Alipuddin (12/31/2013 2:44:43 AM)". Your kindly response to my requests have inspired me to continue and expand my project.

Now I would like to request your help on my additional requirment (addition or modified codes) as follow.

For example, lets refer to your code "Set cNode = cNode.AddChild("2.2.1", "2.2.1 level 3", "OpenBook")"

I believe the node caption "2.2.1 level 3" and Icon of "OpenBook" can be string varibles. So Is it possible to change the Icon if its node caption or its parent node caption (perhaps get from a cell) changes. If Yes, please advise the right additional or modificationcode.

Abdul Rauf Alipuddin

Comment by: Jan Karel Pieterse (22-9-2014 13:40:16) deeplink to this comment

Hi Abdul,

Currently, updating a node's image is not supported easily. You can change the ImageMain property of a node, but there is no code that actually changes the image accordingly.

If you urgently need this, let me know and I can quote a price for adding this for you.

Comment by: Kurt Bergman (6-11-2014 21:02:36) deeplink to this comment

Excellent Design - Thank you!

I have a lot of data to display. How do I collapse all nodes once the tree is displayed.

Comment by: Sen (7-11-2014 22:42:51) deeplink to this comment

Hi. I am working on your code to see if applicable for my situation. Thanks for the nice work. It is easy to move around the tree.

When the text inside a node is too long, is there an easy way to "wordwrap"? Where to look at clsNode and clsTreeView classes?

Thank you.

Comment by: Jan Karel Pieterse (10-11-2014 11:30:43) deeplink to this comment

Hi Sen,

Well, you could change the associated property of the label, but I fear you would have to update quite some code in the treeview because the positioning of other node controls will be affected if a node needs multiple lines.
Since the control is written entirely in VBA this is certainly possible, but a bit of a hassle.

Comment by: Jan Karel Pieterse (10-11-2014 11:33:37) deeplink to this comment

Hi Kurt,

One way is to set the Expanded property of the nodes to False when you define them.

Comment by: H Rathod (11-12-2014 20:32:34) deeplink to this comment

How to Edit a Node in a VBA MS Access TreeView ?

I have added all nodes and root to a treeview, but, now, I want to edit the 9th Node content in the treeview, without a need to repaint entire treeview.

Can this be done in MS Access VBA Coding ? How ? Please guide.


Comment by: Peter Thornton (18-12-2014 13:02:52) deeplink to this comment

Hello H Rathod,
Sorry for late reply, only just noticed your question.
You can add additional nodes to a currently loaded treeview with .AddChild or .NodeAdd methods. See the code behind the buttons on the demo form "Add Sibling" and "Add Child"

Comment by: Roy (28-12-2014 23:56:45) deeplink to this comment

Hi, I'm using your treeview to develop a graphical way of creating SAP org object and relationship files and it's working great.
But some companies can have people that work for 2 or more bosses (i.e. SAP matrix organisations).
So my question is, Is it possible to assign more than one parent object to a child object using your treeview and see it graphically (with lines if possible)? I know I'm asking a lot:)

Happy new year,

Comment by: Jan Karel Pieterse (29-12-2014 11:48:56) deeplink to this comment

Hi Roy,

That is something that I have given some thought, but it is outside the scope of the treeview I'm afraid.

An n to n relationship diagram is quite complicated to draw as the lines may go in any direction, which would (theoretically) become difficult to read very quickly.

Comment by: pradeep (10-1-2015 13:10:26) deeplink to this comment

currently there is option to dump the data.
Is it possible to create tree structure for the dump data or data which is in similar format ??

Comment by: Alexander Bollmann (11-1-2015 20:32:15) deeplink to this comment

Hi, i used the activex treeview to build a navigation. We got issues with the activex-version and now I try to get your version to work.

I added everything of the demo in my program and replaced the activex-treeview in my form.

The only Problem is, the nodeclick event does not start on my form. On the demoform it works fine, but on my form it just does nothing. (I added the code to the form with "This code fires up when node clicked").

Have you any idea why the events don't start on my form? Did I forget something?

Thanks for the grat code and your help!

Comment by: Jan Karel Pieterse (12-1-2015 11:02:58) deeplink to this comment

Hi Alex,

Have you declared an object variable with the word "WithEvents" at the top of the userform module:

[code]Private WithEvents mcTree As clsTreeView[/code]

Comment by: Jan Karel Pieterse (12-1-2015 11:05:33) deeplink to this comment

Hi pradeep,

I am not sure what you mean. The dump data option already mimicks the tree structure by shifting a column to the right for each child?

Comment by: Alexander Bollmann (13-1-2015 14:52:31) deeplink to this comment

Yes, I did declare the object variable. The Problem was that I outsourced the part of the treeview initialisation in an extra module. Now I wrote that part directly in the code of the form, and now it works. Thanks for your quick reply!

Comment by: Jan Karel Pieterse (13-1-2015 16:12:25) deeplink to this comment

Hi Alexander,

Great. It is better to have the tree code in the form itself rather than on another module (unless you plan to use class modules to handle multiple tree controls of course).

Comment by: Andriy (19-2-2015 08:05:00) deeplink to this comment

Hi, I started adoption of your Treeview on one of my projects. All is going well, however I am stuck with icons/images collection.
Is there a way to dynamically read files similar to example below:


ImageList1.ListImages.Add , "TestPictureName", LoadPicture("C:\Text.bmp")


Comment by: Jan Karel Pieterse (19-2-2015 09:09:18) deeplink to this comment

Hi Andriy,

Sure you can do that.

Along these lines (somewhere during initialisation of the form, assuming you have a frame called frmImageBox which holds the images:

    Dim oCtl As msforms.Image
    Set oCtl = Me.frmImageBox.Controls.Add("Forms.Image.1")
    oCtl.Name = "imgTest"
    oCtl.Picture = LoadPicture("c:\text.bmp")

Comment by: Andriy (19-2-2015 11:03:23) deeplink to this comment

Thanks for your prompt reply.

Are there any picture type/size limitations?
I use 16x16 bmp files. After form initiation I am trying to create a node with new pic e.g.

Set RootNod = mcTree.AddRoot("_Global", "Global", "Global")

but an error is returned (Error in BuildRoot: Invalid procedure call or argument).
Note, there is Global.bmp file that was dynamically added as discussed above. If I use one one of the embedded pictures such as "Scroll" then there are no errors.
What do I do wrong?

Comment by: Andriy (19-2-2015 11:40:23) deeplink to this comment

Hi Jan,

Please ignore my previous email. All works. Perfect.

Thanks again.

Comment by: Jan Karel Pieterse (19-2-2015 13:18:53) deeplink to this comment

Hi Andriy,

There are limitations when your treeview must work on a Mac. Don't have the needed detail at hand however.

Comment by: Peter Thornton (24-2-2015 16:58:02) deeplink to this comment

Not sure how you got things working but you can load icons in two ways, by passing a Frame containing Image controls or passing a Collection of images (eg populated with LoadPicture, created with APIs or any method to add StdPicture handles)

Dim col as Collection
Set col = New Collection
col.Add LoadPicture("C\Text.bmp"), "myIcon1"
' add more images
Set mcTree.Images = col

Use the unique keys such as "myIcon1" to refer to icons when adding nodes

Comment by: Johann Stroh (6-3-2015 01:11:49) deeplink to this comment


Does anyone have any examples of how to remove a node from the treeview?

I also am looking for some examples on moving nodes

Any help/ideas/suggestions would be greatly appreciated

Comment by: Jan Karel Pieterse (6-3-2015 11:59:46) deeplink to this comment

Hi Johann,

For removal of nodes we provide the following method in the clsTreeView class:

Public Sub NodeRemove(cNode As clsNode)
' PT Remove a Node, its children and grandchildrem
'    remove all associated controls and tear down class objects
'    Call Refresh() when done removing nodes

Comment by: Peter Thornton (6-3-2015 12:30:56) deeplink to this comment

Hi Johann.
All the examples you ask for are included in the demo. Select a node and press Del to delete a node (and it's child nodes). Select a node, press Ctrl-X or Ctrl-Y and note the highlight colour change to beige (move) or green (copy), select the destination node and press Ctrl-V.

Apart from the example code in the demo see the following methods in the documentation (in the Excel file), NodeRemove, Move and Copy in the clsTreeView sheet. You can implement these methods as you wish, eg from a right click context menu.

Comment by: Janin Lord (16-4-2015 00:36:09) deeplink to this comment


Question though : how come textboxes, option buttons and listboxes doesn't work (as if they were disabled) ? Looks like they're out of focus. Thought ALL buttons works as in your demo form... My form is no use without all of them... Please !!!

Comment by: Jan Karel Pieterse (16-4-2015 07:42:57) deeplink to this comment

Hi Janin,

You're welcome.

I don't know why some things aren't working on your implementation.

Perhaps you might try to contact Ben Clothier as he did the work on the Access changes. A link to his LinkedIn profile is on this page under Acknowledgements:

Comment by: Peter Thornton (16-4-2015 18:30:03) deeplink to this comment

Hi Janin,
Long shot, I wonder if what you describeD might be related to the Dec-2014 update that caused problems with MSForms controls. The fix that normally works is to delete or if you prefer rename all *.exd files in various system folders, they will get re-created as needed.

Comment by: Keven Kemege (8-5-2015 21:59:51) deeplink to this comment

is Me.mcTree.Nodes the same as TreeNodeCollection?

should "t" be "clsTreeview"?

Call WordRecursion(Me.mcTree.Nodes, oWord)
    End Sub
' Public Sub WordRecursion(ByVal t As TreeNodeCollection, ByVal oWord As Object)

Public Sub WordRecursion(ByVal t As clsTreeview, ByVal oWord As Object)

Comment by: Peter Thornton (9-5-2015 18:02:40) deeplink to this comment

Hi Kevin,
Your question is very cryptic. In a light search "TreeNodeCollection" appears to be a collection of nodes in various types of Treeviews, though not the MSComCtl.ocx version which our treeview replaces for VBA. Indeed clsTreeview.Nodes does return the nodes collection though do not assume the respective methods are directly comparable with other versions. Note also clsNode.ChildNodes which typically may be useful in recursive functions

There are recursive examples in the Excel and Word demos, look behind the "Dump Data" button

Comment by: Yves (11-5-2015 13:34:15) deeplink to this comment

Hi !
Thx a lot for that marvellous tool.
However one silly question : I dont want the form to strech each time the form resizes ... it goes to weird results in my own tests !! Can you help me through the class ?
Thx !

Comment by: Peter Thornton (12-5-2015 14:17:59) deeplink to this comment

Hi Yves,
The demo forms shouldn't stretch, nothing the treeview does should change any dimensions except perhaps the internal scroll dimensions of its container frame. Which form are you talking about?

Comment by: Geoff Harper (17-5-2015 13:07:48) deeplink to this comment

What a fantastic tool!

I am able to do everything I want, except for one ankle-biter. I have followed instructions but I cannot get rid of the border around the TreeView container. I must be doing something wrong, but I don't know what.


Comment by: Jan Karel Pieterse (18-5-2015 06:20:10) deeplink to this comment

Hi Jeff,

That is a property of the parent frame. For me it (rather odd) works if I change the borderstyle to 1 and then back to 0.

Comment by: Aleem (28-5-2015 09:30:05) deeplink to this comment


Thank you so much, this is just so wonderful, i would like to save the text once i update example if i use this sample to enter the org strcuture and to keep it save, how i will save these text.


Comment by: Jan Karel Pieterse (28-5-2015 14:58:18) deeplink to this comment

Hi Aleem,

The sample workbook has a button to dump the current tree content to a worksheet if I recall correctly, so you should be able to use that and modify it.

Comment by: Brian (22-9-2015 22:51:40) deeplink to this comment

How do I initialize the tree with all nodes collapsed? I am using access 2013 and am populating the tree from a table. It all works great except the tree appears with all nodes expanded. I notice your demo behaves the same way.

I tried to useing tv.ExpandToLevel 0, but got some strange behavior. Do I have to iterate through all nodes and set expanded property to false?

Thanks for a great control.

Comment by: Jan Karel Pieterse (23-9-2015 17:49:36) deeplink to this comment

Hi Brian,

You should be able to set the expanded property when you add the nodes to the tree.

Comment by: Luis G (26-10-2015 21:58:56) deeplink to this comment

Great job.Thanks for sharing.

Any way to include multiple columns?

Comment by: Peter Thornton (28-10-2015 10:07:52) deeplink to this comment

Hello Luis

What do you mean by "multiple columns" ?

Comment by: Alan WIlson (10-12-2015 22:03:29) deeplink to this comment

Very nice piece of work. It helps me a lot and is the core display for a fairly complex data set.

I'm running the Access version and loading a recursive data structure to the tree. It works very well after I added a couple of fields to the nodes to handle linking the table record to display.

Question - I'm working with the copy node function - I let your code add the tree node and then I add the linked table record. But I need to find the node the tree added so I can update the linked fields I've added to the tree. I don't see a find type of operation so I'm assuming I need to write one. In general terms, what would you suggest that would be a consistent approach for stepping through the tree keys?


Comment by: Peter Thornton (11-12-2015 16:12:42) deeplink to this comment

Hi Alan,
If you don't specify Before or After arguments in the Copy function (see the documentation), your newly copied node will be the last node in destination's childnodes collection, eg

mcTree.Copy cSource, cDest
Set cCopiedNode = cDest.ChildNodes(cDest.ChildNodes.Count)

If using Before/After (only if you know the given before/after exists) replace the .count with index = Before or index = After+1

If calling the Sort function set a ref to the copied node first.

Comment by: Luke M (18-12-2015 20:27:03) deeplink to this comment

Thanks for making this available. It is very impressive.

Like Luis, I'm also interested in a multi-columned tree. It would work just like the Locals/Watches windows in the VBA Editor. Here is a screenshot that should help describe what I'm after: Is this possible?

Comment by: Peter Thornton (21-12-2015 12:20:17) deeplink to this comment

Hi Luke,
The simplest approach would be to add a Listbox for the columns. Trap the treeview's node-expand event (not included but easy to add) and update the multi-column list to line up with nodes as expanded. Tip, set a low NodeHeight and let it autosize to the minimum required to match the listbox row height, assuming similar fonts.

If you have dug inside the treeview and worked out how all the controls are added and updated, it's fairly straightforward to add additional columns similar to how the node controls are created and updated, but with their Lefts aligned in columns. Just that might be enough for your needs but for general use would need a lot more work to make it all look and handle nicely.

Comment by: Alexander Finck (7-1-2016 11:20:24) deeplink to this comment

Hi Alan,

did you already implement a "Find" function ?
I also need this function for my own, and if it already exists i don't have to invent the reel again :-)

Comment by: Peter Thornton (11-1-2016 14:26:10) deeplink to this comment

Hi Alexander,
If what I suggested as an answer for Alan, how to 'find' a newly copied node, is not what you are looking for describe what you want a "Find" function to find.

Comment by: Alexander Finck (13-1-2016 08:59:37) deeplink to this comment

Hi Peter,

i need to put some additional information inside a node, maybe using the .tag field.
And to get access to that information i need to find the node first :-)

Like in the demo: There's a node for Jennifer Aniston, but how can i access the information ? Either i have to step through all the nodes manually, or use a "find" function :-)


Comment by: Peter Thornton (13-1-2016 15:52:37) deeplink to this comment

Hi Alex,
This is very different objective to the "Find" Alan needed. Without knowing more details you will probably need to loop nodes comparing Caption or Tag properties. If there might be similar or duplicates you may need to continue searching. However if you already know the parent node you could limit to looping looking at its Childnodes collection. Get used to looping recursively to look down a branch.

I don't know if relevant for your purposes but use of Keys might help, no need to loop and search for those. However care is needed to avoid duplicate Keys.

If you want to offer a Find feature for users see the "Project Explorer" demo on the Examples page.

Comment by: Joseph Volence (21-1-2016 20:24:36) deeplink to this comment

I am getting a runtime error 2455 "you entered an expression that has an invalid reference to the property Form/Report"

on the line

Set mcTree = subTreeView.Form.pTreeview

I am using Access 2010, have the subform imported from your file, and the same name.


Comment by: Joseph Volence (22-1-2016 15:11:12) deeplink to this comment

Nevermind, it was because I was trying to load the tree before the subform was loaded, from the form.load event.

I worked around it another way.

Comment by: Ed Leibrick (27-1-2016 01:09:24) deeplink to this comment

I would also like to thank you.
Your code has simplified a tricky issue for me and has impressed all who view it.

Comment by: Jan Karel Pieterse (27-1-2016 10:29:24) deeplink to this comment

Hi Ed,

Thank you!

Comment by: Ian Wilson (1-3-2016 12:10:28) deeplink to this comment


Thank you for the fantastic code! It is helping me remove the very problematic OCX treeview my access 2010 application!

It is possible to have event handlers for each individual Treeview? I have 4 forms with Treeviews on, all working fine but each form has a specific function so I could do with writing individual onClick events, currently each one calls the TreeControl_Click routine in clsTreeview.

Thank you.

Comment by: Jan Karel Pieterse (1-3-2016 21:21:57) deeplink to this comment

Hi Ian,

If you declare a separate object variable for each tree using WithEvents you'll have each of them in the top-left dropdown to pick from so you can add their events.

Comment by: Ian Wilson (2-3-2016 08:28:58) deeplink to this comment


Thanks, all working as I need now.

Your Treeview is such a big improvement on the Common Control one, well done!

Comment by: Rob L (18-3-2016 16:37:12) deeplink to this comment

Thanks for sharing this excelent treeview control as replacement for the MSCOMCTL .
I am using it in an Access project, and it works very well !
The only thing is that the images in the frImages frame cannot be changed from within refuses to apply changes (just wanted to replace your flag images with my images, but no way). The other method you mention to make a collection needs the function GetIcons . Can I get it somewhere ?

Comment by: Jan Karel Pieterse (18-3-2016 16:48:44) deeplink to this comment

Hi Rob,

GetIcons is a function you can find in the Excel demo of the treeview. What it comes down to is that you need to pass a collection of Picture objects to the treeview (using their names as key values). Whether that collection is created by reading the picture properties from the images in a frame or by loading Picture objects from somewhere else makes no difference to the treeview.

Comment by: Jan Karel Pieterse (18-3-2016 19:25:08) deeplink to this comment

Hi Rob,

Images can't be edited in the Frame in Access for some odd reason. From memory one way is prepare the images with pictures on a Frame in Excel (or just on a userfrom), select and copy them. Then in Access put the msForms Frame in the subform into design mode (should see the Toolbox) and simply paste the image controls. Can do the lot in one go.

A different approach waiting for a rainy day is to store the Images as "blobs" (effectively the image file as a text or probably better as Hex or Base64) into a DB Table, then convert as a byte-array to an StdPicture object. A bit of work but pretty confident this would work.

Comment by: Antonio (18-4-2016 10:01:39) deeplink to this comment


I've been implementing the treeview succesfully on Access 2010 64 bits.

But I have some issues:

I need to move nodes in the same branch by VBA Code.

I use the following to move up:

Private Sub cmdTabUp_Click()
    Dim oAct As clsNode
    Set oAct = New mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub
    If Not oAct Is oAct.FirstSibling Then
        Set mcTree.MoveCopyNode(True) = oAct
        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True
    End If
End Sub

But i get an error. How can I move anode one position up or down in the branch?

Many thanks.

Regards. Antonio.

Comment by: Jan Karel Pieterse (18-4-2016 12:20:17) deeplink to this comment

Hi Antonio,

Forgive my ignorence, but shouldn't this line:

Set oAct = New mcTree.ActiveNode

be modified to:

Set oAct = mcTree.ActiveNode

Comment by: Antonio (18-4-2016 14:45:35) deeplink to this comment

Hi Jan.

Sorry, I have a mistake on the code posted.

Correct code is here.

Private Sub cmdTabUp_Click()
    Dim oAct As clsNode
    Set oAct = mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub
    If Not oAct Is oAct.FirstSibling Then
        Set mcTree.MoveCopyNode(True) = oAct
        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True
    End If
End Sub

Anyway, How can I move a node up or down in the same branch by using VBA code?

Thanks and best regards.


Comment by: Antonio (18-4-2016 19:22:00) deeplink to this comment

Hi Jan.

After modify the code, I receive the error code 9 at the Move procedure located in the clsTreeview class module.

Thanks in advance.



Comment by: Peter Thornton (18-4-2016 20:47:40) deeplink to this comment

Hi Antonio, try these

Dim cGrandParent As clsNode
Dim cFirstSibling As clsNode

    Set oAct = mcTree.ActiveNode
    If oAct Is Nothing Then Exit Sub

    Set cGrandParent = oAct.ParentNode.ParentNode
    mcTree.Move cSource:=oAct, cDest:=cGrandParent

'or down
    Set cFirstSibling = oAct.FirstSibling
    If Not oAct Is cFirstSibling Then
        mcTree.Move cSource:=oAct, cDest:=cFirstSibling
    End If

I didn't include the before or after argument as not sure what position in you want to place the moved node, as written will be placed as the last childnode.

Use of PreviousNode doesn't make sense here as that refers to a current sibling, you might want want to refer to first or last node in the what will be the new parent's collection of childnodes.

You don't need to set MoveCopyNode for these actions

Comment by: Antonio (19-4-2016 16:52:52) deeplink to this comment

Thanks Peter.

I'll try it later. The positions are one before o one after. So I used the firstSibling and lastSibling to avoid running code in that positions (up or down).

Best regards.


Comment by: Peter Thornton (20-4-2016 10:59:46) deeplink to this comment

Perhaps I misunderstood your objective, is it to move the node up/down in the the same collection its parentnode's childnodes?
If so start by getting the index of the node's index in the parent's childnodes collection, simply loop its childnodes collection until you find your node.

Check the node is not its First/LastSibling (depending on direction to move) and

mcTree.Move cSource:=oAct, cDest:=oAct.ParentNode, vBefore:=idx - 1
' or

and call .Refresh

Comment by: Antonio (20-4-2016 23:41:51) deeplink to this comment

Hi Peter.

You are right, I wanted to move the active node one position up or down in the current branch.

As you can see in my code (move up), first I check it's not the first sibling.


If Not oAct Is oAct.FirstSibling Then

And later I move the node

        mcTree.Move oAct, oAct.ParentNode, oAct.Previous.Key, bShowError:=True


But with this code I always get an error code 9.

I'll check your modification, using the index.

Thanks for all.

Best regards.


Comment by: Donald LaDue (17-5-2016 16:37:55) deeplink to this comment

Hi Team,

great work!

I have two suggestions:

1. Add an "If Len() >0" to the property below.
Reason: If you read the Images from a table and the ImageExpanded field is blank (Nullstring), you otherwise receive an error.

Public Property Let ImageExpanded(vImageExpanded)
' PT string name or numeric index for an expanded icon key
    On Error GoTo errExit
    If Not IsMissing(vImageExpanded) Then
        If Not IsEmpty(vImageExpanded) Then
            If Len(vImageExpanded) > 0 Then
                If Len(mvIconMainKey) = 0 Then
                    mvIconMainKey = vImageExpanded
                End If
                mvIconExpandedKey = vImageExpanded
                mlIconCnt = 2
            End If
        End If
    End If
End Property

2. I feel that the labels (label text) of the nodes stick too close to the Images.
It would be great to have a parameter where you can determine the gap between text and Image.
But maybe it is there already and I overlooked it?
BTW: Setting .FullWidth = True widens the gap nicely, but you probably want control over the gap independently of this setting.


Comment by: Jan Karel Pieterse (18-5-2016 06:59:15) deeplink to this comment

Hi Donald,

This constant in clsTree controls the padding of the icon:

Private Const mcIconPad As Single = 14.25

As you can see there is a whole bunch of constants there determining the looks :-)

Comment by: Donald LaDue (20-5-2016 17:45:23) deeplink to this comment

Hi Jan,

Thanks for pointing out the padding possibilities via mcIconPad.

At first I was a bit puzzled as that did not show the effect I wanted.
I found out that this is because I'm looking at the gap between .Picture and .Caption within the same(!) Label-Object.
For Illustration:

With MyLabel
.Picture = SomePicture
.Caption = SomeCaption
End With

There probably is no way of getting more room between .Picture and .Caption other than puttung a Space before the .Caption, is there?

Comment by: Peter Thornton (23-5-2016 11:19:38) deeplink to this comment

Hi Donald,
To change the space between the icon and caption change the constant mcIconPad to suit as Jan Karel suggested. First, if necessary, adapt the constant mcIconSize to the size in points of your largest icon (in "normal fonts" Windows 12 points = 16 pixels). To give a 3pt space between 12pt(16pxl) icon and caption

    Private Const mcIconSize As Long = 12
    Private Const mcIconPad As Single = mcIconSize + 3

In hindsight perhaps we should have declared these as variables and given default values in the initialize event.

A similar approach and constants are used for checkboxes.

If distributing to Mac keep in mind 1 point = 1 pixel and adapt the Mac constants similarly.

Comment by: David Reid (24-6-2016 01:39:48) deeplink to this comment

Could you please help me to understand how to set the activenode of the treeview from an unbound subform in Access:

Following is my code:

' on main form:
Public Property Get propTree() As clsTreeview
    Set propTree = mcTree
End Property

'main form: 00_frmOpenMe
'treeview on main form: mcTree
'unbound subform: ctrTree

' on unbound subform:
Private Sub Form_Click()
Dim cTree As clsTreeview
Dim cNode As clsNode
    Set cTree = Me.Parent.propTree
    If Not cTree Is Nothing Then
     With cTree
        Set cNode = cTree.Nodes(Key)
        ' both of these lines work
        ' but I still get an error 91: obj/with var not set
         Set cTree.Activenode = cNode
     End With
    End If
End Sub

'Refer to Form and Subform properties and controls

Got to be as simple mistake I'm making. Thank you for
your help.


P.S. Love your treeview control.:)

Comment by: Jan Karel Pieterse (24-6-2016 11:17:20) deeplink to this comment

Hi David,

The Nodes collection is by index, not by Key so you need to run through the nodes collection to get the right one:

    With mcTree
        For Each cNode In .Nodes
            If cNode.Key = Key Then
                Set .ActiveNode = cNode
                Exit For
            End If
    End With

Comment by: René (29-6-2016 00:58:22) deeplink to this comment

I don't have any special comment or question but I just wanted to say thank you for generosity!
Your treeview is great and way better than what Microsoft has decided to take away. I'm still a bit puzzled how they could do that!
Cheers Rene

Comment by: Jan Karel Pieterse (29-6-2016 09:31:37) deeplink to this comment

Hi Rene,

Thank you!

Comment by: tz3poo (1-7-2016 12:37:17) deeplink to this comment

I don't usually do this - but thanks... Your treeview is better then MS's and better than the one i've made myself. Having said that ( :))), how can i use the mousewheel to scroll through the nodes. As it is now, the wheel doesn't work - only on your control - it works on anyother control in ms access. Thnx again

Comment by: Jan Karel Pieterse (2-7-2016 19:36:50) deeplink to this comment

Hi tz3poo,

Thanks! For enabling wheel scroll you would need the (paid) pro version of the treeview, which is in beta. If you would like to aprticipate in the beta program, please let me know.

Comment by: Trandafir Marius (3-7-2016 10:50:00) deeplink to this comment

Hey Jan. I'd love to enter the beta program. How much would i have to pay for the pro version.
P.S. Also, if you use textboxes for the branches of the tree, why not use richtext on them - so we could use html tags for format?
Thnx a bunch - and by the by - your tree is awesome and easy to implement.

Comment by: Brandon B (7-7-2016 02:07:27) deeplink to this comment

I am having some trouble getting the expander +/- to show. I did set the mcTree.ShowExpanders = True but I am not actually seeing the expanders. Is there something else that I need to do... or is there something that could be blocking them somehow?

Below is part of the initialization sub

With mcTree
        Set .TreeControl = Me.frTreeControl
        .AppName = "test name"
        .CheckBoxes = False
        .RootButton = False
        .EnableLabelEdit = False
        .Indentation = 20 * 0.75
        .NodeHeight = 16 * 0.75
        .ShowExpanders = True
        .ShowLines = True
        Set .Images = Me.frmImageBox
        For Each animalID In ws2.Range("B2:B" & ws2.Range("B" & ws2.Rows.Count).End(xlUp).Row)

            If mcTree.RootNodes.Count = 0 Then 'initialize the tree
                Set cRoot = .AddRoot(CStr(animalID.Offset(0, 1).Value), CStr(animalID.Offset(0, 1).Value), "tenrecImg2")
                Set cNode = cRoot.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
                GoTo NextIteration
            End If
            For Each cExtraNode In .RootNodes
                If CStr(animalID.Offset(0, 1).Value) = CStr(cExtraNode.Caption) Then
                    Set cNode = cExtraNode.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
                    GoTo NextIteration
                End If
            Next cExtraNode
            Set cRoot = .AddRoot(CStr(animalID.Offset(0, 1).Value), CStr(animalID.Offset(0, 1).Value), "tenrecImg2")
            Set cNode = cRoot.AddChild(CStr(animalID.Value), CStr(animalID.Value), "greyCkImg2")
        Next animalID
End With

Comment by: Jan Karel Pieterse (7-7-2016 10:29:14) deeplink to this comment

Hi Brandon,

I don't see anything obviously wrong with your code apart from a missing Refresh statement to refresh the treeview after setting up the nodes. Best place it before the End With:

End With

Comment by: Brandon B (9-7-2016 07:29:21) deeplink to this comment

Hey Jan,
Thanks for having a look at it for me. I accidently skipped the .refresh when copy/pasting the code over and the actual issue ended up being something super simple... I had .rootbutton = false /face_to_palm.

I wanted to let you know what a life saver your tree is. I spent weeks working on a form that would streamline data collection and animal care at our lab. The Microsoft version of treeview was a central piece to the whole project... but after creating the whole thing I realized that it would not work on the lab's 64bit excel and Macs. I am baffled by Microsoft's decision to exclude such immensely useful controls from those versions of VBA. But at the same time I am a little glad that they did. Your treeview was much easier to use and more flexible!

Dim i As Integer
i = 1

Do While i <> 1
msgbox "THANK YOU!"

Comment by: Brandon B (9-7-2016 07:45:36) deeplink to this comment

GAH i ment
Do While i = 1

*feel free to just edit my previous comment...and we will pretend that this never happened haha

Comment by: Jack M (9-7-2016 13:04:56) deeplink to this comment

I'm new to Excel VBA and am working on a simplified double-entry accounts project for to help my son with his business accounts.
I saw your brilliant treeview and wanted to use it for my 'Chart of Accounts' with the level 1 nodes being account types e.g Assets/Liabilities etc and lower levels being accounts eg Bank account/Savings account etc.
It all works perfectly from nodes I build into the coding and the I have an 'Add Account' button which adds a node perfectly.
The problem I have is that I can't find what I need to do to RETAIN THE NODE ADDED VIA THE BUTTON, ie. I can Add it,expand it, close and re-expand it etc. but, when I close the InputForm, the newly added node is lost!
Thanks in anticipation.

Comment by: Peter Thornton (1-8-2016 15:41:48) deeplink to this comment

Hi Jack,
Sorry only just noticed your question on this page, if you haven't already figured it contact me off-line as I don't quite understand what you're asking.

Comment by: Karthikeyan.s (9-8-2016 13:07:21) deeplink to this comment

I cant able to print check box and tree structure once i press the Dump data,what i have to do to print it in exact tree structure as you shown in example.

Comment by: Jan Karel Pieterse (9-8-2016 13:54:39) deeplink to this comment

Hi Karthikeyan,

If you double-click the button on the dem userform, there is an "Exit Sub" command in the click event of the button. Remove that to make it work.

Comment by: BUM SUK KO (6-9-2016 14:57:13) deeplink to this comment

Hi, This is Bumsuk Ko from South Korea.

I found your treeview three days ago in a website.
I was really really astonished from your treeview result.
As you know, MS common control sometimes bothers people. So I have tried to avoid using it. At this moment, your treeview gives me a chance to use treeview in my program.
Thanks a lot.

And I also found listgrid/listview. If it is not too late, can I join as a beta tester? Or Can I purchase your listgrid/listview?

I'm so excited to wait for your response about listgrid/listview.

Best Regards,
Bumsuk Ko

Comment by: Peter Thornton (6-9-2016 21:20:39) deeplink to this comment

Hi Bumsuk Ko,

It's never too late:) Please contact us via the "Contact us" link just below the ListGrid details on the main treeview page. If you can't find it you can contact me directly, you can find my address in the headers of the treeview demo.

Comment by: Steve Wilson (30-9-2016 16:29:48) deeplink to this comment

What an awesome job you guys did with this code! I spent a couple of days perusing through it trying to figure out what methods did what, and WOW... very impressive.

I have one question though:

I have a function built to test if a node already exists in the tree, but I'm struggling a bit on determining the most resource-efficient way to go about this is. What would be your recommendation?

Comment by: Jan Karel Pieterse (30-9-2016 17:16:57) deeplink to this comment

Hi Steve,

Thanks for the compliments!

What does your current function look like?

Comment by: Steve Wilson (30-9-2016 17:55:59) deeplink to this comment

I've tried a couple of things, but nothing seems to work just yet. My application runs through all the rows in a recordset using the mcTree.NodeAdd method, which works great. However, creating a simple Boolean function to see if the node already exists has proven to be my Achilles' heel this week.

Here's what I've been trying to do:

Private Function NodeExists(intUnitID) As Boolean
    'test to see if node exists in the treeview object: mcTree
    NodeExists = True
    'test for nothing, then set to false
    If mcTree.Nodes.Item("'" & intUnitID & "'") = Nothing Then
        NodeExists = False
    End If

End Function

Comment by: Jan Karel Pieterse (30-9-2016 19:04:13) deeplink to this comment

Hi Steve,

I'd do something like:

Function NodeExists(cTree As clsTreeView, sKey As String) As Boolean
    Dim cNode As clsNode
    For Each cNode In cTree.Nodes
        If cNode.Key = sKey Then
            NodeExists = True
            Exit Function
        End If
End Function

Comment by: Mike (26-10-2016 20:52:29) deeplink to this comment

I get "Compile error in clsTreeView" when using this in Mac Excel version 15.28 (161019).. This was working fine on older Mac Excel and on PC Excels... Any ideas what's up with the newer max Excel?

Any fix available?

Comment by: Peter Thornton (3-11-2016 11:57:55) deeplink to this comment

Mike, could you contact me off-line and tell me exactly where it fails to compile in your Mac. Get my address from the headers.

Comment by: John Bingham (10-1-2017 03:48:25) deeplink to this comment


We've incorporated the tree into our product in order to escape from mscomctl. Really what you've done here is absolutely incredible and fantastic.

Our application incorporates a lot of subforms and tabcontrols, and either may be nested inside the other.

When any form containing an mcTree is viewed in design view, the mcTree subform insists on always appearing on top.

For example, a form FF has a tab control TC1. On TC1's second tab, there is a subform SF, containing another tab control, TC2 (TC2 is in a subform to overcome MS Access's inability to render nested tab controls correctly). TC2 contains an mcTree. When I view the form FF in design, I see TC1; By default I do not see SF, because SF is on a tab of TC1 which isn't the default tab - yet I do see the mcTree subform contained in SF.

This behaviour can be quite frustrating, if you need to be able to see the controls existing in the same part of the screen as where the mcTree control is. Can this behaviour be overcome?

I don't know for certain what code is causing the behaviour in the first place, and I'm reluctant to just wade in and make lots of changes to try and fix it, given what I don't know.

Can I get any guidance, re what logic causes this behaviour and if applicable what issues are involved, which this behaviour was or is intended to solve?

Many thanks

Comment by: Peter Thornton (10-1-2017 16:55:51) deeplink to this comment

Hi John,

It sounds like you've got a complex arrangement of tabs within subforms within tabs? If I follow the problem is only in design, yet you say "not sure what code is causing the behaviour".

If you could you send me a non-sensitive file that shows the problem I'd be interested to have a look. My address is in the headers.

Comment by: Bolat (17-1-2017 05:47:55) deeplink to this comment

It really great job you have done. Many tahnks.
Just wondering is it possible to show treeview in "read only" option.
i mean , just to prepare treeview as i need, show it to a user, and not to allow user to change/click checkboxes.
I could not find any properties of checkboxes like "locked" or something similar.
Is it possible to solve this question ?

Comment by: Jan Karel Pieterse (17-1-2017 08:50:00) deeplink to this comment

Hi Bolat,

If you add this little line of code to the demofile in the routine called "InitializeDemo1", just below "With mcTree":

.TreeControl.Enabled = False

then you effectively disable clicking the tree.

Comment by: Bolat (18-1-2017 05:29:11) deeplink to this comment

Hi, Jan.
Thank you for prompt reply.
But if i add this line [.TreeControl.Enabled = False] - it will disable the whole tree, and i can not expand/collapse, scroll, etc. If "tree" is too long, it is not possible to see all nodes, especially childnodes.
What should i do to allow user to expand/collapse, scroll, but to prohibit change checkbox status ?
Thank you &
Best reagards,

Comment by: Jan Karel Pieterse (18-1-2017 08:24:31) deeplink to this comment

Hi Bolat,

CLicks on the checkbox are handled in the clsNode class. You could simply comment this routine:

Private Sub mctlCheckBox_Click()
'Other code here
End Sub

Comment by: Bolat (18-1-2017 10:48:57) deeplink to this comment

Thank you so much for your support.
I used public variable to select wether it is allowed to edit(true) or just to view(false).
So easy. Thank you.

One question more.
1) there are some nodes in childnodes collection (for example level=2), which are not to be changed in any case. if parent node checkbox status is 0, and when i click on the checkbox to change status to -1, i do need to predict that there are few childnodes ( not all but part of them) and they should not(!) to be changed .
It means that status of childnodes boxes is going to be changed -1, exept ones i talked above. and their status should remain = 0,
So automatically status of the parent node must be changed to +1 only (!!), since not all childnodes are =-1 ( part of them should remain with status = 0).

What shall i do to get such a rezult ?
Thank you &
Best regards

Comment by: Jan Karel Pieterse (18-1-2017 18:18:25) deeplink to this comment

Hi Bolat,

I would add a property to clsNode which allows you to enable/disable its checkbox for clicking so you can turn that on or off when adding the node. You would also have to include an argument in the NodeAdd and AddRoot subs in clsTree so you can pass that setting to those methods. Then finally set the new clsNode property in NodeAdd and AddRoot according to the new parameter.

Comment by: Bolat (19-1-2017 07:53:12) deeplink to this comment

Hi, Jan.
Thank you for your message.
Frankly speaking, it's hardly understand what shall i do. Sorry for bothering you.
Do you have some similar sample ?
for exapmle tree nodes are, all their checkboxes status are 0:
1. Cars
1.1 Toyotas
1.2 KIA
1.2.1 KIA RIO
1.2.3 KIA CEED
if i click on node [1.2 KIA](!) to change status, all checkboxes are to &#1080;&#1091; changed , except [1.2.2 *KIA OPTIMA] - because first char is "*". thus status of checkboxes should be converted as
0 -> 1.1 Toyotas
1 -> 1.2 KIA
-1-> 1.2.1 KIA RIO
0 -> 1.2.2 *KIA OPTIMA
-1-> 1.2.3 KIA CEED
I would apreciate for any help/code sample.
Thank you in advance

Comment by: Peter Thornton (19-1-2017 18:23:27) deeplink to this comment

Hi Bolat,
I don't quite follow what you are trying to achieve but you can validate and check changes in the NodeCheck event and apply as you you want, for example

Private Sub mcTree_NodeCheck(cNode As clsNode)
' prevent user from checking any level-2 nodes, or any nodes with "3" in the caption
    With cNode
        If .Checked Then
            If .Level = 2 Then
                .Checked = False
            ElseIf InStr(1, .Caption, "3") Then
                .Checked = False
            End If
        End If
    End With
End Sub

Comment by: Bolat (24-1-2017 18:10:48) deeplink to this comment

Hi, Peter.
Thank you for your reply.
It's working. But there is one issue.
it's very difficult for me to explain, and I did not find any possibility to attach a picture .
Thus I loaded picture into cloud. this is the link!Ahfd8-miifhwj6UvhmDlXjOBnnMd2Q

When I click on root node , all nodes status should be changed from [0] to [-1] except ones which caption consists of "reserved"(for example - [1.2.2 (reserved) KIA OPTIMA]). Yes, status of [1.2.2] is [0], but all "below"(1.2.3, 1.3 , etc) nodes status have been changed from [0] to [1], but should be changed to [-1] .
What else code I should add to solve the problem ?
Thank you in advance.

Comment by: Peter Thornton (25-1-2017 13:18:59) deeplink to this comment

Hi Bolat,
With the triState checkboxes option, when you click a checkbox it will change to 0 or -1, then all descendant checkboxes will change to the same value, but parent checkboxes may change to the appropriate value of -1, 0, or if a parent's childnodes have a mixed combination of checkboxes it will change to +1 (grey).

If I follow you want to be able to check a parent, but maybe prevent some child checkboxes from changing to the same value. This defeats the normal way triState checkboxes work. Although possible to adapt the code to meet your objective difficult to suggest without knowing a lot more about your treeview, eg only leaf-nodes might be prevented from changing, or other constraints or requirements. Can you try the conventional way without using the triState option?

Comment by: Mike Smith (8-2-2017 20:29:52) deeplink to this comment

Does the clsTreeView works on the MAC 2016 version of Excel? Seems there might be issues with their move to 64 bits only. Please check this problem that was reported, and the reply given by the Mac MVP... Thanks!

Comment by: Peter Thornton (10-2-2017 12:44:45) deeplink to this comment

Hi Mike,

We don't have Mac 2016-64 to test, but we've made a small change with use of the #If Mac compile constant and uploaded.

Could you download again and test. If it fails, open the project, do Debug/Compile and advise where it fails to compile.

Comment by: Erlind (23-2-2017 16:20:24) deeplink to this comment

Hi there,

thank you very much for this great treeview.

I found it yesterday and today I started implementing it into my project where I am currently using MSComctlLib.

I have already modified how the tree is populated based on my tables by there is something that I am missing.
In my example I create four Roots and then add child nodes. Problem is that all the child nodes end up being on the last root that I add.
Is there a way to programmatically activate the desired RootNode prior to adding ChildNodes?

Thanks in advance

Comment by: Peter Thornton (24-2-2017 13:52:22) deeplink to this comment

Hi Erlind,
Several ways, eg to return the 3rd root

a) Give each root a Key when you create it, then later when you need it -
Set cRoot = mcTree.Nodes("myRoot3")

b) Add the 4 root nodes first and, assuming none will be deleted until you need it -
Set cRoot = mcTree.Nodes(3)

c) an array of roots
Dim cRoots() as clsNode
Redim cRoots(1 to 4)
for i = 1 to 4: Set cRoots(i) = .AddRoot(arg's)

d) get the first root's parent's 3rd childnode
Set cRoot = .Nodes(1).ParentNode.ChildNodes(3)

The root nodes' parentNode is not part of the visible tree or included in the Nodes collection

Comment by: Erlind (24-2-2017 13:59:43) deeplink to this comment

Thank you very much, Peter!

That was very helpful.


Comment by: Erlind (2-3-2017 13:48:09) deeplink to this comment

Hi there,

is there a way to raise an event when in image on the tree is clicked?
At the moment the event is raised only if the label or the expander are clicked (through mctlControl_Click() )

Thanks in advance


Comment by: Peter Thornton (3-3-2017 12:29:30) deeplink to this comment

If you want the icon to raise the same events as the label -
in the clsNode declarations, change
Private mctlIcon As MSForms.Image
Private WithEvents mctlIcon As MSForms.Image

and have it's events call equivalent label events, eg

Private Sub mctlIcon_Click()
End Sub

Normally it's best to add event stubs from the top right dropdown, first select mctlControl in the middle dropdown.

Comment by: Erlind (3-3-2017 21:40:16) deeplink to this comment

Hi there Peter,

thank you very much! Your answers are always quick and precise.

Speaking of; is there a way to scroll the tree view using the mouse scroll?

I looked inside clsTree but nothing pointed to a scroll event

Thanks in advance

Comment by: Peter Thornton (6-3-2017 12:47:13) deeplink to this comment

Scroll is not built-in to the treeview, or indeed into any MS VBA, or VB/ocx type controls. If you search the internet you might come across an approach for VBA controls developed by and originally posted by, er, me! Re-posted and adapted many times by others.

However I strongly suggest you don't try integrating this into the treeview unless you really understand how it works, and even then will take a long time to get it working reliably.

Wheel scroll is built-in to the 'pro' treeview (see link above), after a considerable amount of development time!

Comment by: Erlind (7-3-2017 11:28:26) deeplink to this comment

Thanks Peter,

I saw the posts online and integrated a version that handles the wheel scroll.
It seems stable on the 32 version but it crashes on the 64-bit version of Access. Maybe something's wrong with the APIs even though the version I am using has APIs and functions dedicated to 64-bit.

The wheel scroll inside the pro version works correctly with Access 64-bit?

How much is the license and can I include your code in a commercial application?

Thanks in advance

Comment by: Peter Thornton (11-3-2017 19:34:59) deeplink to this comment

Hi Erlind, sorry for late reply, I was away last week.

Most of the posts on-line with wheel scroll for MSForms controls in x64, are adaptations of an approach for wheel scroll I posted many years ago for x32. I haven't looked at all of them but I haven't seen any that are likely to work in x64.

The best way for you to find out if wheel scroll works in the x64 version of the 'pro' treeview is to try it!

Your last question, unlike the free the treeview the code is packaged like a 'control' in an xlam or accde type file, and yes you can re-distribute it with a license, all explained in the demo.

Comment by: Erlind (13-3-2017 08:58:14) deeplink to this comment

Thank you for your reply Peter,

I think that's the best option.

The demo will give me time to test the tree in my production environment for stability and compare it with the free version.

Can you please tell me how to get the demo?

Thanks in advance

Comment by: Peter Thornton (13-3-2017 18:55:50) deeplink to this comment

Hi Erlind,

There are links for the 'pro' Treview and ListGrid on the main Treeview page, scroll up and see the links on the left. However I've taken the liberty of contacting you directly.

Comment by: Zoltan Attila (14-3-2017 15:04:28) deeplink to this comment

Hello there,

first of all I want to thank you sharing your wunderfull treeview.
I would like to enable the doubleclick and the mouseup events but I'm not able to do it... Could you please help me?

By double click event I want to change the Icons and add some comments to the Nodes

By mouse up event I would like to implement a dynamic popup menu in the treeview depending if I right click on the root or on a node.

Thank you in advance for your help.

Comment by: Jan Karel Pieterse (14-3-2017 16:27:57) deeplink to this comment

Hi Zoltan,

Have a look at this comment, perhaps it helps?

Comment by: Roberto (30-3-2017 23:48:45) deeplink to this comment

I have a problem to change the picture into Forms.Frame.1 class

I choose picture property for XPMinus, after the program open the folder for select the image and save all, the showing drawing non change.

what can I do ?

many thank's

Comment by: Peter Thornton (1-4-2017 10:20:15) deeplink to this comment

Hi Roberto,
In Access it can be difficult to add or change the 'picture' property in Image controls on the Frame.

Simple way is to Cut the Image control from the Access Frame, and paste it into a frame or even directly onto a userform in Excel; add or change the the 'picture' property in Excel, then copy the Image control back to the Access Frame.

You can create all your Images in Excel, then copy all in one go to the Frame in Access.

Comment by: filou07 (12-5-2017 11:15:17) deeplink to this comment

How can I count all children and sub-sub-sub...children of a node ?

Comment by: Ashish (12-5-2017 11:24:48) deeplink to this comment

Hello everyone,

Is this possible to add multiple columns in tree view.

Comment by: Peter Thornton (13-5-2017 17:08:09) deeplink to this comment

Hi Ashish,
It is possible but means significantly adapting the treeview code. I've seen various attempts but none that appear to work very well. Better to display columns of data in an adjacent listbox/listview, or our ListGrid. Look at Windows Explorer, with a treeview in the left panel and list on the right.

Comment by: Peter Thornton (13-5-2017 17:14:38) deeplink to this comment

@ filour07

Try this recursive routine to count all child and grandchild nodes

Sub recChildNodesCount(cNode As clsNode, count As Long)
Dim cChild As clsNode
    If Not cNode.ChildNodes Is Nothing Then
        For Each cChild In cNode.ChildNodes
            count = count + 1
            recChildNodesCount cChild, count
    End If
End Sub

Call it like this

Dim count As Long
    recChildNodesCount mcTree.ActiveNode, count
    MsgBox count

Comment by: Charles Vanders (11-8-2017 08:14:55) deeplink to this comment

Thank you for filling the gaping hole left by a crappy microsoft decision to leave treeview users high and dry!


How do you add a child to a node when you only know its key?

ie how do I search whole collection for a specific node by key?

Comment by: Peter Thornton (11-8-2017 16:07:59) deeplink to this comment

Hi Charles,
Thanks, glad you like it. FWIW the 'pro' Treeview has moved on a long way!

Your last Q first followed by two ways to add a new node

Set cParent = mcTree.Nodes("parentKey")
Set cChild = cParent.AddChild("newKey1", "New node1")
' or
Set cChild = mcTree.NodeAdd("parentKey", tvChild, "newKey2", "New Node2")

Comment by: yossi oulu (24-8-2017 18:32:09) deeplink to this comment

Is it possible to place the tree view control on top of the worksheet instead of on a user form?
If yes, do you have an example?
Thank you

Comment by: Jan Karel Pieterse (25-8-2017 13:14:24) deeplink to this comment

Hi Yossi,

I've had one sample of someone who tried it. But I think too much will need to be adjusted to make it work reliably.
It isn't very hard to create a userform instead I'd say?

Comment by: Yossi (25-8-2017 17:33:27) deeplink to this comment

Yes, I guess this is OK
Thanks a lot

Comment by: Antoni (13-3-2018 19:08:24) deeplink to this comment

I try this spectacular "component", congratulations!!

It's possible to add a new property thats It show in every node parent the number of childs has a it checkered?

Thank you,

Comment by: Jan Karel Pieterse (14-3-2018 09:16:23) deeplink to this comment

Hi Antoni,

Given that we have provided all code in theory you should be able to pull that off :-)

Comment by: Jan Karel Pieterse (23-4-2018 14:53:05) deeplink to this comment

test message

Comment by: Dmitriy (17-10-2018 09:18:27) deeplink to this comment

Thankyou very much for this great job !

Comment by: Gary Moore (30-10-2018 22:45:09) deeplink to this comment

It's my understanding that the clsNode Expand method, when set to True on a Parent node, should expand to show all the Child nodes. Maybe I've misunderstood but all that happens when I try is that the plus indicator (+) on the Parent node changes to the minus indicator (-) without showing any of the Child nodes. Just making sure I'm not trying to do something the code is not designed for.

Comment by: Peter Thornton (31-10-2018 17:12:10) deeplink to this comment

Hi Gary,

Indeed, if you click an expander icon from + to - the node's childnodes should be shown. Further nodes in the branch (childnodes of childnodes) may also be shown but only if each respective parent's Expanded value is true. (If changing with code during runtime also need to call Refresh). You say it's not working like that, is that with the original demo or your own treeview?

Comment by: Hannes (12-1-2019 19:12:39) deeplink to this comment

Thanks for the good work, it really works great!
And faster than before! :)

Comment by: Salim Talal (30-5-2019 22:14:00) deeplink to this comment

Could you please be kind enough to provide coding example on how to remove a node.

You explained that you have mcTree.NodeRemove, could you show an example by code how to use it!!

I have great difficulty in implementing this, as I keep on getting error property does not support this method.
Perhaps am using old version.


Comment by: Salim Talal (30-5-2019 23:42:00) deeplink to this comment

Please show by code how to remove a child node


Comment by: Peter Thornton (31-5-2019 09:37:00) deeplink to this comment

Hi Salim,

Dim cNode As clsNode
    Set cNode = mcTree.ActiveNode.ChildNodes(1)
    mcTree.NodeRemove cNode

Comment by: ggroh (13-7-2019 15:46:00) deeplink to this comment

When I try to run this codes to on sheet activex(frame) not userform, run-time Error 9: subscript out of range occurs.
Any suggestions?

Comment by: Peter Thornton (13-7-2019 23:16:00) deeplink to this comment

Hi ggroh,

Without any details I cannot suggest why you're getting that error.

The treeview can be adapted to work on an ActiveX Frame on a worksheet. However note it does not persist between sessions and should be cleared before saving the workbook.

Comment by: ggroh (14-7-2019 06:33:00) deeplink to this comment

This is sample code to test on sheet ActiveX Frame, run-time Error 9: subscript out of range occurs.

workbook name is Treeview_PJ
sheet name is TestSheet
frame name in TestSheet is Treeview_Test

follow code is in TestSheet

Option Explicit

Private WithEvents mcTree As clsTreeView

Private Sub Test()
    Dim cRoot As clsNode
    'Dim cNode As clsNode
    'Dim cExtraNode As clsNode

    Set mcTree = New clsTreeView

    On Error GoTo errH
    With mcTree
        'Set .TreeControl = Me.frTreeControl
        'Set .TreeControl = ThisWorkbook.Worksheets("TestSheet").Treeview_Test
        Set .TreeControl = Treeview_PJ.TestSheet.Treeview_Test
        Call .NodesClear
        '.AppName = "TEST"
        .CheckBoxes = True
        .RootButton = True
        .LabelEdit = 0
        .Indentation = 20 * 0.75
        .NodeHeight = 16 * 0.75
        .ShowLines = True
        Set cRoot = .AddRoot(sKey:="Root", vCaption:="Root Node")
        cRoot.Bold = True
    End With
    Exit Sub
    #If DebugMode = 1 Then
        Debug.Print Err.Source, Err.Description
    #End If

    Debug.Print Err.Source, Err.Description

    If Not mcTree Is Nothing Then
    End If
End Sub

Private Sub CallTreeview_Click()
Call Test
End Sub

Comment by: Peter Thornton (15-7-2019 09:42:00) deeplink to this comment

Hi ggoh,

Your example worked fine for me, where does it error for you?

Be careful to ensure mcTree is fully cleared with .TerminateTree before it loses scope for any other reason.

In passing, if the code is behind the same sheet as the Frame you can simply refer to it like this:

Set .TreeControl = Me.Frame1 ' named in your example Treeview_Test

or safer to avoid compile errors if the frame doesn't exist or aX controls are disabled:
Set .TreeControl = Me.OLEObjects("Frame1").Object

Comment by: Peter Thornton (16-7-2019 23:18:00) deeplink to this comment

Ggroh sent me his file off-line, indeed a few changes are required for use if embedded on a worksheet:

In clsTreeview
in BuildRoot(
bInit = TreeControl.Count = 0
bInit = TreeControl.Controls.Count = 0

Private Sub SetActiveNodeColor(Optional bInactive)
Private Sub SetActiveNodeColor(Optional bInactive As Boolean)

In clsNode
Friend Sub EditBox(bEnterEdit As Boolean)
Set moLabSizer = moTree.TreeControl.Add(
Set moLabSizer = moTree.TreeControl.Controls.Add(

Comment by: Rattie (18-9-2019 12:43:00) deeplink to this comment

I just wanted to say how magnificent this work is, and to thank you from the bottom of my heart for creating, improving and taking the time to answer peoples questions.

Thank you.

Comment by: Jan Karel Pieterse (18-9-2019 14:47:00) deeplink to this comment

Hi Rattie,

Thank you and you're welcome!

Comment by: AskingGuyFromTheInternet (25-9-2019 11:45:00) deeplink to this comment


im filling the treeview from an array that contains my content.
mcTaskTree.AddRoot (content) by doing this i dont get an err, so it seems to be working.
As soon as i do mcTaskTree.Refresh i get the following error : Error in Build Root : Type Incompatibil.

U got any ideas what is wrong?


Comment by: Peter Thornton (25-9-2019 15:34:00) deeplink to this comment

Hello AskingGuy,

With so little information it's impossible to suggest what might be causing the error. What line does it error on and what is the error number and error description?

Comment by: AskingGuyFromTheInternet (26-9-2019 09:05:00) deeplink to this comment

Hi Peter,

so i go an Array that contains a few strings.
I loop through my Array and want to push the Elements of the Array into to the tree.
So i do this:
Public WithEvents mcTaskTree As clsTreeview
Set mcTaskTree = Me.subTreeviewTask.Form.pTreeview 'thats my Tree and the SubForm the Treeview is in.
mcTaskTree.Refresh '--> Throws an Error

Runtime Error 13: Error in BuildRoot : Type incompatible.

Hope thats better. If not, let me know :)


Comment by: Peter Thornton (26-9-2019 10:07:00) deeplink to this comment


Right-click Treeview in project explorer, in Properties add the following in the 'Conditional Compilation Arguments' box:
DebugMode = 1

Next time code should break in the error handler in BuildRoot, use F8 to step back to the code line with the error. What is this line, and what is any relevant data, for example if the error line includes cRoot.Caption:
Debug. Print cRoot.Caption
Debug. Print VarTpye(cRoot.Caption)

If stepping back takes you into BuildTree, uncomment 'On Error GoTo locErr' at the top, and
the error handler starting 'LocErr:' at the bottom. Similarly what is the error line and varType of any data (typically .Caption) on the error line.

Comment by: AskingGuyFromTheInternet (1-10-2019 09:32:00) deeplink to this comment

Hi Peter,

thanks for your help. I found the error!
Now i got another question... is there a way to change the font of the nodes? I need the font to be "courier new", so all my treeNodes are aligned right.


Comment by: Peter Thornton (1-10-2019 12:00:00) deeplink to this comment

Hi AskingGuy,

If using Excel, change the font in the treeview control frame, nodes will inherit the frame's font properties. In Access, in the subTreeview code see ApplyDefaultFont, uncoment the example code and apply your choice of font properties.

Comment by: AskingGuyFromTheInternet (1-10-2019 16:08:00) deeplink to this comment

Hey Peter,

big thanks!

Comment by: Jamie (17-10-2019 19:24:00) deeplink to this comment

When I click the "+", to open a node, I can trap that with _Click event. But if I click on the same button again (Now a "-") the _click even no longer fires. Is there another event I should be using? If so what's its syntax?

Comment by: Peter Thornton (17-10-2019 20:48:00) deeplink to this comment

Hi Jamie,

In clsTreeview, in Sub NodeEventRouter, towards the bottom, comment 'If bFlag Then and corresponding 'End If

Alternatively add your own Expand event:
Leave the If bFlag ...End If uncommented as it is
Below the End If add this new line

RaiseEvent Expand(cNode)

In the clsTreeview declarations area, include this new Event with the other declared Events
Event Expand(cNode As clsNode)

In your form, select mcTree in the left dropdown and you should see the new Expand event stub in the right dropdown.

Comment by: Jamie (29-10-2019 16:52:00) deeplink to this comment

I have one form with the subTreeView loaded on it and it's working well. I copied the subTreeView form on to another form and now I'm getting an error when the new form, with the subTreeView, loads. Error msg: "The expression you entered refers to an object that is closed or doesn't exist" This is occurring on the line "ctl.Form Is Me". What am I doing wrong? To I need unique copies of the subtreeView subform.

Comment by: Jan Karel Pieterse (30-10-2019 09:05:00) deeplink to this comment

Hi Jamie,

Are you sure the object variable Ctl actually points to the correct instance of the treeview? Perhaps it points to one that is closed?

Comment by: Voytec_P (21-11-2019 09:10:00) deeplink to this comment

Great tool!
Thank you

Comment by: Martin (23-11-2019 23:32:00) deeplink to this comment

Is there any way to add a Tag to a node?

Comment by: Peter Thornton (24-11-2019 12:54:00) deeplink to this comment

Hi Martin,

Tag is a built-in clsNode property, a Variant

cNode.Tag = "my tag"

Comment by: Adelina Trandafir (1-3-2020 12:20:00) deeplink to this comment

Hey. Thank you very much for an amazing project. i've been using it for a few years now. i have o question: is it possible to open the branch with a single click on the label control? i've tried adding a new property, and using it as i thought it should be, but it just doesn't expand or collapse the node as does the expander button. i know it's something in the buildtree function, but i just can't, for the life of me, figure out what...
Thank you again for the treeview, and for the answear :) maybe :P

Comment by: Peter Thornton (2-3-2020 09:22:00) deeplink to this comment

Hi Adelina,

There are several ways, the following will only expand if the node is already the active-node -

In clsNode / Private Sub mctlControl_MouseDown, at the top of the routine change the first 'If...End If' as follows

If moTree.ActiveNode Is Me And Not mcolChildNodes Is Nothing Then
    Exit Sub
End If

However this approach would not work well if editing is enabled with double-click on parent nodes.

Comment by: Adelina Trandafir (2-3-2020 13:57:00) deeplink to this comment

Thank you for the quick reply. I had it working a few minutes after i posted the question. But now i find myself not being able to load images in the picture control. Which is wired, because it worked for one picture, one time and then it stopped. I tried loading the same picture again and still nothing. I've tried on the demo tree and nothing. The pictures are 16x16 bmps. Any thoughts on that? Thank you again. You're magic &#10084;

Comment by: Peter Thornton (2-3-2020 23:22:00) deeplink to this comment

Hi Adelina,

If(?) you're using the Access version it can be difficult to add images to the frame. Try adding the image control(s) + picture(s) to a temporary userform in Excel. Select the image control(s), copy, and paste into the Access frame. The frame has to be selected correctly first, there's a knack to it!

Comment by: Adelina Trandafir (3-3-2020 08:22:00) deeplink to this comment

Thank you so much. It's weird that, after i restarted my pc i was able again to add image controls and pictures to them. anyway, i'll keep your solution for next time. what's amazing is that, after all this time, you still answer super fast.

Comment by: Peter Thornton (3-3-2020 09:30:00) deeplink to this comment

Well Adelina, we're still here:)

Comment by: Chad McKay (4-3-2020 14:26:00) deeplink to this comment

Looks like a great tool. I've done some testing and am running into a strange problem. I have a simple routine that dynamically builds a tree based on an array that I feed to it. When I run through the code (button click) with break spots (and step through the code) everything works great. When I remove the breaks and run the code (button) then the tree is not built. No errors, just an empty tree. No changes in the code just stepping through vs running with no breaks. Excel 2010. Help?

Comment by: Jan Karel Pieterse (5-3-2020 10:33:00) deeplink to this comment

Hi Chad,

Odd, I haven't seen that in my implementations so this must be something specific to yours. Can you email your file to me and explain what I do to reproduce your issue? My address is at the bottom of this page.

Comment by: CHAD A MCKAY (5-3-2020 13:40:00) deeplink to this comment

:) Figured out what was going on. It was a duplicate key issue. Using the cRoot.key + str(NOW) function at the tail end of my key values. Without the break spots in place the code was running too quickly resulting in duplicates. I changed my key strategy to index and now it works great. Thanks for the great tool.

Comment by: Jan Karel Pieterse (5-3-2020 13:48:00) deeplink to this comment

Hi Chad,

Glad you were able to solve your issue.

Comment by: Alexandr (21-4-2020 00:48:00) deeplink to this comment

Hi, thank you for great tool!
How to write changes in a tree (add, delete...) to tables?

Comment by: Peter Thornton (21-4-2020 10:04:00) deeplink to this comment

Hi Alexandr,

You can add nodes during runtime with the same methods as adding nodes at startup: clsNode.AddChild, clsTreeView.NodeAdd, clsTreeView.AddRoot. You can also remove nodes during runtime with clsTreeview.NodeRemove.

The demo includes examples; see the 'Add Child', 'Add Sibling' and 'Remove Node' buttons on the demo form, also on the demo form key Delete to remove a node.

Comment by: Julian Rodriguez (20-7-2020 15:35:00) deeplink to this comment

Hi Peter:
I have been using your treeview control in my application for some years without any problem. It is really a fantastic control. However a month ago a collegue of mine using my application told me that there was a bug in my application. When I took a look I realized that for whatever reason when the aplication had to use your treeview it failed and prompted a message saying that the control had been dissabled for changes in the security directives or something like that. I checked the security center of acces and everything was apparently correct without restriccions etc...
Do you have any idea of what is going on? Have other users had a problem like this.
My colleague uses office 2007 on windows 7. But I have other colleagues that use the same and it works fine!!
I do not realyy know what to say to my friend and what else I can check or do

Comment by: Peter Thornton (21-7-2020 13:18:00) deeplink to this comment

If you've got an MSForms Frame control on the form to store images check if ActiveX controls are enabled in Trust Center settings.

Comment by: Adelina Trandafir (23-7-2020 10:20:00) deeplink to this comment

So, Hi again. I feel i owe you a beer or something (maybe a coffe :P)

My question, this time, is: can i remove the horizontal scrollbar, even if the data is larger than the control? I've tried fiddling around with the "SetWindowLongPtr" function, but apart from removing the title bar and the thick border of the child window, nothing works...

It's not a real issue, as i can work around it by making the control large enough to accommodate the text, but it would be useful to have a property like "ShowHScrollBars" in the treeview class... :)

Thanks for the time and the info

Comment by: Peter Thornton (24-7-2020 09:36:00) deeplink to this comment

Hi Adelina,

A coffee or beer are both good depending on time of day, cheers!

If you remove the H-scrollbar and the control isn't wide enough your users won't be able to see any nodes or text that extend to the right..?

So I don't follow why you'd want to disable the H-scrollbar, but if I'm missing something simply comment or not call under a flag this line in Public Sub SetScrollbars()

    If .InsideWidth < .ScrollWidth Then
     ' bars = fmScrollBarsHorizontal[
    End If

Then maybe allow the user to to scroll with the keyboard, say with the left/right arrows if Ctrl is held; for example in the TreeControl_KeyDown event

Case vbKeyRight
    If Shift = 2 Then
        With TreeControl
            .ScrollLeft = .ScrollLeft + 6
        End With

Comment by: Adelina Trandafir (28-7-2020 10:37:00) deeplink to this comment

Thank you so much. The idea is that very few rows extend over the right edge, and i don't have enough room on my form to show everything, so i use controltiptext to display the extra information. that's why i don't care if they see everything on each branch.
if you ever get to bucharest, i will assert the time of day and do what's needed :P :P :P
Thank you :)

Comment by: Peter Thornton (28-7-2020 18:27:00) deeplink to this comment

Normally scrollbars solve the problem when internal contents do not fit within the internal dimensions the control (frame in Excel / subForm in Access), so I still don't follow why you want to disable the horizontal scrollbar. Guess I'm still missing something but if you've got it working as you want all is good.

That's a shame, I was in Bucharest 18 months ago!

Comment by: Adelina Trandafir (31-7-2020 19:23:00) deeplink to this comment

Hey again. Here goes another question for when you have the time: is there a way to disable the control? But really disable it, because now i'm using a check-box on the parent form which, when checked - it disables the NodeClick event, but the user can still click on it, which looks kinda' unfinished. I was hoping there is a way to even grey it out somehow?

Thanks a lot :)

Comment by: Adelina Trandafir (1-8-2020 10:24:00) deeplink to this comment

I've solved the "Disable" issue by adding "Enabled" property to the tree and then using it in the NodeClick() event in the clsNode class. It doesn't grey-out the nodes, but it will have to do for now. Thank you so much! >:)<

Comment by: Adelina Trandafir (2-9-2020 10:51:00) deeplink to this comment

Hi again. Your nightmare is back :D
Firstly, i know you don't have to answer my questions, so thank you in advance if you do :D :P
I'm trying to do the following:
- when a user clicks on a node, i want to get the data from it and the close the form containing the tree, but i get an error because it appears that when the form closes it tries to click again on the node which has been destroyed and an error occurs:
mctlControl_Click() ->        

If moTree.ActiveNode.ChildNodes Is Nothing Then
            If moTree.SingleClick And Not RightClick Then mctlExpander_Click
        End If

the motree is nothing because i'm closing the form. is there any way to get over this?

Comment by: Peter Thornton (3-9-2020 16:20:00) deeplink to this comment

Hi nightmare, sorry I mean Adelina :)

I don't seem to have a problem closing the form in the node's click event trapped in the form, like this -

Private Sub mcTree_Click(cNode As clsNode)
    DoCmd.Close acForm, Me.Name, acSaveNo ' Access
    Unload Me ' Excel
End Sub

So I guess you're closing in a different way(?).

To avoid any event and timing problems try closing your form in the MouseUp event. It's not exposed in the demo but you can easily add it yourself...

Find the NodeEventRouter procedure in the clsTreeview and the comments briefly explains how to add additional events. The example suggests using a single event 'MouseAction' to handle all events, but your could 'RaiseEvent MouseUp +arg's' if you include the event in the declarations like this

Event MouseUp(cNode As clsNode, Button AS Integer, Shift As Integer, X As Single, Y As Single)

Comment by: Adelina Trandafir (3-9-2020 16:35:00) deeplink to this comment

It worked like a charm (lucky one), so thank you very much. :)

Comment by: Sameer Kelkar (3-11-2020 19:03:00) deeplink to this comment


Thank you very much for your amazing work. I am currently working on Parent,Child relationship as being discussed in this exchange:

My Parent Child Relationships have duplicate child values such as below. How to support duplicate key values in this code.

Parent         Child
A06080010 A06180010
A06180010 0201002
A06300030 A06301010
A06300030 0201002    (*node with duplicate key value)

Comment by: Jan Karel Pieterse (4-11-2020 09:48:00) deeplink to this comment

Hi Sameer,

One option is not to use the key, as long as you make sure you add the node to the right parent all should work as expected. another option is to use the path to the node as key. Suppose you have a node two levels down the tree, then you could define the key as:

sKey = ParentParentKey & "|" & ParentKey & "|" & NodeKey

Comment by: Sameer Kelkar (4-11-2020 14:42:00) deeplink to this comment

Yes I got it to work that way. I was thinking about the same and saw your reply. It works perfectly now.

I am working on creating tool to display/edit "Bill Of Material" data where base data will be fetched from SQL DB, which has "Parent|ParentDescription|Child|ChildDescription|Qty" Format.

If I end up making any reasonably good thing I will share it here.

Kudos for your tool. Its really a boon for me _/\__/\_. I really appreciate it!!. Thank you very much.

Sameer Kelkar

Comment by: Jan Karel Pieterse (4-11-2020 16:15:00) deeplink to this comment

Hi Sameer,

Thanks, you are most welcome!

Comment by: Anna Vera (7-12-2020 14:24:00) deeplink to this comment


Is it possible to change the visibility of a node without actually removing it from the tree?

I have a tree where the group levels are pre-defined and users can add/remove child nodes. The parent nodes should be displayed only if there are any child nodes. I don't want to remove them as they are displayed in a particular order(not based on captions/keys) but hide/remove based on user action.

Many Thanks,

Comment by: Jan Karel Pieterse (7-12-2020 14:49:00) deeplink to this comment

Hi Anna,

The Visible property of the Control of a node is available:

cNode.Control.Visible = False

But visually the only effect that has is that the text disappears.

So either you program this yourself by adding a new property or method to the clsNode class which allows you to hide a node, but perhaps what you could do is set the Enabled state to false:

cNode.Control.Enabled = False

Comment by: Adelina Trandafir (20-12-2020 13:52:00) deeplink to this comment

Let me start by wishing you a Merry Xmas and a happy New Year! :)

I have 2 questions this time, for when you have the time:
1) Is it possible to set the node height individually, not as a property for the whole tree?
2) Is it possible to set the Control.Enabled=False at the creation of the tree, rather than after?
3) This is the big one: can you point me in the right direction as to have another column in each node? - I know that probably this is something difficult and may exist in a payed version, but i was trying to hone my skills in VBA (pointless - I know) and to adapt your code a little (I love doing that). The second column doesn't have to be selectable or anything, I just need a way to present data in tabular form and i hate formating the text every time.
4) BONUS QUESTION: Is there a way to insert some sort of node separator line?

Thank you so much for your work and for all your answers!

Adelina, a VBA freak.

P.S. There were only 2 questions at the beginning, but then i remembered I have more :D

Comment by: Peter Thornton (20-12-2020 18:36:00) deeplink to this comment

Hi Adelina,

Thanks, and let me return the same good wishes to you:)

#1 Not impossible but a lot of work to implement variable node heights. With all same heights it's simple to calculate the vertical position of each visible (ie expanded) node, as required after collapse/expand and scrolling, also for 'ScrollToView'.

#2 Simply mcTree.TreeControl.Enabled = False. In Excel & Word you could disable the Frame control at design, and enable it when you want during run-time

#3 That's actually easier than #1, still quite a lot of work though. I haven't looked into it but from memory I might have been sent example, a long time ago.

However if you have a column of data and with nodes under the same parent each with similar data, consider moving all the sibling 'leaf' nodes into a multi-column List to give something overall like an 'Explorer' type control. FYI The 'PRO' Treeview and ListGrid are designed to work as a pair like that.

#4 Similar to #1 but even more complicated!

Comment by: Adelina Trandafir (8-1-2021 11:34:00) deeplink to this comment

Happy new year and hopefully a healthy one too :P
I have an easier question this time: is it possible to make the rows in the tree have 0 (zero) space between them? Thank you and i hope you're doing fine

Your friend and follower,
Adelina T.

Comment by: Peter Thornton (9-1-2021 14:17:00) deeplink to this comment

Hi Adelina,

Thanks, and the same to you:)

With the demo run the form, increase the font sise to about 12 and decrease NodeHeight to 10 (not high enough to display the full font height). Run demo2, expect now to see the NodeHeight has automatically increased to accommodate the larger Font size.

If you select the coloured nodes under node-4D there shouldn't be any gaps or spaces vertically between the nodes.

It is possible to avoid gaps in all scenarios but a bit of work to adapt the treeview code. FWIW that's all handled in the 'pro' version and no gaps with any combination of font-size & NodeHeight, also height is automatically sized for icons.

Comment by: Adelina Trandafir (17-1-2021 11:22:00) deeplink to this comment

Thanks a lot for everything. You are right with the pro version and i want it, especially since i want to pay you for everything, but i can't find the price tag anywhere on the site.

Comment by: Peter Thornton (17-1-2021 13:32:00) deeplink to this comment

Hi Adelina,

There are enquiry links on the main Treeview page, just below the screenshot of the ListGrid. However I have emailed you off-line with details.

Comment by: Peter Poppe (21-1-2021 04:51:00) deeplink to this comment

Is there a way to expand a node branch? The node has the node has an expanded property but when you set this to true nothing happens except the + becomes an -. The tree has an ExpandedNode property, but the same happens. I do not want to expand the entire branch just open it one level. Thanks.

Comment by: Jan Karel Pieterse (21-1-2021 10:04:00) deeplink to this comment

Hi Peter,

After changing a property of a node (or the tree as a whole), you must call the Refresh method of the treeview class instance to display the change. You can first have your code make all the property changes and then use a refresh at the end to have them all come into effect.

Comment by: Daniel Fried (18-5-2021 17:34:00) deeplink to this comment

WithEvents mcTree         As clsTreeView
Compile error

Comment by: Peter Thornton (19-5-2021 11:03:00) deeplink to this comment

Hi Daniel,
Could you give more details; is this with the Access or Excel version, and what is the decimal separator in your language settings?

Comment by: Sam (7-10-2021 19:04:00) deeplink to this comment


There is a bug in V26.4 last version.
- click on "Demo1 Treeview" button
- click on "Clear the Treeview" button
- Click on "Dump Data" button

Error is triggered here:
    '' Dump all treeview data
    For Each cRoot In mcTree.RootNodes <== ERROR
        GetData1 cRoot, lCnt, 0, ws.Range("A1")

=> Object variable or With block variable not set


Comment by: Peter Thornton (8-10-2021 12:13:00) deeplink to this comment

Hi Sam,

Thanks for flagging this. It’s not a bug in the Treeview but in the demo.

Code behind the DumpData button checks to ensure the mcTree instance is running, but it doesn’t check if it actually has any nodes. Then it fails, as you point out, if all nodes have been removed with NodesClear.

We will amend that in the next upload, in the meantime don’t click the DumpData button if there are no nodes as a result of clicking the Clear button:)

Comment by: Oleg (24-10-2021 12:46:00) deeplink to this comment

Hi.Thank you for so good classes. (VBA Excel)
Could you explain, where I should place class declaration:
Private WithEvents meTree As clsTreeView
for access from child forms? It is able to see meTree as object in main start Form only. It is not available in child forms, for example: when I call some OtherForm.Show in my project, meTree is not available as object. I tree to declare like that: Public WithEvents meTree As clsTreeView - no help. Thank you in advance.

Comment by: Jan Karel Pieterse (25-10-2021 10:51:00) deeplink to this comment

Hi Oleg,

I'm not sure what your setup is, do you have one userform in your VBA project or more than one?
Note that you need more than just the declaration, you also need to use this statement somewhere in your userforms' startup code:

Set meTree = New clsTreeView

Comment by: Gabriel (21-3-2022 02:15:00) deeplink to this comment

I can generate the treeview OK!!
But after create the root nodes and the childs nodes, (whit 4 levels) I need activate and show an specific node (the last selected and clicked in previus session).
The idea is that the tree open with the last node used preselected. In the previos session, I save the key of node.
I assign object cNode to variable lastNode of the saved node during the new generation of tree, and try to activate that node with the property mcTree.ActiveNode, but that generate ERROR.

Set mcTree.ActiveNode = lastNode

generate error 91, "Object variable or With block variable not set"

The error is apparently generated in the class clsTreeView Subs:
ResetActiveNodeColor and SetActiveNodeColor

I appreciate help to correct it

Comment by: Peter Thornton (21-3-2022 11:57:00) deeplink to this comment

Hi Gabriel,

Store some way of referring to the last activenode before destroying the treeview. Simplest is if you use keys, for example -

' in previous session
sLastKey = mcTree.ActiveNode.Key

' in new session
With mcTree
    ' create the treeview
    Set .ActiveNode = .Nodes(sLastKey)
    .ScrollToView ' ensure the new activenode is expanded and in view
End With

Comment by: Gabriel (21-3-2022 15:41:00) deeplink to this comment

Thank you!!! Now works as expected..
Initialize tree
add roots...
add nodes...
Set .ActiveNode = .Nodes(sLastKey) --> error!

Initialize tree
add roots...
add nodes...
Set .ActiveNode = .Nodes(sLastKey) --> AFTER refresh is the correct way

Comment by: Gabriel (21-3-2022 16:16:00) deeplink to this comment

Hello again
In the previous question you solved the error about activating a node, thanks for the help!
Now, the node is selected but does NOT fire its click event.
How can I trigger the click on that node by code?

Something like "" ....

Comment by: Jan Karel Pieterse (21-3-2022 17:04:00) deeplink to this comment

Hi Gabriel,

The treeview does not trigger a click event when you use code to set the active node.

You could add that yourself, by adding this line to the end of the "Public Property Set ActiveNode" routine in the treeview class:

RaiseEvent Click(oActiveNode)

Comment by: Gabriel (21-3-2022 17:36:00) deeplink to this comment

thank you very very much!!!

Comment by: Essoo (27-4-2022 05:15:00) deeplink to this comment

Thanks for the treeview controll works perfectly as explained

I want to know if this treeview has RightToLeft property and if not how to align it ftom right to left

Thanks in advance

Comment by: Peter Thornton (28-4-2022 09:22:00) deeplink to this comment

Hi Essoo,

This Treeview does not have a RightToLeft property and I can't suggest an easy way to adapt it as RTL. It would require significant changes!

Comment by: Gerson (30-6-2022 14:17:00) deeplink to this comment


Thank you so much for this CODE! Very interesting for MAC/PC...

I followed the steps but I'm getting this error:
"User-defined type not defined" in the line:
"Private moCheckboxImage (-1 to 1) As StdPicture"

Could help me?

Thank You

Comment by: Peter Thornton (4-7-2022 11:28:00) deeplink to this comment

Hi Gerson,

"User-defined type not defined" with "As StdPicture" highlighted would suggest your project does not include the "OLE Automation" (stdole) reference.

That's unusual as normally this reference is included by default, but see if it's ticked in tools references.

Comment by: Gerson (4-7-2022 21:44:00) deeplink to this comment

Just perfect!

It was the OLE Automation reference.

Great project!

Thank you!

Comment by: Chris (13-3-2024 15:55:00) deeplink to this comment

I am trying to use this in an Access project and performed the following steps:
* Imported subTreeview, clsNode, clsTreeView, modStartup into Access (O365/Version 2308) DB by drag-and-drop.
* Added 'Private WithEvents mcTree As clsTreeView' to the declarations for my form.
* Added the subTreeview to a frame on my form.
* Ensured that OLE Automation is referenced.
However, when opening my form or, for that matter, subTreeview I get the following errors:
'The expression On Load you entered as the event property setting produced the following error: User-defined type not defined.'
That same error for On Resize, Mouse Move, and On Close when I open and then close the subTreeview form.
Went through the documentation in the Access as well as the Excel versions but can't figure out what I'm missing.

Comment by: Jan Karel Pieterse (13-3-2024 16:03:00) deeplink to this comment

Hi Chris,

Did you run the code in modStartup?

Comment by: Chris (13-3-2024 16:16:00) deeplink to this comment

That got it; altered the DoCmd.OpenForm line to run my userform instead of the demo form I didn't copy over. Thank you. Looking forward to learning this tool. Thanks!

Comment by: Chris L. (26-3-2024 14:29:00) deeplink to this comment

Struggling a bit with a node exist test, adapting an older MS treeview function but not having any luck. Included below. I poked around in your 'Public Property Let Key(ByVal sKey As String)' code to get the sTree.Nodes.Item(strKey) bit but can't seem to make it work... any tips?

Also, is there a way to include checkboxes on only some levels of the tree? Not on the root, say, but on the children of the root only.

' MS Treeview Node Exist Test.
Public Function NodeExists(ByVal strKey As String, sTree As Object) As Boolean
    Dim Node As MSComctlLib.Node
    On Error Resume Next
    Set Node = sTree.Nodes(strKey)
    Select Case Err.Number
        Case 0
            NodeExists = True
        Case Else
            NodeExists = False
    End Select
End Function

' PF Treeview Node Exist Test.
Public Function PFNodeExists(ByVal strKey As String, sTree As Object) As Boolean
    Dim tmpNode As clsNode
    On Error Resume Next
    Set tmpNode = sTree.Nodes.Item(strKey)
    Select Case Err.Number
        Case 0
            PFNodeExists = True
        Case Else
            PFNodeExists = False
    End Select
End Function

Comment by: Jan Karel Pieterse (26-3-2024 16:14:00) deeplink to this comment

Hi Chris,

If you place this in the clsTreeView class:

Public Function NodeExists(ByVal strKey As String) As Boolean
    Dim Node As clsNode
    NodeExists = False
    For Each Node In Me.Nodes
        If Node.Key = strKey Then
            NodeExists = True
            Exit For
        End If
End Function

you can use it like so:

' tree is an instance of clsTreeView
Dim theKey As String
theKey = "foobar"
MsgBox "nodeExists for node " & theKey & " returns " & tree.NodeExists(theKey)

Comment by: Chris L. (27-3-2024 15:29:00) deeplink to this comment

Thanks for the help on the NodeExists test, worked great.
Question - is it possible to have Checkboxes only at certain levels of the tree? I have some elements at level 3 I want to be selectable but Level 1 and 2 are more like categories that shouldn't be clickable.
So I tried the .Checkbox property for the node...

    With mcTree
        .AppName = Me.Caption
        .CheckBoxes = True
        (some lines not reproduced here)
        Set cRoot = .AddRoot("Aircraft", "Aircraft", "FolderClosed", "FolderOpen")
        cRoot.Bold = True
        ' cRoot.Checkbox = False ' This doesn't work.
    End With

... but no luck. Is that only able to be set for the whole tree rather than individual nodes?

Comment by: Jan Karel Pieterse (27-3-2024 16:31:00) deeplink to this comment

Hi Chris,

I'm afraid we didn't implement it with this in mind, checkboxes is currently an "all or nothing" setting.

Comment by: hey jkp :) (15-4-2024 17:11:00) deeplink to this comment

Long time no talk :)
I haven't got to implementing the pro version yet (the demo works so nicely and is very customizable - i did manage to get checkboxes only for the nodes i needed with some new properties and a few lines of code) and also got it to look like a kind of tree-listbox.
The only issue i'm facing and haven't been able to solve it (tried anything from the MSForms properties to APIs) is that, once the msform gets focus by the SetForegorundWindow/BringToTop, i can't alt tab back to it.
I've read some things online, but i thought i would ask you, if you have time: is there any way to make the alt-tab thing work?
Thanks and hope you're well :)

Comment by: Jan Karel Pieterse (16-4-2024 09:49:00) deeplink to this comment

Hi jollieandreea,

You mention that you can't get back to "it". What is meant by "it" exactly?

Comment by: Adelina Trandafir (16-4-2024 14:44:00) deeplink to this comment

I mean I can't alt tab back to the form containing the treeview as long as the treeview itself has focus. If the parent form has focus, not the treeview, it works normally.

Comment by: Peter Thornton (16-4-2024 17:54:00) deeplink to this comment

Hi Adelina,

Yes it's been a while, good to hear from you again!
What you describe is because the treeview uses it's own userform window and there's no simple way to make it a child-window of Access in a way which will make it work with alt-tab. However in the pro-Treeview you should find alt-tab works normally even when the Treeview is in focus.

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:

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].