Home and Learn - Free Excel VBA Course
Items on a Treeview are called Nodes. The top level nodes are said to be parent nodes. If a parent node has items of its own, these items are called child nodes.
To add parent nodes to a Treeview the syntax is:
TreeviewObject.Nodes.Add relative, relationship, key, text, image, selectedimage
First, you specify your Treeview object. You then use the Add subroutine of the Nodes object. After the Add subroutine, you can specify up to 6 optional arguments: relative, relationship, key, text, image, and selectedimage
The first two arguments are relative and relationship. If you're using the relative argument then you need to specify a unique value for each parent node that you want to add. The relationship can be one of 5 values: tvwFirst, tvwLast, tvwNext, tvwPrevious, tvwChild. We'll be using this last one, tvwChild, when we add a child node.
The next two arguments are key and text. You can use just these two arguments to set up a parent node. Like this:
Treeview1.Nodes.Add Key:="item 1",
Treeview1.Nodes.Add Key:="item 2", Text:=" Parent 2"
Treeview1.Nodes.Add Key:="item 3", Text:=" Parent 3"
Each key value above (item 1, item 2, item 3) is unique. The text can be whatever you want as the node's text.
To add a child node to these parents, you need to use the relative and relationship arguments:
Treeview1.Nodes.Add "item 1", tvwChild, "some unique key", "text value"
The relative argument is now one of those unique key values from the parent node (item 1, item 2, item 3). The relationship is tvwChild. This means, "Set up a child node for the parent node called item 1". This child node needs its own key and text value.
To clear all this up, have a look at the following code.
So we have three parent nodes. Each parent node has two child nodes. The parent node with the Key "item 1", for example, has two child nodes. The relative value for these child nodes comes first after Add: "item 1". The unique keys are Cstr(one) and CStr(two). This converts to a string the values one and two. (These are quite arbitrary values and we could have used almost anything as the unique keys.)
When this code is run, the Treeview will look like this:
The parent nodes for our Treeview are all continents: Africa, Americas, Asia, Australasia, and Europe. We could set up the parent nodes like this, then:
Treeview1.Nodes.Add Key:="Africa", Text:="Africa"
Treeview1.Nodes.Add Key:="Americas", Text:="Americas"
Treeview1.Nodes.Add Key:="Asia", Text:="Asia"
Treeview1.Nodes.Add Key:="Australasia", Text:="Australasia"
Treeview1.Nodes.Add Key:="Europe", Text:="Europe"
Here, the unique key is the same as the text value. Which is perfectly fine.
Instead of hard-coding the values, however, we can get them from the spreadsheet, Sheet 1. Have a look at this and you'll see that the continents are all in row 1 of Sheet1:
To get the value of Africa, for example, the code would be this:
This gets the value of the Cell in Row 1, Column1.
We can use this code as the Key and the Text for our Treeview:
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1, 1).Value
To get the other continents, we only need to change the number for the column between the round brackets of Cells:
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 2).Value, Text:=Sheet1.Cells(1, 2).Value
We're now referring to column 2, but still pointing to row 1.
In the next part, we'll add code for the Initialize event of a user form.