Most Valuable Professional

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

Subscribe to our mailing list

* indicates required

Audit !!!

Check out our RefTreeAnalyser
the ultimate Excel formula auditing tool.


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

Third party tools

Speed up your file

The best tool to optimise your Excel model!

Repair your file

Stellar Phoenix Excel Repair
Best tool to repair corrupt Excel sheets and objects
Home > English site > Articles > Treeview control > Features

An MSForms (all VBA) treeview; Features


Realistically a VBA form with all the controls we need for each node can’t be expected to match that of the compiled original. Even so we are surprised at what has been possible, albeit with considerable tuning and refining. Light testing suggests 1000 to 2000 nodes is quite feasible even in a typical five year old laptop, depending on the configuration of the treeview.


The original has a wide variety of features and functionality. We have tried to replicate most of these, and added a few features of our own.


The common controls treeview has a checkboxes option so you can check nodes. We ensured our treeview has that too.

A picture says more than a thousand words, so:

Checkboxes are possible and work
Checkboxes are possible and work!

Expand/collapse boxes or icons

By default we draw a rectangle with a plus or minus characters as text to mimic the expand/collapse boxes. You can also opt for expand/collapse icons. We have bundled XP and Win7 style expander icons in the demo form or you can customize your own as you wish:

 Expander icons are provided
Icons as expand/collapse buttons


The treeview from the common controls lib always shows lines to indicate the structure. With ours it is optional to show lines. The choice might be for aesthetic reasons, however a very large treeview will load significantly faster without lines so that might be an overriding consideration.

Choose whether or not to show lines
Choose whether or not to show lines


Again, let's just look at the picture!

Icons can be added
You can add icons too!

If you are familiar with the ImageList control we have incorporated a similar approach. You can store your images in a (hidden) frame and pass a reference to the frame, just as you would pass a reference to an ImageList to the common controls treeview. However your icon images could be stored anywhere. As long as you can retrieve a “StdPicture” handle for each icon and add it to a “collection” that’s all we need.

Label formatting

Label formatting is available to you with code as simple as:

cNode.Bold = True
cNode.BackColor = RGB(255, 255, 220)    ' pale yellow
cNode.ForeColor = RGB(180, 0, 0)    ' dark red

Make sure you do not use code like:

cNode.Control.BackColor = RGB(255, 255, 220)    ' pale yellow

When you are building the set of nodes, the controls of the treeview have not yet been created (this is done when .Refresh is called). Using the properties the node class itself exposes ensures those are applied when the nodes are created. Only once the treeview is fully initialised and refreshed you can access the properties of the cNode.Control.

In addition, the treeview will automatically pick up the default font settings of its parent (frame) control, so controlling appearance of the treeview starts as easy as setting properties of the frame you'll use to host the treeview. I'll explain this on the next page.


We've already implemented quite some functionality. Here is what you can do "out of the box":

Function Description

Collapsing and expanding

Clicking the expand/collapse buttons does as advertised: it expands or collapses the tree.

Navigation using mouse and keyboard

Navigation using mouse and keyboard works just like in the "old" treeview. Right and left arrow expand or collapse, up and down arrow move up or down the tree. Page-up and down just work.

Editing nodes

You can use F2 to edit a node. Double-clicking a node also puts it in edit mode. This option can be enabled or disabled by setting a property value.

Drag and drop

We're working on that.

Copying, moving (cutting) and pasting

Copy and move functions are built in, you can copy or move single nodes or entire branches. You can do that silently but we have also made it easy for you to trap user actions on the selected node, eg Ctrl-c, Ctrl-x, Ctrl-v. You can include your own code to validate the intended actions, eg only allow copy between similar levels, then instigate the copy or move.


An efficient sort routine has been devised especially for this treeview. Currently it only operates at a given node level, however options for sort order and text/binary are included.


There is quite a number of properties you can change. To give you an idea, some are listed below. There are more properties and methods, which are documented in the Excel download.

Property Description Remarks

Full Width

True: Node caption widths extend beyond the width of the treeview, makes for clear highlighting of the selected node and if backcolor's are applied.

False: label widths are 'autosized' to the text
If node icons are used an additional image control is created for use with 'full width'

A large tree using node icons will load faster if 'full width' is not applied.


True: show checkboxes.
Press spacekey or click to change the value.

The check change event is trapped in the form, and the info label updated.

Allow Editing

User can manually edit the node.
Double click or press F2 on a selected node.

Key Enter to apply the edit or Esc to abort.

Show Lines

Whether or not to show lines may be a matter of aesthetics or needs depending on the tree.
Try experimenting with different indentation widths (see the spin button).

In a large tree start-up time will be considerably faster if lines are not shown.

Root button

True: the entire tree can be collapsed to the Root.
False: the tree can only be collapsed to the first level.


Expander icons

The expander buttons can be Labels with +/- characters toggled, or pairs of icons.
If icons are used the images must be available in the workbook, on a sheet or as in the demo hidden on the form.

The demo includes pseudo WinXP and Win7 Explorer type expander icons, but you can use any icons of your choice, or no icons.


Default is 15 points



The treeview already automatically adjusts its Node height to the Font size or checkbox size or Icon size, whichever is the larger.

It's probably best not to adjust Node height.

Font size

The font size and other default label properties are adopted from the parent frame.

Change at design time only.


The root nodes of the tree  


All Nodes of your tree  


Method Description Remarks


Activate a node based on the key.  


