Tree List

Create a Tree View to Expand & Collapse List Items in Excel 2013

Version 3.1
25Mar 2017

Tree List Documentation

By: Mark Kubiszyn
Tree List Design Excel Screenshot

Tree List Template Excel Screenshot

Tree List Options Excel Screenshot


Tree List uses hierarchical data to create lists of Items in an Excel Worksheet that can expanded and collapsed at Parent Nodes. When the Nodes are expanded or collapsed any descendants that are already expanded or collapsed persist. The finished list can be copied into a new Workbook or an existing Project - all of the Code is generated in a self-contained "Template" Worksheet Code Module.

Tree List can be used to create many kinds of lists, including a Hyperlink Index to other Worksheets or Web Pages, a T.O.C. or an Options / Settings list. Tree List is highly configurable. You can change the position of the Tree List, change the Node Type and Colours, choose whether or not to draw the Node hairlines, choose whether or not to add the Buttons and Code to expand or collapse all of the Items at once and much more... Check out the features below or download some of the free examples that have been created using Tree List.

FEATURES

- Create an expandable and collapsible Tree List in an Excel Worksheet with associated VBA Code from a list of Items
- The Tree List "Template" Worksheet can be renamed and copied into a new or existing Workbook
- Use Text, Dates, Numbers, Hyperlinks or Styles to format the Tree List Items
- Change Standard Node Icons to Warning & File Icons (more can easily be added...)
- Create the Tree output to start at any Row or Column
- Set the Tree List Column & Row Widths
- Set the Tree List Background Colour (the Icons are transparent .PNG's and look just as good on a light Coloured Background as they do on a White Background)
- Choose whether or not to draw the Node Hairlines
- Choose the Node Hairline Colour
- Choose the Expandable & Collapsable Node Types, either Folders, Squares or Triangles
- If using Squares or Triangles, you can choose the Expand & Collapse Colours for these Shapes
- Choose Row Banding and set the Banding Colour
- Set the Node Item Merge Columns Width between 1 & 99 Columns
- Set the Node Item Font Size between 0 & 409 points
- Set the Node Horizontal & Vertical Text alignment
- Set the Node Wrap Text and Orientation
- Add a Node Text indent or set the Node Text to Shrink to Fit
- Collapsed Tree Nodes persist until they are expanded through the use of Defined Names
- Tree Node & Folder Icons visually indicate collapsed or expanded Item
- Options are included to write out the VBA Code to collapse and expand all of the items at once
- After Copying to an new Workbook the "Template" Worksheet can be renamed to any valid Name


REQUIRED

Excel 2010 or Excel 2013 (32bit or 64bit), working with Windows 7 & 10
Please Note: the Code works with Excel 2010, however there is a flicker when selecting Nodes - please test the examples before purchasing if you are using Excel 2010

HOW THE CODE WORKS

The Code works by allowing multiple Icons or Shapes to be passed into a Select Case routine. The Icons that can be expanded and collapsed are given a 'Hashed' unique identifier to avoid collisions and sat ontop of one another. Icons are layered upon each other - both Node Icons and the Folder Icons that may be toggled (the Parent Nodes). The Icon sitting behind the first Icon is given the sufix of 'X' to allow identification. The unique identifier allows me to pass in multiple Icon Names into a single routine through the use of a Const Variable which includes a fuzzy '*' character. The use of 'Application.Caller Like' returns a Boolean TRUE value if the Icon Name is like the Const Variable Name and enters the Code for that part of the Select routine. This allows me to cut down on the amount of Code generated on-the-fly for the "Template" Code Module but the downside is that all Icon Names must be unique. Parent Icons are attached to the Code having the 'Template.TreeUI' Subroutine as their Macro. The Standard Nodes are attached to the Code routine but no Code will fire. An example of the Code I use is shown below, but you can download any of the examples and take a peek behind the scenes. The Code also uses multiple 'IIf' routines to toggle visiblity settings and Defined Names values on or off accordingly. The use of Defined Names for the Node status allows me to persist Icons in whatever state they have been before a higher branch is expanded or collapsed. A routine in the Code loops these settings to expand or collapse all of the Nodes.

' // Tree List Node constants
Const NODE_20501591 As String = "NODE_20501591*"

' // begin Case Select
Select Case True

' // Node like: NODE_20501591
Case Application.Caller Like NODE_20501591
Rows(Template.Range("NODE_20501591").Address).EntireRow.Hidden = _
IIf(Rows(Template.Range("NODE_20501591").Address).EntireRow.Hidden = False, True, False)
Template.Shapes("NODE_20501591").Fill.Visible = _
IIf(Template.Shapes("NODE_20501591").Fill.Visible = msoTrue, msoFalse, msoTrue)
Template.Shapes("FOLDER_20501591X").Fill.Visible = _
IIf(Template.Shapes("FOLDER_20501591X").Fill.Visible = msoTrue, msoFalse, msoTrue)
Template.Shapes("FOLDER_20501591").Fill.Visible = _
IIf(Template.Shapes("FOLDER_20501591").Fill.Visible = msoTrue, msoFalse, msoTrue)
Template.Names("NODE_20501591_STATUS").Value = _
IIf(Right(Template.Names("NODE_20501591_STATUS"), 1) = False, "=1", "=0")
 


THE EXAMPLES

In order to see if Tree List can do what you would want it to, I have created some Demo Workbooks for you to download and play about with. Remember you can set the output of the tree List to fit your own needs. If you like what you see, please Purchase the Software from the link above. Here are the Demos all created with Tree List in no particular order of relevance:

Folders: Expandable & Collapsible Folders Demo

This is a simple list using the Folders Icons which change from open to closed depending on whether or not the Parent Node is expanded or collapsed (see 'Parent 4' and 'Child 4'). It also demonstrates how you can change the Standard Node Icons by setting the 'Grandchildren' to the '{Yellow File}' Icon. This is accomplished by adding the tag '{Yellow File}' to the Node Item Name at the design stage when using Tree List ie. 'Grandchild 1 {Yellow File}'. When the Code encounters any of the tags, it removes them for the Node Item Name and then copies over the corresponding Icon. In this Demo I copied out the "Template" Worksheet and then left the Name of the Worksheet intact - you can test this by changing the Worksheet Tab to any valid Name and expand or collapse the Icons. The Folders Demo includes the smaller ExpandAll() and CollapseAll() Buttons. Press ALT+F11 to view the Code.

Tree List Folders Demo

Counties of the United Kingdom: Counties of the United Kingdom Demo

In this Demo I downloaded a list of all of the Counties of the United Kingdom and formatted them as a heirarchal list. I chose to use Square Nodes without drawing the Node Hairlines. When I had generated the Tree List, I then formatted the outside Borders myself using the Format Painter. I changed the Worksheet Background Colour to a light Grey - you can do this manually or on the "Options" Worksheet. As the list is only 2 levels I think this style looks nice and the Code is fast at drawing the Icons as you expand or collapse all of the Items (there are a fair number of Counties in England and Scotland).

Counties of the United Kingdom Demo

Dashboard Hyperlinks: Dashboard Hyperlinks Demo

In this Demo I have used Hyperlinks to link the Nodes to 'Places in this Document' with one Node linking externally to my Website. After generating the Tree List, I copied out both the "Template" Worksheet and the "Examples" Worksheet which included the existing Hyperlinks to Cells from the Hyperlink Node Items into the "Examples" Worksheet. The Node Items also use a 'Node' Style which is a Custom Style included with the Tree List Software. In this Demo the Hyperlinks do not change Colour to be 'Followed Hyperlinks' and remain as a modified Hyperlink Style similar to the Node Style but in Blue Font. I changed the Squares Colours to Yellow and Green.

Dashboard Hyperlinks Demo

Items: Items Demo

For this Demo I use up to 3 levels. I changed the Square Node Colours to Orange and Light Grey. I chose not to draw the Hairlines and again formatted the surrounding Borders to give a nice effect. I renamed the Worksheet to 'Items Example'.

Items Demo

Triangles: Triangles Demo

In this Demo I set the Node Type to Triangles and then set the Use Row Banding to TRUE. I changed the Triangles Colour to 2 shades of Blue.

Triangles Demo

Deeper Levels: Deeper Levels Demo

In this Demo I went a little deeper with the Node levels - up to 9 levels. Despite having 16 Toggle Nodes the Code is very fast with no lag or flicker at all in Excel 2013 (in Excel 2010 you get a flicker as the Nodes are traversed). Node Icons do rely on Item Names as you can see from the image below - here I have used the same Item Name 'Node' for all of the Items. Node and Folder Icons are Hashed to create unique Codes to avoid collision. I use this technique because I allow multiple Icons to be detected with Case Statements using a 'Fuzzy' Like statement which cuts down on the amount of Code that is required to be generated for each Tree List.

Deeper Levels Demo

Complex 64 Nodes: Complex 64 Nodes Demo

In this Demo I went crazy with 64 Nodes - check it out...

Complex Demo

Project Timeline with Checkboxes: Project Timeline with Checkboxes

Using Defined Names, you can easily setup a Tree List to use a Checkbox (a Node Item shown as merged Cells). In this Demo I formatted the Checkboxes and then added Code to allow the Checkboxes to be toggled on/off whenever they are double-clicked. I then added a simple piece of Code to demonstrate how to tell if an Checkbox is set to on/off. I selected all of the Checkbox Cells and added the Defined Name "Tree.Checkboxes" so that I could detect the double-click. I then added the Defined Name "Tree.Checkbox.CustomerValidation" for the 'Validation with Customers' Checkbox.

Checkboxes Demo

Here is the Code to detect the Checkbox double-clicks and to see if the 'Validation with Customers' Checkbox is set on/off:

' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
' ## Worksheet_BeforeDoubleClick, used to toggle Option checkboxes on/off (the font used is Marlett)
' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' // Checkbox Range, use any Range or a Defined Name for the Range
    '    here I used the Defined Name "Tree.Checkboxes".  notice how we refer to the
    '    first Cell in the Merged Cell Range
    If Not Intersect(Target, Range("Tree.Checkboxes")) Is Nothing Then
        ' // set to "a" := On (Tick)
        '    - Cell was blank prior to a double-click
        If Target(1, 1).Value <> "a" Then
            Target(1, 1).Value = "a"
            Cancel = True
            Exit Sub
        End If

        ' // set to "" := Off (Blank)
        '    - Cell was "a" prior to a double-click
        If Target(1, 1).Value = "a" Then
            ' // use the entire Target Range when clearing otherwise you will get an error
            Target.ClearContents
            Cancel = True
            Exit Sub
        End If
    End If

End Sub

' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
' ## CheckboxStatus, used to see if the Checkbox is set on/off
' /////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Public Sub CheckboxStatus()

    ' // use a Defined Name to check if the Option is set or not ie. on/off
    If Range("Tree.Checkbox.CustomerValidation").Value2 = "a" Then MsgBox "on" Else MsgBox "off"

End Sub
 


THE TREELIST WORKSHEET

The "TreeList" Worksheet is used to design the Tree List. Enter your own data or Copy and Paste one of the examples from the "Examples" Worksheet into Cell "F11" in the "TreeList" Worksheet. Press the 'Create Tree' Button to generate the Tree List in the "Template" Worksheet. You must enter at least 2 Items to generate a Tree List. You must enter the data in a hierarchal format. You can change the Icons for the Standard Nodes by adding a Tag into the Item Name ie. 'Node1 {Blue File}' - this will use the 'Blue File' Icon. The Tag '{Blue File}' will be removed from the Item Text when the Tree is generated.

The Icons at the top of the Worksheet are used by the system. You can change the Folder Icons to your own Icons ie. an open Icon and a closed Icon. You can modify the Shape of the other Icons. It is advised that you back up Tree List before you mess about changing the Icons in case you wish to revert back to the original Software Icon setup. In the image below you can see that I changed the Square Shapes to Hearts.

Change Square Shapes to Hearts Demo

THE TEMPLATE WORKSHEET

The "Template" Worksheet is used to output the finished Tree List. You can adjust the Row and Column that you want the Tree List to be output in the "Options" Worksheet. Once the Tree List has been created the Worksheet Code Module contains all of the Code neccessary to enable the Node Items to be expanded and collapsed. You can Copy the Worksheet out to a new Workbook or into an existing Workbook. All of the examples above were simply copied out of the TreeList File. Once you have copied out the "Template" Worksheet, you can rename the Worksheet itself to any valid Name.

THE OPTIONS WORKSHEET

The "Options" Worksheet is used to set or edit any available options for the Tree List. Options can be expanded or collapsed using the Accordion Buttons. Option validation is performed in the Code using Regular Expressions (RegEx). Available options are:

Tree List

Tree List Row Number Output
- sets the Row position where the first Tree List Item is rendered in the "Template" Worksheet, default = 6

Tree List Column Number Output
- sets the Column position where the first Tree List Item is rendered in the "Template" Worksheet, default = 6

Tree List Column Width
- sets the Tree List Column Width - Tip: it is better to keep this small & increase the Nodes->Merge Columns Width, must be between 1.00 and 9.99, default = 1.43

Tree List Row Height
- sets the Tree List Row Height - Tip: remember this is half of a normal Column Width (making this too large will move the hairlines aways from the Node Icons), must be between 7.5 and 8.5, default = 7.75

Tree List Background Colour
- sets the Tree List Background Colour whilst still allowing Styles to be used, default is White, Tip: highlight the Tree post-creation when using Styles if you want the Background Colour to persist

Create ExpandAll() Code Subroutine
- creates an additional Code Subroutine to exapnd all of the Tree List Items, default TRUE

Create CollapseAll() Code Suroutine
- creates an additional Code Subroutine to collapse all of the Tree List Items, default TRUE

Create Buttons for Code Subroutines
- creates the Buttons with Macro links to the additional ExpandAll() and CollapseAll() Code Subroutines, default TRUE

Tree Nodes

Draw Standard Node Icons
- set to FALSE to prevent the Standard Node Icons from being drawn, default = TRUE

Draw Node Hairlines
- set to TRUE to draw the hairlines that connect Nodes in the Tree List or set to False to leave the Nodes unconnected (if using Triangles, set this to FALSE), default = TRUE

Node Hairline Colour
- set the Node Hairline Colour, default = Medium Grey

Node Type
- set the Node Type to 'Square' for square Colour Nodes, 'Triangle' for Triangle Colour Nodes or to 'Folder' for Folder Nodes, default = Square

Expanded Node Colour
- set the Square or Triangle Node Colour for Expanded Nodes, default = Orange

Collapsed Node Colour
- set the Square or Triangle Node Colour for Collapsed Nodes, default = Blue

Use Row banding
- set the Use Row Banding, default = FALSE

Row Banding Colour
- set the Row Banding Colour to Colour alternate Rows, default = Light Grey

Merge Columns Width
- sets the number of Columns used to merge Node Items, must be between 1 and 99 Columns, default = 4

Font Size
- sets the Node Font Size - this overrides any Cell Style formatting if a value is entered - Tip: leave blank to ignore, must be between 1 and 409 points, default = blank

HorizontalAlignment
- sets the Node Text Horizontal Alignment - this overrides any Cell Style formatting if a value is selected, must be xlLeft, xlRight, xlCenter or xlJustify, default = xlLeft

VerticalAlignment
- sets the Node Text Vertical Alignment - this overrides any Cell Style formatting if a value is selected, must be xlCenter, xlTop or xlBottom, default = xlCenter

WrapText
- sets the Node Text Wrap, must be either TRUE or FALSE, you will probably need to raise the 'Merge Column Width' setting and / or reduce the 'Font Size' setting if set to TRUE, default = FALSE

Orientation
- sets the Node Text Orientation, must be between -90 and 90, default = 0

AddIndent
- sets the Node Text Indent, meaning that the Node Text will be indented, default FALSE

IndentLevel
- sets the Node Text Indent Level, should only be used if the 'AddIndent' setting is TRUE, default 0

ShrinkToFit
- sets the Node Text Shrink to fit, will force Excel to render the Node Text to fit the merged Cells), default FALSE

