Table Slider

Purchase · £2.99

Introduction

Introducing Table Slider

Table Slider - Add a Stylish Table Slider to your Projects in Excel to Filter & Sort a Table Column

Table Sliders are 3 Shapes with a Slider Button linked to a Subroutine that allow you to move the Slider left and right using the Mouse and control a Macro that then Filters and Sorts a Table Column. The Macros can be anything you like in reality, however the Code included in this Software simply Filters and then Sorts a Sales Column in a Table by the largest currency value

The Slider can be either Horizontal or Vertical and is protected from user changes using standard Sheet protection that can be easily toggled on/off. Here is an image of a Table Slider for Excel showing all 20 Records filtered descending in the Table Column 'Sales'. It is positioned next to the tiny little Drop-down Button included in Table Headers if turned on. My Table Slider has a lovely look and feel as you drag it along, no flicker, just a smooth action as you Filter and Sort your Table Columns. I chose 3 dots as an elipse for my Text on the Button but you can change the Font and use Webdings etc. to add Arrows or other Icon Font styles to the Slider button. You can also hide a Table Column Drop-down Filter Button and just use the Table Slider instead to Filter and Sort the Table Column




If you like this Project you may also enjoy


Pivot Table Slider ·  Awesome Sliders ·  Flat UI Specialist Sliders ·  Sliders ·  Vertical Sliders ·  Slider Toggle Buttons ·  Awesome Buttons ·  Sliders ·  Small Toggle Buttons ·  Menubar ·  Toggle Buttons ·  xlui ·  RadiObutton ·  CheckboX ·  Burger Buttons ·  Popup Burger Buttons

Prerequisites

  • Excel 2013 (Version 5) & Excel 2016 (32bit or 64bit, Version 8)
  • Basic to moderate Excel Skills and some VBA skills to copy Shapes and then link a Subroutine to a Shape or a Graphic object
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • Uses a TimerProc() Callback API to control the operation of the Slider in Milliseconds
  • Creates and destroys a Type Interface called 'this' which contains all of the local variables used by the Slider
  • Minimum and Maximum bounds (left/right and up/down) are now derived using the Placeholder Width/Height and the Slider Button Diameter
  • The Slider Bar Width/Height is dynamically adjusted based upon the Placeholder Width/Height and the Slider Button Minimum bounds, not the new Mouse position as was the methodology used in previous Slider Projects
  • The Code dynamically detects whether the Slider is a Horizontal Slider or a Vertical Slider using the logic if PlaceHolder Width > PlaceHolder Height then Direction is Horizontal else Direction is Vertical
  • Works with any Zoom level (some tiny anomalies may occur)
  • The Code linked to the Slider Button is the same Subroutine for both Horizontal and Vertical Sliders
  • You can have multiple Slider Buttons used on multiple Tables in the same Worksheet
  • Use Formula in the Config. Named Range so you can add extra Rows to the Table and the Slider will pick these up
  • Hide a Table Column Drop-down Filter Button and just use the Table Slider instead to Filter and Sort the Table Column
  • Tested with Table Rows & the Top Ten Filter to a maximum of 500 Rows as per Excel limits. You can obviously apply other Filtering and Sorting options via your own Macros that can be linked into this Slider
  • Watch the Table Slider Message update with Filtered Records as you drag the Table Slider back & forth before any Filtering and Sorting has taken place in the Table. This is standard design so no Code is running until the Mouse Button is released on the Table Slider
  • Real-time List Updating: When using the Table Slider with the Filter Formula on Table data the update to a dynamic List is in real-time! See Example2 for more information
  • Resize and reposition your Table Slider within the Worksheet






Installation

Open the TableSlider.xlsm File and test out the Example on the Example1 Sheet. The Code is stored in the 'mdTableSlider' Code Module. This is where you will also configure or add your own Code if neccessary for any additional Sliders or anything different that you may want to do. Please read all of the info on the Sheet and in this online documentation before you begin to edit and create your own Table Sliders or before turning to Support

Usage

TableSlider.xlsm


Example1 Sheet
Before you start editing and creating your own Table Sliders, take some time to look at all of the Example in the 'Example1' Sheet. Click on the Example1 Sheet. A Table Slider uses the same Code located in the 'mdTableSlider' Code Module. The Table Sliders comprise of 3 Shapes and each Slider Button is linked up to the 'DragSlider' Macro which can operate both horizontal and vertical Sliders (if required). Whenever a Slider is operated the Code will automatically lock the Objects for Drawing, meaning that you cannot right-click them by accident with the Mouse. You should always Unprotect the Worksheet before editing a Table Slider or any of its parts

Each Table Slider must have a unique Name ie. Slider1, Slider1Bar, Slider1Placeholder, Slider2, Slider2Bar, Slider2Placeholder etc. They also must have 3 unique Named Ranges scoped to the Worksheet, Slider1, Slider1Value and Slider1Config. The Slider1 Named Range is updated whenever the Slider is operated. The Slider1Value is a Formula used to Round the Slider1 Named Range. The Slider1Config is used to pass a pipe-delimited string of values that are as follows:

a) The Minimum Range of the Slider
b) The Maximum Range of the Slider
c) The Name of the Macro that you want to Run when the Slider Button is released by the Mouse

Here is the Example1 Sheet Fig 1.




