The Node Click Event

To detect if a parent or child node was clicked on, there is inbuilt event called NodeClick . To access it, from the dropdown boxes at the top of the coding editor, select the name of your TreeView, which is Treeview1 for us:

From the dropdown on the right, select NodeClick :

The NodeClick event will look like this (two lines of code):

Private Sub Treeview1_NodeClick( ByVal Node As MSComctlLib.Node )

End Sub

Notice that between the round brackets of the NodeClick event, there is an object variable called Node. This object variable has properties of its own that you can access:

Node.Key
Node.Text

We can use the Key properties to detect if a parent node is selected:

If Node.Key = "Africa" Then

MsgBox "Parent Node"

End if

We only have five parent nodes so we can create a long line of Or clauses:

If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key = "Asia" Or Node.Key = "Australasia" Or Node.Key = "Europe" Then

MsgBox "Parent Node"

End if

If the node key is anything else then it must be a child node:

If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key = "Asia" Or Node.Key = "Australasia" Or Node.Key = "Europe" Then

MsgBox "Parent Node"

Else

MsgBox "Child Node"

End if

The child node that has been clicked can be retrieved using the Text property of the Node object:

MsgBox "Child Node is: " & Node.Text

If you have a look at the data in Sheet2 of your spreadsheet, you'll see that the countries are all in the "A" column. We can loop round this column and check if the value in the cell matches Node.Text . In other words, Check the text from the Node that was clicked and see if matches a value from the "A" column. If it does, then we can use Offset to get the information from that row. Here's the full code to add to your NodeClick event:

If Node.Key = "Africa" Or Node.Key = "Americas" Or Node.Key = "Asia" Or Node.Key = "Australasia" Or Node.Key = "Europe" Then

Else

Dim LastRowID As Long

LastRowID = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

For Each ID In Range(Sheet2.Cells(2, 1), Sheet2.Cells(LastRowID, "A"))

If ID.Value = Node.Text Then

Label2.Caption = ID.Offset(, 1).Value
Label4.Caption = ID.Offset(, 2).Value
Label6.Caption = ID.Offset(, 3).Value
Label8.Caption = ID.Offset(, 4).Value

TextBox1.Text = ID.Offset(, 5).Value

End If

Next ID

End If

 And here's what your coding window should look like (we've used the underscore character to break up that long first line):

For the first part of the If statement, we don't do anything. That's because it will be a parent node, and we haven't got any code to execute if a parent node is clicked.

The code for the Else part of the If statement, however, handles the child node clicks. First, we get the last row for column "A" that has data in it:

LastRowID = Sheet2.Cells(Sheet2.Rows.Count, "A").End(xlUp).Row

We can then use that LastRowID in the For Each loop, as we did before (two lines):

For Each ID In Range(Sheet2.Cells(2, 1), Sheet2.Cells(LastRowID, "A"))

Next ID

Instead of the variable being called country, as in previous For Each loop, it is now called ID. We then check to see if the value of ID matches Node.Text:

If ID.Value = Node.Text Then

End If

If it does, then we've found a row of data that matches the node that was selected. We can then grab data from Sheet2 using Offset:

Label2.Caption = ID.Offset(, 1).Value
Label4.Caption = ID.Offset(, 2).Value
Label6.Caption = ID.Offset(, 3).Value
Label8.Caption = ID.Offset(, 4).Value
TextBox1.Text = ID.Offset(, 5).Value

And that's it! Try it out. Run your form and select a child node. You should find that the information for that entry on Sheet2 appears in your labels and text box:

As an exercise, exit your form. Return to your spreadsheet. Enter a new country on Sheet1. On Sheet2, enter some data for that country. Run your form again and you should find that your new country appears on the Treeview. When you click the country, its data from Sheet2 should appear in the labels and text box.

And that’s it for this Excel VBA course. Hope you enjoyed it!