1.8 Resize
Home and Learn - Free Excel VBA Course
12.3 Initialize the Treeview Nodes
To add the parent nodes to our Treeview, we'll use the Initialize event of the User Form.
From the dropdown boxes at the top of the coding editor, then, select the UserForm item:
This will give you a click event, which you don't want. We need the Initialize event. From the dropdown on the right, select Initialize:
An Initialize event will be added to your coding window. You can delete the Click event, as you won't need it.
The first thing we can do in the Initialize event is to activate Sheet1. This is the sheet with all the Treeview data. If we don't make Sheet1 the active sheet then Treeview will try to pull its data from Sheet2, if that sheet is displayed in Excel. The code is only one line. This:
Worksheets("Sheet1").Activate
In between the round brackets of Worksheets, you can type the name of worksheet in between quote marks.
The next five lines get the parent nodes for the Treeview. Add the following:
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 1).Value, Text:=Sheet1.Cells(1,
1).Value
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 2).Value, Text:=Sheet1.Cells(1, 2).Value
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 3).Value, Text:=Sheet1.Cells(1, 3).Value
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 4).Value, Text:=Sheet1.Cells(1, 4).Value
Treeview1.Nodes.Add Key:=Sheet1.Cells(1, 5).Value, Text:=Sheet1.Cells(1, 5).Value
Your coding window should look like this:
Press F5 to run your form. You should find that the Treeview looks like this:
We now have five parent nodes. These parent nodes don't have any child nodes, yet, however. Let's do that now.
Add Child Nodes to the Treeview
Have a look again at the spreadsheet called Sheet1:
The countries we want to add as child nodes are all underneath the headings in row 1 of the spreadsheet. The number of items in each column are different. What we can do here is to get the last cell with data in it for each column. Then use a For Each loop to add the child nodes. We can do all this with one Subroutine.
Add the following Sub to your code:
Sub FillChildNodes( ByVal col As Integer, ByVal continent As String )
End Sub
We're going to be passing two things to this Sub called FillChildNodes: an integer value for the column number, and a string value for the continent. We're going to be using this string value with a counter to get a unique value for the child nodes.
First, add the code to get the last cell with data in it:
Dim LastRow As Long
With Sheet1
LastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
So if we pass in a value of 1 as the value for the col variable, then this line:
Cells(.Rows.Count, col).End(xlUp).Row
will read Cells(8, 1). This means that Excel has found 8 rows in column 1 with
data in them.
f we pass a value of 2, however, as the value for the col variable then the line would read Cells(7, 2), meaning Excel has found 7 rows in column 2 that have data in them.
In other words, we're getting the last row with data in it for a particular column. Which column this is will depend on the value we pass in when we call the Sub into action later.
The next thing we can do is to set up a counter. The counter will be used to set a unique value for each child node. Add the following lines to your code:
Dim counter As Integer
counter = 1
Your coding window should now look like this:
Add the Child Nodes
We can loop round and add the child nodes for the col variable we passed in. Add this For Each loop to your code:
For Each country In Range(Cells(2, col), Cells(LastRow, col))
Next country
The variable that's going to hold the countries from the cells on the spreadsheet is country. (We could have called this variable almost anything.) Have a look at this part, though:
Range(Cells(2, col), Cells(LastRow, col))
We want the range of cells that hold data. The first cell we want is on row 2. The column number is held in the col variable. That would get us something like Cells(2, 1) as the starting point for Range. The end point is the last row with data in it, Cells(LastRow, col), plus the column number held in the col variable.
If you look at Africa as an example, the For Each loop would read like this:
For Each country In Range( Cells(2, 1), Cells(8, 1))
This loops round every cell in column 1, the "A" column, and ends at the last row with data in it, which is row 8 for Africa.
To add child nodes to each parent, place these two line in your loop:
Treeview1.Nodes.Add Sheet1.Cells(1, col).Value, tvwChild,
continent + CStr(counter), country
counter = counter + 1
The first thing we need after Node.Add is the relative node. This is the parent node we added earlier, which is the name of the continents in the top row of the spreadsheet:
Sheet1.Cells(1, col).Value
Next, we need the relationship, which is the inbuilt constant tvwChild.
As a unique ID for the child node we're just combining the name of the continent with the counter:
continent + CStr(counter)
So the first time round the loop the ID will be "Africa1", then "Africa2", "Africa3", etc.
Finally, to add some text for the child nodes we can use whatever is in the country variable, which we're getting from the cells when we loop round.
Your Sub should look like this:
We now need to call this Sub into action.
Go back your form Initialize event. Underneath the parent nodes, add this line:
Call FillChildNodes(1, "Africa")
The first value we pass in is the number 1. This means column 1. As the second value passed in, the continent, we've typed "Africa". Instead, we could have done this:
Sheet1.Cells(1, 1).Value
This would pull the value from row 1, column 1 on the spreadsheet.
Now add calls for the other four continents:
Call FillChildNodes(2, "Americas")
Call FillChildNodes(3, "Asia")
Call FillChildNodes(4, "Australasia")
Call FillChildNodes(5, "Europe")
Your Initialize event should now look like this:
You can run your form now. Press F5 to launch it. Expand one of the parent nodes and you should see the child nodes have been added:
Now that we have child nodes, we need something to happen when we click on them. Specifically, we want to pull data from Sheet2 on the spreadsheet and place it in the labels and text box. We'll do that in the next part below.
Next Lesson: 12.4 Node Clicks >