System & Node Styles

Display the Output Message
- displays the output message indicating that the process is complete, default = TRUE

Node 1 Style
- a simple Tree List Node Style that you can use to create a Tree List

Node Style 2
- a checkbox Style, uses the Marlett Font with character 'a' for a tick and the character 'r' for a cross

THE EXAMPLES WORKSHEET

The "Examples" Worksheet contains simple Tree Lists Items that you can Copy into the "TreeList" Worksheet to test the Software - in fact I used these when creating the Example Workbooks shown at the top of this online documentation. Copy the entire area from Row '7' down and Paste it into Cell "F11" of the "TreeList" Worksheet. Press the 'Create Tree' Button. If you use the Hyperlink Example, when you Copy out the "Template" Worksheet, you will also need to Copy out the "Examples" Worksheet as the Hyperlinks go to that Worksheet. Some Colours are shown where I changed the Icons expanded and collapsed Colours. A Comment is included detailing other options that I chose on the "Options" Worksheet if you would like to recreate any of the Example Workbooks.

Q&A

Q. Can I delete the License Worksheet?
A. Yes, once you have purchased TreeList and read the Terms & Conditions of the License, you may remove it - please ensure that whenever you distribute Workbooks containing Tree List Code that the Code attribution remains in situ. Please remember to conform with the License whenever modifying TreeList - as per the Terms of the License, you cannot simply remove the License Worksheet and then pass this Software off as your own