Example2 Sheet (Excel 2013-2016)
The List on the 'Example2' Sheet uses Filter Function Formula on the data in the Table on the left-hand side. This Array Formula functionality is only available in newer versions of Excel. It takes the Table data, then Filters and Sorts it via the Formula and the 'Slider1Value' output of the Table Slider to create a dynamic, real-time updating List of Country / Company Sales, Descending. This example does not pass any parameter for the Macro Name in the 'Slider1Config' Named Range. You can also use array constants in the formula - for example {3,1} for sort index and {-1,1} for sort order. For example, swapping the -1 for 1 will invert the Sort so it will show Lowest Sales. Try it, move the Table Slider using the Mouse back & forth really quickly to see the List updating and then try swapping the '-1' in the Array Formula for '1' Fig 2.




Editing the Slider Shapes (Selections and Formatting Panes)
In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheets as Code protection is added every time that they are used to protect the interface (protects the Shapes from user interference, either on purpose or accidently) - do this from the Review Tab on the Ribbon. Click the Example1 Sheet. Right-click one of the Slider Buttons or left-click the Bar. Now, use the Selection Pane on the Editing Group of the Home Tab of the Ribbon to make Selections easier. To format a Shape you can hover near the Selection and use right-click Format and then change the formatting using the formatting Pane. I like to toggle on a Shape between the Format Pane and the Selection Pane Fig 3.



Copying one of the Sliders into another Workbook
This is simple. Unprotect the Sheet. Using the Selection Pane, select all 3 Objects for one Slider (use CTRL+Left Mouse click). On the Clipboard Group of the Home Tab on the Ribbon, select Copy and then switch to your Workbook and Paste

Now create the 3 Named Ranges using the Defined Names Group of the Formulas Tab on the Ribbon. Select Name Manager. Then press New and enter a Name ie. Slider1. Then choose the Scope to the Worksheet that you are on. Click on the Cell that will be used for the Named Range and then click OK. Repeat this for the other two Named Ranges ie. Slider1Value and Slider1Config. Now populate all three Named Ranges as I have done on my Example Sheet using my example values. Open the VBA Code Editor and in your Workbook you can drag the Code Module directly into your Project or you can include a new or existing Code Module and then Copy and Paste in my Code from the TableSlider.xlsm Workbook. Now just right-click on the Slider Button and assign the Macro 'DragSlider'. That's it, go ahead and do some Sliding...

Resizing the Sliders
This is also very easy. Using the Selection Pane, just select the Bar and / or the Placeholder and adjust the Widths. Here I have adjusted the Heights of the 'Bar' and the 'Placeholder' Objects to make them smaller to fit within the table header better. Move the Slider Button by right-click and use the Cursor Arrows to move it into position say at the right-hand end of the Bars. Now Drag it and see the magic happen - the Slider Button myseriously lines up with your Minimum and Maximum Ranges. All that may be required is a tweak to the Buttons horizontal or vertical positioning, sweet! Fig 4.



And this is the finished result, you could also make the Button a little smaller I guess to fit in with the reduced 'Bar' and 'Placeholder' Fig 5.




Real-time Updating
When using the Table Slider with the Filter Formula on Table data the update to a dynamic List is in real-time. This is used in 'Example2'

Using Foobar as the Macro Name or Omitting it Entirely
There may be a time when you don't want to actually execute a Macro using the Table Slider for instance when using 'Example2' so the Code will skip any errors caused by incorrect or missing Macro Name Subroutines. This means you can use 'Foobar' or 'Foo' or any dummy Macro Name for the third parameter or just omit the third parameter entirely

Hiding a Drop-down Filter Button in a Table Column
You may or may not want to hide the Drop-down Button in a Table Column ie. the 'Sales' Column for this example. You can do this with the following Code, swap the False to True to show the 'Sales' Drop-down Button again - the Field is the third Column and the 'Sales' is the Column Name 
' use this to hide/disable a Drop-down in a Table by the Column Name
Private Sub DisableKeyFigureSelection()

   Dim ActiveTable As ListObject
   Set ActiveTable = ThisWorkbook.Sheets("Example1").ListObjects("Table1")
  
   With ActiveTable
      .ListColumns("Sales").Range.AutoFilter Field:=3, Visibledropdown:=False
   End With

End Sub
And here is the result of hiding the 'Sales' Column Drop-down Filter Button and then resizing the Slider again Fig 6.



Screen Shots

A Table Slider Filtering & Sorting a Table Column called Sales for 12 Records on the Example1 Sheet - its the little bar next to the tiny Drop-down Button in the Table Column

The result of me hiding the 'Sales' Drop-down Filter Button and my Object Resizing for the Slider Shapes

A Table Slider being used to Filter & Sort a Table into a List in Real-time using Filter Formula, meaning the Records in the List update immediately as you move the Mouse back & forth using the Slider

FAQ

Q. I Filtered using the Table Slider for the Top Ten of 1 Record and 2 Records are showing?
A. This is as standard Excel design. If you try doing a Top Ten on a list with multiple values then doing Top 1 of the list will have the same results, if the values are the same it will show 2 Records

Table Slider

This is a video of the Table Slider in action. In this demonstration, I go through 'Example1' in the download File. Music is 'Hologram by Bobby Richards' · view the Table Slider video on YouTube

This is a video of the Table Slider in action using Filter Formula to Dynamically Filter & Sort a List. In this demonstration, I go through 'Example2' in the download File. Music is 'Hologram by Bobby Richards' · view the Table Slider video on YouTube

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

Future Builds

* No ideas as yet!

Bug Fixes

* There are currently no bugs reported for Table Slider

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

18.09.2022 - (Version 1.1)

Added an 'Example2' Sheet demonstrating how to use Filter Function Formula to Filter and Sort a List from Table data together with the 'Slider1Value' from the Table Slider output

17.09.2022 - (Version 1)

General release of Table Slider