Skeleton

Purchase · £2.99

Introduction

Skeleton

A Bare-bones Options Sheet Theme Creator for Excel (part of Treelist) for Excel 2013/2016 (32bit & 64bit)

Skeleton creates stylish toggle 2-Tier Menus for Option Sheets in two Colour Themes with or without Icon Protection and Click effects/animations. An Option Sheet or Template Sheet as it is initially named in Treelist can be simply copied into any Project. You design your Skeleton or use one of mine and then just add the 'Flesh' to the bones ie. extra Rows, Text, Checkboxes, Radiobuttons etc. All of the Code is automatically generated and designed so as to not interfere with outher VBA Code in your Projects. The beauty of using this type of Options layout is that it is very neat and compact. You can easily Copy & Paste additional Rows and the Code will recognise the extra Rows and Toggle them accordingly

In order to use Skeleton, you just Purchase Treelist_v4 from the link above (Top, Left-hand side)

Prerequisites

  • Treelist Version 4 for Excel 2013/2016 Client, (32bit & 64bit)
  • Moderate Excel skills. Minimum VBA Skills
  • Please - Windows PC · NOT a Mac!

Features

  • Two Theme Colours to choose from in Treelist, a Dark Theme and a Light Theme
  • Eight Example Files for you to use, play around with or extend in the Zip File that comes with Treelist_v4
  • Example Skeleton Lists showiing you how to include Hyperlinks with your Options Sheet creation






Installation

The Skeleton Theme Creator comes as part of Treelist Version 4. Unzip the Treelist_v4.zip File (Right-Click->Extract All). Check out the free Skeleton Examples that come with the Zip File - you can create all of these using the Treelist_v4.xlsm File with some guidance here for any tweaking and Colours for the Click Effect. You can of course use these Examples as they are or alter/extend them and copy them into your own Projects without even opening Treelist

Usage

Creating a Skeleton Options Sheet

Open Treelist_v4.xlsm. On the TreeList Sheet press the 'Clear Treelist Range Contents' Button. Click on the Examples Sheet and scroll right. Select the Range for the Skeleton Example List 1. Copy it. Paste it into Cell "F11" on the TreeList Sheet. Click on the Options Sheet. Scroll to the bottom. Select either the Dark Theme or the Light Theme Radiobutton underneath the Skeleton Accordion (Click the Accordion, if the Settings are not displayed). Press the 'Apply the Skeleton Theme' Button to apply the Theme. A Message Dialog will be displayed. Click back onto the TreeList Sheet. Press the '2. Create Treelist' Button to create the Treelist (Click Yes to any Message Dialog that may appear). The Skeleton Theme will be created on the Template Sheet as per the Image below (I chose the Skeleton List Example 1 and the Dark Theme):



Copying out the Template

Left-click on the Template Tab and choose 'Move or copy'. Select 'New Book' in the first Drop-down Combo Box. Click the 'Create a copy' Checkbox at the bottom of the Dialog and Click OK. Now change the Name of the Template Tab to Options and Save the File as an XLSM File (Macro-enabled). That's it. If you wanted to Copy in into your own Project then with your Project Open select it from the first Drop-down Combo Box and choose where to Copy it into, in the 'Before sheet' Section of the Dialog

Adding Icon Protection

To add Icon Protection so that users cannot Right-click on an Image, change the 'Protect Tree Node Icons from Selection' Setting underneath the TreeNodes Accordion to TRUE. Now an Icon cannot be Selected or Moved

Adding the Click Effect

To add a Click Effect to the Icons, change the 'Create Click Effect Code Subroutine' Setting underneath the Treelist Accordion to TRUE. Then to adjust the Click Effect for a Dark Theme, set the '- Click Effect Size' to 16 and the '- Click Effect Colour' to 2236962 or RGB(34,34,34) using the Colour Picker. To adjust the Click Effect for a Light Theme, set the '- Click Effect Size' to 16 and the '- Click Effect Colour' to 15648990 or RGB(222,200,238) using the Colour Picker. Now simply create the Treelist again

Adding Checkboxes

Intro

To add a Checkbox or Checkboxes to your Skeleton, you need to create a Defined Name or Names, add the Checkbox Cell Formatting and some Code. You can add 1 Checkbox and run Code to capture the change and then act accordingly or you can simply read the State of the Defined name to check if it is either Ticked or not Ticked. This method will work regardless of any Icon Protection and/or animation effects on the Toggle Nodes

Add a Single Checkbox with Code and Optionally Handle any Changes as they Occur