Q. Can I change or add more Standard Icons - the ones where I include a Tag like '{Warning}' in the Node Item Name?
A. Yes, an array holds the current Names of the Icons which can be expanded in the Code. First of all, you need to make sure your Icon is a transparent PNG file so that it will work on a coloured background.

To change the Icon, right-click on a Shape and select 'Format Picture...'. On the 'Fill' part of the Format Picture options, select 'Insert Picture from', the 'File...' Button and choose a new Icon.

To add an Icon, Copy & Paste one of the Standard Icons ie. the '{Warning}' Icon into the "TreeList" Worksheet and rename it to your new Name ie. '{New Icon}'. Now change the Icon as mentioned previously. Lastly change the Code. Press ALT+F11 to enter the Code Editor. Double-click the 'mdlSubroutines' Code Module. Scroll down. Adjust the Code as per below (scroll to the right and you will see my change):

Icons = Array("{Warning}", "{Yellow File}", "{Blue File}", "{Orange File}", "{Yellow Picture}", "{Blue Picture}", "{Orange Picture}", "{Yellow Tick}", "{Blue Tick}", "{Orange Tick}", "{New Icon}")
 


Changelog

25.03.2017 - (Version 3.1) Shorten the Code output created by Treelist in the 'Template' Worksheet by passing a single Node String into a Subroutine - this prevents a 'procedure too large' error. Turn off Events & Screen Drawing to speed up the Code when creating Treelists

08.04.2016 - (Version 3) General Release, supersedes all previous Tree List Versions