• Using a Treeview Control

    This is a tutorial on the creation of a simple Treeview control on an Excel userform. For the purposes of this example, we'll be populating the Treeview control with worksheet names and the addresses of cells with formulas in them.

    The article is intended for intermediate level VBA coders, meaning that you should be comfortable with navigating the Visual Basic Editor (VBE), and preferably have created a least a few userforms in your experience. With the exception of Treeview specific items, I will not be covering terminology or explaining things in great detail, so if you're a beginner, you may need some additional help to follow all the steps.

    For ease of use, I have just used all the default object names that I've created for the article, but feel free to rename them in your own instance. Just remember to update the code examples to reflect the names of any controls that you do this for.

    Creating the underlying structure:
    To follow along with the example:
    • Create a fresh new workbook
    • Add a few formulas to worksheets 1 and 2. (It doesn't matter what they are, just something that is a formula)
    • Enter the VBE
    • Add a new userform to your workbook (we'll come back to this)
    • Add a new blank code module, and place the following code in it (which will be used to launch the userform):
      Code:
      Sub ufLaunch()
           'Launch the userform
           UserForm1.Show
      End Sub

    Add the Treeview control to your controls toolbox:
    • Right click the Toolbox and choose "Additional Controls" (as shown below)
    • Scroll down the list until you find the "Microsoft Treeview Control, version x.x" (Mine was version 6.0, but yours may be different)
    • You should now have a Treeview control on your toolbox:

      NOTE: The Treeview control will stay on your toolbox until you remove it

    Set up the userform:
    • Right click the add the following controls:
      • TreeView
      • Label
      • CommandButton

      Your userform should now look approximately like this:

    Add the code to make things work:
    Each of the following routines will go in the userform module, and will be discussed at the end.
    Code:
    Private Sub UserForm_Initialize()
    'Author : Ken Puls (www.excelguru.ca)
    'Purpose: Load userform with desired defaults
         'Set control defaults
         With Me
              .CommandButton1.Caption = "Close"
              .Label1 = vbNullString
              .TreeView1.LineStyle = tvwRootLines
         End With
         'Populate the Treeview
         Call TreeView_Populate
    End Sub
    
    Private Sub TreeView_Populate()
    'Author : Ken Puls (www.excelguru.ca)
    'Purpose: Populate the treeview control
         Dim ws As Worksheet
         Dim rngFormula As Range
         Dim rngFormulas As Range
    
         With Me.TreeView1.Nodes
              'Clear TreeView control
              .Clear
    
              For Each ws In ActiveWorkbook.Worksheets
                   'Add worksheet nodes
                   .Add Key:=ws.Name, Text:=ws.Name
    
                   'Check if any formulas in worksheet
                   On Error Resume Next
                   Set rngFormulas = ws.Cells.SpecialCells(xlCellTypeFormulas)
                   On Error GoTo 0
    
                   'Add formula cells
                   If Not rngFormulas Is Nothing Then
                        For Each rngFormula In rngFormulas
                             .Add relative:=ws.Name, _
                                  relationship:=tvwChild, _
                                  Key:=ws.Name & "," & rngFormula.Address, _
                                  Text:="Range " & rngFormula.Address
                        Next rngFormula
                   End If
    
                   'Release the range for next iteration
                   Set rngFormulas = Nothing
              Next ws
         End With
    End Sub
    
    Private Sub Treeview1_NodeClick(ByVal Node As MSComctlLib.Node)
    'Author : Ken Puls (www.excelguru.ca)
    'Purpose: Write the selected node to a label for later use
         Me.Label1.Caption = Node.Key
    End Sub
    
    Private Sub CommandButton1_Click()
    'Author : Ken Puls (www.excelguru.ca)
    'Purpose: Unload the userform
         Unload Me
    End Sub
    The end result:
    Save the project and go back to the Excel interface. Press Alt+F8 and run the "ufLaunch" macro to display the userform. As long as you put some formulas in your worksheets, those sheets should have a little + sign beside them, so click to expand them. Click on the Sheet names or the range names, and watch the label in the bottom left of the userform show what node you've selected. A screenshot of the finished form is shown below:
    The code explained:
    The userform makes use of four routines, which are broken down as follows:

    Private Sub UserForm_Initialize()
    The purpose of this routine is to set the desired defaults on the userform when it's loaded. While I would normally set the default values of my controls in design view, I chose to go this route to allow an explanation with just the drag and drop of standard controls. This routine sets each controls defaults, then calls the Treeview_Populate routine to fill the treeview control.

    While there isn't much mystery in the above, there is one line in this routine worth nothing, and that is the one that sets the Treeview's LineStyle property to 'tvwRootLines'. This property is be default set to 'tvwTreeLines' when you drop the control on the userform. In that state, however, we do not get the +/- indicators, which means that you have to double click a node to get it to expand or collapse. Personally, I find the +/- view much more intuitive, as the user does not need to double click the node to know if there is anything in it. (They can still double click to expand or collapse the node if they want to though.)

    Private Sub TreeView_Populate()
    This routine actually populates all the nodes of the Treeview; in this case with the name of each worksheet in the workbook and the ranges that contain a formula.
    The clearing of the control and looping through sheets and ranges should be self explanatory, so I won't explore them in detail, but I do want to look at the method to add Treeview nodes.

    When adding nodes to the Treeview, we need to create a parent/child relationship. In this scenario, each Sheet is a parent, and the ranges are the children that are spawned from the parents. (You could take this to a further level, by making the actual formula, value and fonts children of the ranges as well if you chose. In that case, the range would be the child of the sheet, but the parent of the other data under it.)

    To add a top level parent node, we need to assign both a Key and some Text to the node. The key must be unique, and the text is what shows up in the Treeview on the userform. In the example above, the worksheets are added to the treeview using the following code:
    Code:
    .Add Key:=ws.Name, Text:=ws.Name
    i.e. Me.TreeView1.Nodes.Add Key:="Sheet1", Text:="Sheet1"

    Every time you want to add a top level node to the userform (Sheet1, Sheet2 and Sheet3 in the example), you'd use the coding construct above.
    To add child nodes, the procedure is slightly different. We still use the add method, and we still need a unique key, but we also have to provide the key of the parent node (relative) and the relationship (tvwChild). In the example, the code to add the children nodes (edited for line breaks) looks like this:
    Code:
    .Add relative:=ws.Name, relationship:=tvwChild, _
    Key:=ws.Name & "," & rngFormula.Address, Text:="Range " & rngFormula.Address
    i.e. Me.TreeView1.Nodes.Add relative:= "Sheet1", relationship:=tvwChild Key:="Sheet1,$C$8", Text:="Rage $C$8"

    So to link the child node under the parent node, the 'relative' must be set equal to the parent node's 'key', and the relationship must be set to 'tvwchild'. To make the child node valid, it must also have it's own key that is unique from any other in the treeview (not just the other children). What I did here was set up a compound key that is made up of the parent's key and the current range, separated by a comma. Why? In a project I built with three parent/child layers (Sheet,Range,Data), I was easily able to split it up and see do what I wanted. For example, if I wanted to activate the selected range, I split the label's contents into an array, use the individual components to activate the range i want. An example of this is shown in the Supplementary Information section at the end of the article.

    Private Sub Treeview1_NodeClick(ByVal Node As MSComctlLib.Node)
    The purpose of this routine is to capture the treeview node's click event and write the key of the clicked node to our label for later use. I set the label1 control to 'vbNullstring' when the form was loaded, to ensure that the label is blank if no control is selected.

    Private Sub CommandButton1_Click()
    This command button simply unloads the userform.

    Conclusion:
    Once understood, the Treeview control is actually quite easy to set up and use. While this example has focussed on using the treeview control in an Excel userform, it could just as easily be applied in an Access, Word or Powerpoint solution, or any other application that supports the use of VBA. (In fact, this control can actually be accessed from other languages as well.)

    Supplementary Information
    Adding images to the Treeview:
    The treeview control is used in a great many places in Microsoft Windows, and many times has images associated with it. Just one example of this is the capture below from the windows explorer:
    Like the picture above, you can add images to your treeview control, and my friend Colo has a great page on doing that here.

    Using the Treeview form to go to the selected range:
    The following routine would add additional functionality to our example, by taking the user to the selected worksheet or range. It also goes in the userform module of our example, but you will need to add a new button to trigger it:
    Code:
    Private Sub CommandButton2_Click()
    'Author : Ken Puls (www.excelguru.ca)
    'Purpose: Go to the selected node
         Dim sNodes() As String
    
         'Check if a node has been selected
         If Me.lblNode.Caption = vbNullString Then
              MsgBox "You have not selected anything!" & vbNewLine & _
                     "Please select something and try again.", vbCritical + vbOKOnly, _
                     "Nothing selected"
              Exit Sub
         End If
    
         'Split label into portions
         sNodes = Split(Me.lblNode, ",")
    
         With Worksheets(sNodes(0))
              'Activate the worksheet
              .Activate
    
              'Activate the appropriate range
              If UBound(sNodes) + 1 > 1 Then
                   'Activate selected range
                   .Range(sNodes(1)).Activate
              Else
                   'Worksheet selected, activate "A1"
                   .Range("A1").Activate
              End If
         End With
    
         'Unload the userform
         Unload Me
    End Sub
    Syndication:
    This article has also been published at Professional Office Developers Association.

     

    I'm afraid that you must be logged in to comment or leave a testimonial. I wish it could be otherwise, but I'm trying to keep my site spam free for everyone's benefit. If you don't yet have an account it's completely free to sign up, and a very quick process. Simply click here to Register. Not only can you post a comment here, but it gives you full access to posts questions in our forum as well!

     

    If you already have an account, and just haven't logged in yet, what are you waiting for? Login Now!

  • MVP Logo
  • Recent Forum Posts

    abbasi

    Clearing formats doesn't clear the background color

    Hi all,

    This is quote from the "Microsoft Excel 2010 by Jahn Walkenbach" book:

    "Clearing formats doesn't clear...

    abbasi Today, 03:31 PM Go to last post
    abbasi

    Label

    Hi all,

    I have heard much about labels but I don't know what those mean or how to make a cell as label.
    Is there anyone give some info...

    abbasi Today, 02:39 PM Go to last post
    dhazi

    modelling cycles

    The exercise is to model a recurring cost cycle. In this example there are some light bulbs need replacing periodically based on their usage. I cannot...

    dhazi Today, 01:38 PM Go to last post
    JOONA

    Hyper Link

    Hi Here is the sperad sheet if anyone needs it...

    JOONA Today, 09:31 AM Go to last post
    JOONA

    Advise

    Here is the spreadsheet....

    JOONA Today, 09:30 AM Go to last post