Quick Steps

Treelist documentation

2 Simple Steps to start working

  1. Create your Tree List by entering Items below the Shaded area in the "Treelist" Worksheet. Press the 'Create Tree' button to output the finished Tree on the "Template" Worksheet
  2. Copy out the 'Template' Worksheet and rename it if you wish - that's it, you are good to go, all of the Code is dynamically written out to the 'Template' Worksheet Code Module


Treelist was written by Mark Kubiszyn

  • the latest version is version 3.2
  • as per terms of the Software License, you should leave the following code attribution in situ whenever distributing Treelists created by this Software

' © Copyright/Author:
' Mark Kubiszyn 2012-2018. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/


You can Purchase Treelist for £2.99 via FastSpring by clicking here


Configuring Treelist is straightforward. All of the Options for Treelist reside in the "Options" Worksheet - expand the different settings by clicking on the 'Toggle' bars. Here are the main Treelist Settings:

Tree List Row Number Output	        6	sets the Row position where the first Tree List Item is rendered in the "Template" Worksheet, default = 6
Tree List Column Number Output		6	sets the Column position where the first Tree List Item is rendered in the "Template" Worksheet, default = 6
Tree List Column Width			1.43	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			7.75	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	TRUE	creates an additional Code Subroutine to exapnd all of the Tree List Items, default TRUE
Create CollapseAll() Code Suroutine	TRUE	creates an additional Code Subroutine to collapse all of the Tree List Items, default TRUE
Create Buttons for Code Subroutines	TRUE	creates the Buttons with Macro links to the additional ExpandAll() and CollapseAll() Code Subroutines, default TRUE

Here are the Tree Nodes Settings:

Draw Standard Node Icons		TRUE	set to FALSE to prevent the Standard Node Icons from being drawn, default = TRUE
Draw Node Hairlines			TRUE	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				Square	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				FALSE	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			4	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			xlLeft	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			xlCentersets 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				FALSE	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				0	sets the Node Text Orientation, must be between -90 and 90, default = 0
AddIndent				FALSE	sets the Node Text Indent, meaning that the Node Text will be indented, default FALSE
IndentLevel				0	sets the Node Text Indent Level, should only be used if the 'AddIndent' setting is TRUE, default 0
ShrinkToFit				FALSE	sets the Node Text Shrink to fit, will force Excel to render the Node Text to fit the merged Cells), default FALSE


Here are the System & Node Style Settings:

Display the Output Message		TRUE	displays the output message indicating that the process is complete, default = TRUE
Node 1						a simple Tree List Node Style that you can use to create a Tree List
☑						a checkbox Style, uses the Marlett Font with the character 'a' for a tick and the character 'r' for a cross


How the Code Works

The Icon that is being Toggled is passed into a small Subroutine which then Hides or Unhides Rows pertaining to the Nodes' Address String containing a Range of all of the attached Rows. The different Node Shapes are also set, as to is the Node Status for the Nodes Defined Name

Parent Icons are attached to the Code having the 'Template.TreeUI' Subroutine as their Macro. The Standard Node Shapes 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. Here is a snippet of the Code that is generated whenever you create a new Treelist:

On Error GoTo Catch
Application.ScreenUpdating = False
Application.EnableEvents = False

' // vars
Dim objName As Name
Dim X As Variant: X = Application.Caller
Dim strNode As String

' // pass the Node into the UI Subroutine to toggle the Nodes
UI IIf(Mid(X, Len(X), 1) = "X", Mid(X, 6, Len(X) - 6), Mid(X, 6, Len(X) - 5))

' // check the Defined Names - this ensures that any Rows remain hidden for Node heirarchy's
For Each objName In Template.Names
If objName.Name Like "*NODE*_STATUS" Then
strNode = Mid(objName.Name, 1, InStr(1, objName.Name, "_STATUS", vbTextCompare) - 1)
If Right(objName.Value, 1) = True And Rows(Template.Range(strNode).Address).EntireRow.Hidden = False Then
Rows(Template.Range(strNode).Address).EntireRow.Hidden = True
End If
End If
Next objName

Application.EnableEvents = True
Application.ScreenUpdating = True

Getting Started - 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

Viewing the Result - The Template Worksheet

The "Template" Worksheet is used to output and view/test 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 and the Code will continue to work to Expand and Collapse the Treelist

The Example Workbooks

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 adjust the output of the tree List to fit your own needs but you can only create new Treelists by purchasing the Software. If you like what you see, please Purchase the Software from Fastspring

Screen Shots

Here are some Screen Shots of different Treelists that I have created:

1. Treelist Folders

1. Treelist Folders

2. Treelist Countries

2. Treelist Countries

3. Treelist Hyperlinks

3. Treelist Hyperlinks

4. Treelist items

4. Treelist items

5. Treelist Triangles

5. Treelist Triangles

6. Treelist Deeper Levels

6. Treelist Deeper Levels

7. Treelist Complex

7. Treelist Complex

8. Treelist Project Checkboxes

8. Treelist Project Checkboxes


Support is only given to Purchased Software. You should only contact me on the Support Email address supplied by FastSpring - please provide proof of purchase ie. Email address or Order Reference

Known / Fixed Bugs

28.03.2018 - "Column Width must be between 1.0 and 9.99." Message This may occur for one of 2 reasons. Reason 1 - your PC may use the comma ',' character instead of the period (full stop) '.' character as the Radix Point or separator for numbers so change the '.' to a ',' for the Option and the Code should work. Reason 2 - please ensure that you have "Trust access to the VBA project model" ticked in the Trust Center Settings

  • - this was identified 26.03.2018


28.08.2017 - (Version 3.2) Amend the Treelist "Template" Code to minimise the amount of Code required and to speed up toggling of Nodes. Tweaks to the Parse 1 & Parse 2 Subroutines to speed up the Code when writing the Treelists

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