Copies the node put on the 'clipboard'  


Move source node + children to destination node.  


Equivalent to Treeview.Nodes.Clear.  


Remove Node, its children and grandchildren.  


Adds and displays all the controls for the Treeview for the first time. ALso displays and repositions node controls as required.  


Updates the expanded properties according to lLevel. Called recursively.


Removes the treeview from memory. Must be called before your userform goes out of scope.


Ensures the node passed to the method is scrolled into view.  


Currently we have made available a limited set of events:

Event Descrription

Click event

Fires when you click any node.

Node check event

Fires when you click the checkbox of a node.

After label edit event

Fires after editing a label.

Key down event

Fires when you press a key. The demoform included in the sample project shows how to implement copy and paste.

Mouse events

All mouse events are pushed through using a single MouseEvents event.

Add your own events

It isn't hard to add your own events, e.g. it takes three steps to add a new event for a specific element of the tree:

  1. Add event to the clsNode class for the piece of the tree you need an event for, by using the dropdowns at the top of the code window. Make sure it then calls a Friend sub in the clsTreeView class.
  2. Add an Event declaration to the clsTreeView class (like shown above) and a friend sub that raises that event when called from clsNode.
  3. Add an event handler to your userform.

Next: using this treeview in your VBA project.




Showing last 8 comments of 51 in total (Show All Comments):


Comment by: Pablo (2/17/2017 9:33:43 PM)

Hi Peter,
Thank you for your quick answer, indeed I'd like to use keys for navigation and only raise a click event only when pressed "Enter" rather than on every keystroke, I'm trying to use the TV as a menu, where you can click or press enter to open the desired form. I tried also trapping the _KeyUp event but for some (odd) reason the keycode for "Enter" is the same as the left-arrow.
I'd be grateful if you can point out where to make the changes you mentioned in this line: "allow keyboard navigation but not raise any click events"



Comment by: Peter Thornton (2/18/2017 11:06:05 AM)

Hi Pablo, OK I think I follow.

In TreeControl_KeyDown replace 3 instances of "Caption", 1 with 0
In the same event near the top, after "If KeyCode = vbKeyReturn" comment the If/Else/End if and replace with:
If Not moActiveNode Is Nothing Then
    RaiseEvent Click(moActiveNode) ' Enter will raise the click event
End If

Or, trap vbKeyReturn in the mcTree_KeyDown or KeyUp event in the main form.

FYI Enter in other treeviews expands or collapses a branch, like the left/right arrow keys, and explains the "odd" reason Enter morphed into an arrow!


Comment by: Ray Buckley (10/19/2017 12:31:03 PM)

Are you supporting drag drop functionality yet


Comment by: Peter Thornton (10/19/2017 5:16:41 PM)

Hi Ray,

Drag & drop is supported the 'pro' (see above) but it's not viable in the free version, at least not in the way we'd want to do it.


Comment by: Robin Ball (3/14/2018 4:34:35 PM)

I am working on a Bill of Materials system and have a working recursive SQL server script that populates a single table with all components and sub builds and their components and their level number in the hierarchy.

Your free Treeview control looks excellent and I could organise my data easily enough to replicate your multi-table approach but in doing so will lose the benefits of my recursive code. I could code to choose how many levels to display for any specific parent item and its multi-level children to an arbitrary number of levels (say 12) which is currently deeper than real world data.

2 questions

Is there a depth limit to the number of levels in the hierarchy ?

Does the pro version allow for recursion and an unknown number of levels ?


Comment by: Jan Karel Pieterse (3/14/2018 5:33:16 PM)

Hi Robin,

Theoretically there are no depth limits. But I would limit use of our free treeview to a max of about 1,000 nodes to get good performance. The pro version can handle thousands of nodes without issue.
Recursively adding nodes should be easy enough but is left as an excersize to the reader :-)
There is a post here that has some code, perhaps that helps:


Comment by: Bert (5/14/2018 2:11:43 PM)


This is a very nice treeview control.

I do run in one issue though. I'm adding childnodes by selecting the parent node and then using the .Addchild method. That works well until it can't find the parent node. Then I get an VBA error 5:Invalid procedure call or argument.
In this case I just want to ignore this, but how can I do this? I tried several things like:

Set cNode = .Nodes(rst!Parent)
if Not cNode is Nothing then
cChild = cNode.AddChild(sKey:=strKey, vCaption:=strCaption)
end if

But no matter what I try, I keep getting error 5 on line:

Set cNode = .Nodes(rst!Parent)

How can I check if a node exists before I try to use it?


Comment by: Jan Karel Pieterse (5/14/2018 3:46:57 PM)

The nodes are only accessible through their index, not through their name so you would have to enumerate the nodes and then check their Name to find the right node. Air code:

Function GetNode(sName As String)
    Dim oNode as Node
    For Each oNode in mcTree.Nodes
        If oNode.Caption = sName Then 'Or use .Key
            Set GetNode = oNode
            Exit for
        End If
End Function


Have a question, comment or suggestion? Then please use this form.

If your question is not directly related to this web page, but rather a more general "How do I do this" Excel question, then I advise you to ask your question here:

Please enter your name (required):

Your e-mail address (optional, will only be used to inform you when your comment is published or to respond to your question directly):

Your request or comment (max 2000 characters):

To post VBA code in your comment, use [VB] tags, like this: [VB]Code goes here[/VB].

I give permission to process this data and display my name and my comment on this website accoring to our Privacy Policy.