1.8 Resize
Home and Learn - Free Excel VBA Course
12.1 The VBA Treeview Control
In this section, you're going to be learning about the Treeview control in Excel VBA. You'll design a small VBA form like this one:
The Treeview on the left of the form has five headings. Each heading is called a parent node. Click the plus symbol next to a parent node and you'll see other headings appear:
Because these headings are underneath a parent node they are called child nodes.
What we'll do is to pull information from a spreadsheet when a child node is clicked. This information will appear in the labels and textboxes on the right of the form:
In the form above, Botswana, a child node of the parent node Africa, has been selected. The data for Botswana, therefore, appears on the form.
To follow along with the Treeview tutorial, we have a spreadsheet for you. Download the file below:
DOWNLOAD SPREASHEET (Right Click, Save As).
Once you have downloaded the spreadsheet, open it up. Now save it as a macro enabled workbook (XLSM file), so that you can do some coding with it.
The workbook consists of two sheets. The first sheet is the country data for the Treeview. (Many apologies if your country is not on the list - no slight is intended!) The second sheet contains the information we want to appear on the form whenever a child node is clicked.
With that in mind, let's add a User Form to the project.
Adding the User Form
Click on the Developer ribbon at the top of Excel. Now click on Visual Basic in the Coding panel to open up the VBA Editor.
When your editor opens, click on Insert > User Form from the menu bar at the top.
In the properties panel for the new form, change the Name property to Nations. Locate the Caption property and change it to Nations.
In the VBA toolbox locate the Treeview control:
Draw a Treeview onto your form. Draw it on the left, and then use the resizing handles to make it a decent height. Make it wide enough for some text, but not too wide.
With your Treeview selected, change the following two properties:
LineStyle: tvwRootLines
Style: tvwTreeLinesPlusMinusText
You Treeview should look like this when you're done:
Notice that the default parent and child nodes just say "Sample Node". We'll change these defaults with code.
Add 5 labels to the right of your Treeview. Change the Caption properties to: Populations (in millions), Life expectancy, Currency, Capital, and Fun Facts. Add four more labels to the right of the first four labels. You can change the style of your labels. Experiment with the following properties of labels: BorderStyle, BorderColor, BackColor, Font.
Now add a textbox just below the Fun Facts label. Just below the textbox, add a Command Button. Change the Caption property to Exit.
Your form in design view should look something like ours below
The first thing we can do is to code for the Command Button. Double click your Exit button to open up its code stub. Now add the following line:
The Me keyword refers to the current form. When this line is executed, the form will close down.
In the next part below, you'll learn about coding for the Nodes in the Treeview.
Next Lesson: Treeview Nodes >