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

Acknowledgement

Treelist was written by Mark Kubiszyn

  • the latest version is version 3
  • 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-2017. All Rights Reserved
' Website/Follow:
' http://www.kubiszyn.co.uk/
' https://www.facebook.com/Kubiszyn.co.uk/
						

Purchase

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

Configuration

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 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. Here is a snippet of the Code that is generated whenever you create a new Treelist:

' // 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")
						

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

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

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