Geek Documentation Logo

Treelist

Version 3.2


Introduction


If you have already purchased this Software, let me take a moment to thank you for being a loyal customer
You are entitled to free lifetime updates for ALL future builds

This documentation is to help you understand the Software and to give you a more meaningful insight into what it can do. Please go through the documentation and read it carefully · Basic to Moderate skills are required - knowledge of VBA would be advantageous if you wish to develop the Software

Requirements

You will need the following Excel Version & Prerequisite to use this Software

  1. Excel 2010, 2013 & 2016 (32bit or 64bit) - this Product has been specifically designed for Excel 2013. Please Note: the Code works with Excel 2010, however there is a flicker when selecting Nodes - please download the Demo Workbooks and test all of the examples before purchasing if you are using Excel 2010
  2. Basic to Moderate Excel skills - knowledge of VBA would be advantageous if you wish to develop the Software
  3. Windows PC · NOT a Mac!

No support is provided for customization or development of this Software






About Treelist #back to top

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 - just Copy out the Worksheet to use the new Treelist

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 Demo Workbooks that have been created using Tree List

Treelist Software Image 1000 x 654

Features include:

  • * 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
  • * 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
  • * Choose the Node Hairline Colour or whether or not to draw the Node Hairlines
  • * Choose the Expandable & Collapsable Node Types, either Folders, Squares or Triangles
  • * Choose Row Banding and set the Banding Colour
  • * 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

Please Note:

When using Treelist for larger sets of data you may have to turn off 'Draw Node Hairlines' as the routine has to iterate through the Worksheet and will take some time to complete. I have tested Treelist with 3.5K of Rows and 5 levels deep with 'Draw Node Hairlines' set to False






Getting Started #back to top

The download File

Open the 'Treelist.xlsm' File. Click on the 'Treelist' Worksheet and examine the example layout. Press the 'Create Tree' Button to create the example Treelist built from the data in the 'Treelist' Worksheet. The new Treelist will be created on the 'Template' Worksheet. Test the collapsing and expanding of all of the Tree Nodes

Quick Steps

Clear the data in the 'Treelist' Worksheet and enter new data. Press the 'Create Tree' Button. Copy out the 'Template' Worksheet. Save the Worksheet. Rename the Worksheet Tab if you want to

Configuration - the Options Worksheet #back to top

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 #back to top

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

Catch:
Application.EnableEvents = True
Application.ScreenUpdating = True                        

The Treelist Worksheet #back to top

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

The Created Treelist #back to top

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 #back to top

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

Adding Checkboxes #back to top

In order to add Checkboxes to a Treelist you need to do the Following:

1. Create a Treelist using bespoke settings
2. Perform some Formatting
3. Set a Defined Name 'Tree.Checkboxes' and then add Code to the finished Template

Create a Treelist using Bespoke Settings

Add the following to the Treelist Worksheet (the Text Placeholders are used to create a Blank Space for the Checkboxes and or more Rows for Notes)

Software		
	Project Started	
		Checkbox
	Project Complete	
		Checkbox

Change the following settings in the Options Worksheet

Draw Node Hairlines                         False
Merge Columns Width                         8

Open my legacy Checkbox File and Copy Cell "C13" into the Cells containing the Text Checkbox and then Create the Treelist

Prepare the Treelist

Create the Treelist

Perform some Formatting

Delete the Yellow Sub-Node Shapes (right-click Cut) if preffered. Select Rows "10:11" and UnMerge the Rows. Do the same for Ros "14:15". Insert any extra Rows here if you need them for Notes by clicking on Row 11 and then Insert - repeat for the other Checkbox by selecting the last Row and then using Insert. This will maintain the Code to allow the Rows to Collapse. Select Cells "J10:J11" and then Merge the Cells. Adjust the Width of Column "J" as required and Format the rest of the unused Cells still marked in Blue with a White Fill

Here is the Template with the Checkboxes I added:

Treelist with Checkboxes



Set a Defined Name 'Tree.Checkboxes' and add Code to the Finished Template

Treelist with Checkboxes and Defined Name

Copy out the Template Worksheet and then add the Code below to the top of the Worksheet Code Module above the other Code

' 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                                                  

That's it - here is my finished Example

Screen Shots: #back to top

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




There are currently no frequently asked questions about this Software that cannot be answered via this documentation






Support #back to top

Please remember you have purchased very affordable Software and you have not paid for a full-time Software design agency - I am but one man. Occasionally I may help with small tweaks, but these requests will be put on a much lower priority due to their nature. You have not PAID for Support, Support is 100% optional and I provide it for your convenience, so please be patient, polite and respectful

Support (limited) for my Software includes:

* Responding to questions or problems regarding the Software and its features
* Fixing valid (replicated) bugs and reported issues for the VERSION I HAVE WRITTEN

Software support does not include:

* Customization and installation services
* Support for third party software or ANY kind of development whatsoever

Before seeking support, please...

* Make sure your question is a valid Software Issue and not a customization request
* Make sure you have read through the documentation and any related video guides before asking support on how to accomplish a task
* Make sure to double check the Software FAQs or online documentation
* Ensure that you access to the VBOM is allowed and that Macros can run in Excel
* Make sure to provide 'proof of purchase' and state the name / version of the Software that you are having issues with when requesting support by Email or via Facebook

How to get Support

Contact Mark Kubiszyn on the Email address provided when you purchased the Software, including the Order Number
Contact Kubiszyn.co.uk via our Facebook Page - remember to be patient, if there has been an issue with your download, I will always respond within 48 hours and will Email you the File directly if neccessary or via Messanger. For other issues the response time may be considerably longer and I may choose to respond to specific questions only (as is my right), depending on what has been asked

Known / Fixed Bugs #back to top

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

Version History (Changelog) #back to top

You can find the version history in the Code Module for any Macro-enabled Software or read more information below. The latest Version is always shown first


Changelog

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