Okay so taking a Skeleton Options Sheet built from Treelist add a Cell Fill with a Border, enter a lower case 'a' without apostrophes and align the Text to the middle of the Cell. Important! change the Font Name to 'Marlett' without apostrophes and the Font Size to 12. I used a Cell Fill RGB(204,239,246) with a Black Font. Click on the Cell and enter 'Checkbox.Gridlines' without apostrophes, into the Name Box (Top, left) - this is our Defined Name or Named Range. Go into the VBE Editor (ALT+F11) and double-click into the Template (Options) Worksheet Code Module. After the Code 'Option Explicit' Copy and Paste in the Code below. Okay now try double-clicking the Checkbox Cell. The Headings on the Active Sheet should toggle On/Off - Please Note: remove the 2 lines beginning ' run Code here if you don't want to handle changes directly 
    
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.CountLarge > 1 Then Exit Sub
   Application.ScreenUpdating = False
   ' single Checkbox
   If Not Intersect(Target, Me.Range("Checkbox.Gridlines")) Is Nothing Then
      If Target.Value <> "a" Then
         Target.Value = "a"
         
         ' run Code here if you want when the Checkbox is Ticked
         ActiveWindow.DisplayHeadings = True
         
         Target.Parent.Activate
         Cancel = True
         Exit Sub
      End If
      If Target.Value = "a" Then
         Target.ClearContents
         
         ' run Code here if you want when the Checkbox is Blank
         ActiveWindow.DisplayHeadings = False
         
         Cancel = True
         Exit Sub
      End If
   End If
End Sub

Check if a Single Checkbox is Ticked or Unticked

You can check using Code to see if the Checkbox is Ticked or Unticked by simply querying the contents of the Cell via the Defined Name or Named Range, thus: 
    
   If [Checkbox.Gridlines].Value2 = "a" Then MsgBox "Checkbox is Ticked" Else MsgBox "Checkbox is not Ticked"

Screen Shot





Add Multiple Checkboxes with Code and Optionally Handle any Changes as they Occur

Okay so taking a Skeleton Options Sheet built from Treelist add 3 Cells with a Fill and a Border. Enter lower case 'a' into all 3 of them without apostrophes and align the Text to the middle of the Cell. I used a Cell Fill RGB(204,239,246) with a Black Font. Highlight all 3 Cells and enter 'Checkbox.Options' without apostrophes, into the Name Box (Top, left) - this is our Defined Name or Named Range. Important! now enter Option 1, Option 2 and Option 3 next to each of the Cells (to the right of each Checkbox). This is the method that we will use to capture each Checkbox using 'Target.Item(1, 1).Offset(0, 1).Value2'. Go into the VBE Editor (ALT+F11) and double-click into the Template (Options) Worksheet Code Module. After the Code 'Option Explicit' Copy and Paste in the Code below. Okay now try double-clicking the Checkbox Cell 

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
   If Target.CountLarge > 1 Then Exit Sub
   Application.ScreenUpdating = False
   ' Multiple Checkboxes
   If Not Intersect(Target, Me.Range("Checkbox.Options")) Is Nothing Then
      If Target.Value <> "a" Then
         Target.Value = "a"
         
         ' Ticked
         Select Case Target.Item(1, 1).Offset(0, 1).Value2
          Case "Option 1":
          MsgBox "Option 1 Ticked.  Optionally Run Code here..."
          Case "Option 2":
          MsgBox "Option 2 Ticked.  Optionally Run Code here..."
          Case "Option 3":
          MsgBox "Option 3 Ticked.  Optionally Run Code here..."
         End Select
         
         Target.Parent.Activate
         Cancel = True
         Exit Sub
      End If
      If Target.Value = "a" Then
         Target.ClearContents
         
         ' Unticked
         Select Case Target.Item(1, 1).Offset(0, 1).Value2
          Case "Option 1":
          MsgBox "Option 1 Unticked.  Optionally Run Code here..."
          Case "Option 2":
          MsgBox "Option 2 Unticked.  Optionally Run Code here..."
          Case "Option 3":
          MsgBox "Option 3 Unticked.  Optionally Run Code here..."
         End Select
         
         Cancel = True
         Exit Sub
      End If
   End If
End Sub

Check if a Multiple Checkbox is Ticked or Unticked

You can check using Code to see if the Checkboxes are Ticked or Unticked by simply querying the contents of the Cell Offeset via the Defined Name or Named Range, thus (where N for Item(1, N) is the Checkbox you are querying): 
    
   If [Checkbox.Options].Item(1, 1).Value2 = "a" Then MsgBox "Option 1 is Ticked" Else MsgBox "Option 1 is not Ticked"

Screen Shot





Adding Radiobuttons

Intro

To add Radiobuttons to your Skeleton, you need to create a Defined Name, add the Radiobuttons Cell Formatting and some Code. This method will work regardless of any Icon Protection and/or animation effects on the Toggle Nodes. With the Radiobuttons you can also use another Defined Name or named Range that changes whenever a Radiobutton is Selected to indicate the currently Selected option chosen

Add Radiobuttons with Code and Optionally Handle any Changes as they Occur

