Skip to main content

TreeView Structure in Excel VBA

TreeView is extreamly useful in specific cases but it can be bit tricky sometimes to implement. 

Last Few days I was working on a TreeView Structure and thought I will share the knowledge I gained... 

This post takes you through the basic operations to create and operate a TreeView. It will be like the Folder tree window of the windows explorer.  Moreover, you can have it dynamically updated based on the data in excel....

Here we go..

Idea was to populate the tree completly on the fly from the data defined in the sheet. change the structure and content in the sheet and the TreeView gets updated automatically...


1. Get the additional control for the TreeView: 

Get to the VBA Window (Press <ALT+F11>, 
Right Click on the ToolBox Window and 
Select Additional Controls... 
Browse through the window and 
select "Microsoft TreeView Control ...." and "Microsoft ImageList Control ....". 

Now these two items would have come in your TooBox. 
Select and place it on the Form and start working. 

2. Name the Tree: 

I have changed the name of the tree to TV from the default TreeView1. 
Hereon, I will use TV for that item. 

3. Build the Tree up: 

You build tree by adding nodes. You can add a node as a Child or a Parent etc.. 
I like to use the relationship - Child. Basically, each node has a 
..... Key (like the name of controls), 
..... Text (like the Caption of controls), 
..... Index (the ListIndex of a ListBox) 

3.1 Adding Node 

You create each node in relation to another. 
TV.Nodes.Add(Relative:="ThekeyofParent", Relationship:=tvwChild, Key:="NewKey", Text:="New Text") 

The base Node can be added as.. 
TV.Nodes.Add( , , "Key001", "The Base") 

The Next Node can be added as.. 
TV.Nodes.Add("Key001", tvwChild, "Key011", "The First Child") 

3.2 Removing Node 

Remove the Node by refering to the Key or the Index of the item. 
TV.Nodes.Remove(Key) TV.Nodes.Remove

3.3 Clear the Nodes 

4. Usual properties of Nodes: 

You can refer to a node with its Key or Index. 

..... Key   > TV.Nodes(i).Key 
..... Text   > TV.Nodes(i).Text  or simply  TV.Nodes(i) 
..... Index > TV.Nodes(key).Index 

' Gives the Key of the Node. TV.Nodes(key).Index 
' Gives the Index of the the Node. TV.Nodes(key).children 
' Gives the No of children it has. etc... 

 Its safe to use Int(i) and str(Key) when you are using other variable to refer to index or key. 

5. Nodes Handling: 

Defining the variable for assigning Nodes 
Dim xNode As MSComctlLib.Node 

Usual ways of assigning a node to the variable.. 
Set xNode = TV.Nodes(i) 
Set xNode = TV.Nodes(key) 
Set xNode = TV.SelectedItem 

Get a parent Node or Child Node and cycle through the Nodes... 
To assign xNode to the Parent of the selected item.. 
Set xNode = TV.SelectedItem.Parent 

To assign xNode to the First Child of the selected item.. 
Set xNode = TV.SelectedItem.Child 

To assign xNode to the Parent of the item.. 
Set xNode = xNode.Parent 

To assign xNode to the First Child of the item.. 
Set xNode = xNode.Child 

To assign xNode to the Next item of the same level and same parent 
Set xNode = xNode.Next 

To assign xNode to the First item of the same level and same parent 
Set xNode = xNode.FirstSibling 

To assign xNode to the Last item of the same level and same parent 
Set xNode = xNode.LastSibling 

Remember to Check the node existance before start using it.. 

Check if Node is selected or assigned properly.. 
If Node Is Nothing Then 
   MsgBox "Node not Selected or nonexistant!" 
   Exit Sub 
End If 


Anonymous said…
Thanks. And the key cannot start with numeric characters.
Joe said…
Thats true.
Maybe you can also use something like the following to convert the number to text..

"Key" + Format(Key, "00")
excel said…
you helped me a lot. thank you very much.
PArgyropoulos said…
how can i explode all the nodes, before showing the treeview object?
Joe said…
May be you try not to refresh screen until all nodes are exploded.. I could not test this or other possibilities though!
hi joe,

I have got 2 different worksheets, the first one contains data in tree view. What I need to do is to populate the other sheet, according the node. Like if i enter the value of node, then it should populate different columns with the relevant Parent`s value.
Can you help me in it?

Joe said…
@ Ankur Kumar Shah
you let me know how far you have reached.


Popular posts from this blog

Controlling LED through Octolapse for 3D print timelapse

Today's post is about one of the 3D Printing adaptation that I learned. This is specifically about how to control an LED light automatically through OctoLapse so that it is switched on only when the timelapse photo is taken. Just as a background, I have been learning to use 3D printer and its customizations options for a few months now and was a lot of learning since then. The best thing was OctoPi, a platform for controlling the 3D printer and the many plugin options that are developed by the community.  Most of them are like open source. Today we will discuss specifically about an adaptation to one such plugin, OctoLapse. OctoLapse is the plugin for taking timelapse photos, with many options for further customisations. One of the best timelapse method is taking photo after each layer is complete, by moving the head out of the way. You can use standard Pi Camera or a DSLR, which is great to get good resolution videos. One small flash back before we dive into the topic.  I keep the

PTC Creo | my Mapkeys for free

I have created a list of frequently used mapkey shortcuts for the PTC Pro Engineer Creo. This is the macro equivalent in creo.  I am copying them below.If you need them, copy paste the content to the "" file in your startup folder. My favourites are highlited and greatly improves the workflow.. For ex, to reach MEASURE. need to go to another menu and click.  Instead, maypkey from any selected menu on the ribbon will work.. Thats wonderful to me... Also, Edit Sketch (ES) is overloaded and will work for Extrusion, Revelution, Sweep etc.. So is aa, pp, zz..  really helps me a lot.. Hope you will start using them as well and get benefited! Let me know in comments, your feedback and issues.... Sketch View           > sv Show and Erase        > se Working Directory     > wd Hiddel Line View      > hi Close (quit) Window   > qw Measure               > mm Erase Session         > ee Insert Mode @ Sel     > is Cancel Insert Mode    > ic edit Sketch