1.8 Resize
Home and Learn - Free Excel VBA Course
12.4 The Treeview 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:
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:
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. If you want the book of the course, click the link at the top of the page. Thanks for following along!