Okay so taking a Skeleton Options Sheet built from Treelist add 3 Cells with a Fill and a Border. Enter a lower case 'l' without apostrophes into each Cell and align the Text to the middle of the Cell. Important! change the Font Name to 'Wingdings' without apostrophes and the Font Size to 12. I used a Cell Fill RGB(204,239,246) with a Black Font RGB(71,71,71) or Long 4671303. Highlight all 3 Cells and enter 'Radiobuttons' without apostrophes, into the Name Box (Top, left) - this is our Defined Name or Named Range. Now enter Radiobutton1, Radiobutton2 and Radiobutton3 in the next Cells to the right of the Radiobutton Cells. Go into the VBE Editor (ALT+F11) and double-click into the Template (Options) Worksheet Code Module. After the Code 'Option Explicit' Copy and Paste in the Code below. This Code is required in order to prevent a Double-click from going into Edit Mode in a Radiobutton Cell ie. when you press F2 or double-click the Cell 
    
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    ' limit the Target count to 1
    If Target.CountLarge > 1 Then Exit Sub

    ' exit if the Selection made is not within our Defined Name
    If Intersect(Target, Range("Radiobuttons")) Is Nothing Then Exit Sub

    Cancel = True
    Exit Sub

End Sub

Handling the Selection Changes

Next add the following Code to handle the Selection changes. You can see the commented out Code below - if this is uncommented and you add another Named Range, you can track the Selected Radiobutton this way if you want, but I will demonstrate another way to find out what is Selected, remember Radiobutton Groups can only have one Button Selected at a time. You can of course, have multiple Radiobutton Groups 

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    ' limit the Target count to 1
    If Target.CountLarge > 1 Then Exit Sub
    
    ' exit if the Selection made is not within our Defined Name
    If Intersect(Target, Range("Radiobuttons")) Is Nothing Then Exit Sub

    ' vars
    Dim RadiobuttonCell As Range

    ' iterate Defined named Range
    For Each RadiobuttonCell In [Radiobuttons].Cells
        ' switch the current Radiobutton on
        If RadiobuttonCell.Address = Target.Address Then
            With RadiobuttonCell.Font
                .Color = -12105913
                .TintAndShade = 0
            End With
            ' identify the Selected Radiobutton
            ' comment out if not required
            '[SelectedOption].Value = RadiobuttonCell.Offset(0, 1).Value
            ' switch the current Radiobutton off
        Else
            With RadiobuttonCell.Font
                .ThemeColor = xlThemeColorDark1
                .TintAndShade = 0
            End With
        End If
    Next RadiobuttonCell

End Sub

Check which Radiobutton is Selected for a Radiobutton Group

You can check using Code to see which Radiobutton is Selected by simply querying the Font Colour of the Selected Cell for the Radiobutton Group via the Defined Name or Named Range, thus: 
    
Public Sub RadiobuttonState()
   
   Dim Radiobutton As Range
   
   For Each Radiobutton In [Radiobuttons].Cells
    If Radiobutton.Font.Color = 4671303 Then MsgBox "Radiobutton: " & Radiobutton.Offset(0, 1).Value2 & " is Selected": Exit For
   Next Radiobutton

End Sub

Screen Shot





Inserting Rows

To insert more Rows, expand a Section and then Highlight Rows underneath the Section (Important! do NOT highlight the first Row). Right-click on the Row Headers and select 'Copy'. Right-click on the Row Headers and select 'Insert Copied Cells'. Now change the Text as you require and test the Toggle functionality of the Nodes. You can insert as many additional Rows as you like for any Section

Inserting a New Section

To insert a new Section, expand a Section and then Highlight all of the Rows in a Section. Right-click on the Row Headers and select 'Copy'. Go to the next free Row and right-click on the Row Header and select 'Insert Copied Cells'. Now change the Names of the 2 Icons. Right-click on an Icon->Graphics Format Tab->Selection Pane and change the 'R' value of both Icons to anything you like. Now open the Named Ranges Box->Formulas Tab->Name Manager and add 2 new Defined Names or Named Ranges just like any existing pair but give them your new value. One should be 'NODE_C6_R40' and then reference the new Rows that you want to toggle ie. '=Help!$32:$40'. The second should simply be 'NODE_C6_R40_STATUS' (the Status Node) with '=0'. Here is a Screen Shot where I added 2 new Named Ranges and a new Section based upon Section 2 to the end of my help Sheet



FAQ

There are currently no Frequently asked Questions

Future Builds

There are currently no future build requirements

Screen Shots

Skeleton Dark Theme Options Sheet built using Treelist with Icon Protection and Click Effect



Skeleton Light Theme Options Sheet built using Treelist with Icon Protection and Click Effect



Skeleton Variation with Arrows and a different Font Colour RGB(113,116,128)



Skeleton Variation in Red with Table of Contents and Hyperlink Navigation

Video

Here I run through 2 Skeleton Theme Option Sheets created using my Treelist Software

Support

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

  • Make sure your question is a valid Software Issue and not a customization request
  • Make sure you have read through the FAQ's, online documentation and any related video guides before asking support on how to accomplish a task
  • 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
...or Contact Mark Kubiszyn via our Facebook Page - remember to be patient, if there has been an issue with your download, Mark will always respond within 48 hours and will Email you the File directly if neccessary or via Messenger. 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

Changelog

You can find the version history in the Code Module for any Macro-enabled Software or read more information on the status of each release
- the latest Version including a description of any changes made is always shown first

Released as part of Treelist Version 4 16.04.2020