Introduction

Introducing Specialist Sliders

Specialist Sliders - Bars with a Button that you can Slide up and down to increase or decrease a value for a Range or Defined Name for Excel with Step Range and Optional Bar Scale

Specialist Sliders are small bars with Buttons that you click and then drag up or down to increase or decrease a value Range from 0 to 100 as default or a bespoke Range. Specialist Sliders have a coloured Slider Bar that sits underneath the Slider Button and expands or contracts as the Slider Button is dragged. These Sliders differ from other Sliders that I have created by the way that they lock into a position when dragged by the Mouse. When the Mouse Button is released the Slider Button will 'spring' to the nearest Step on the Scale. Slider Shapes are linked to Macros that enable this movement in pixels or in Steps as part of a Range that can shown visually as a Scale through a variety of different methods including a Chart Scale and Cell Formatting. Sliders have a Cursor Drag Coefficient and a Speed Coefficient. They can be set manually either by Cell adjustment or via a VBA Macro. Sliders use advanced Code to ease the setup, design and implementation together with Named Ranges to control their various attributes



If you like this Project you may also enjoy


Vertical Sliders ·  Sliders ·  Slider Toggle Buttons ·  Flat UI

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

  • Code ynamically creates the minimum and maximum settings without any user interaction. This means that you can simply Copy the 3 Shapes required for each Specialist Slider, rename them Slider2, Slider2Bar, Slider2Placeholder, format them how you like and then just drag the Slider Button to see your masterpiece in action
  • Optimised Code allows you to set the Slider Button as a Percentage of the Width of any designed Slider Bar and Slider Placeholder
  • Specialist Sliders have a Cursor Drag Coefficient and a Speed Coefficient, which when combined together give a springy, resistance when pulling or dragging the Slider Buttons
  • You can change the Custom Cell Format to be Percentage (%) or Numeric and the Code will detect the underlying Cell Format to give either 0%-100% or 0-100
  • Draggable, Step SLiders. When these Slider Buttons are dragged they exhibit a springy feel and will lock into situ to the nearest Step once the Mouse Button is released
  • Use a range for your Slider. Add a Named Range with a Suffix of Range ie. Slider6Range and use a Pipe-delimited String of Start and End numbers ie. 0|5 meaning 0 to 5 steps on the Slider. The Named Range should contain either 30|60 if using Numeric values or 30%|60% if using Percentage values
  • Store the Slider values in the Sheet to Defined Names or a Range both as numbers or as a percentage
  • Output the values after the Slider interaction is complete or dynamically as you scroll using the Slider Button
  • Move the Slider anywhere in your Workbook
  • Use Pattern Fills to emulate the Sliders in a RadiObutton style
  • Protect the Shape and Graphic Drawing Objects via VBA Code to prevent Selection








Installation

Open the SpecialistSliders.xlsm File and test out all of the Examples to see how they move and update the Defined Names (Named Ranges) with their respective values. In order to select any of the Shapes or Graphic Objects you must first Unprotect the Sheet. Please read all of the info on the individual Sheets and in this online documentation before you begin to edit and create your own Sliders or before turning to Support

Usage

SpecialistSliders.xlsm


Test the Demos (Example Sheets)
Before you start editing and trying to create your own Specialist Sliders, take some time to look at all of the Examples in the 'Example1' through to 'Example6' Sheets. There are a few subtle variations of the Specialist Sliders and the Scale Bars used for this Project. There are a few different ways that the Code is linked up via the Slider Buttons themselves to enable their use

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:



The Slider Defined Names or Named Ranges
In order for the Sliders to operate correctly, they all require a Defined Name or Named Range to be set up ie. Cell "F6" would have a Named Range of Slider1. You can set a Named Range using the Name Manager on the 'Defined Names' Group of the 'Formulas' Tab on the Ribbon. When setting up these references, ensure that if using multiple occurances of say, Slider1 in a Workbook on two Sheets, that you refer to the individual Worksheet for each separate occurance of the same Named Range ie. Slider1 = Example1$F£6, Slider1 = Example2!$F$6. The second Named Range used for each Vertical Slider (Optional) is one that restricts the Range ie. 1|5 would restrict the Vertical Slider Range to between 1 and 6. To allow this add a Named Range as 'Slider1Range' for a Slider with the number '1'. Named Ranges can be added to any Cells in a Visible or Hidden Worksheet. Named Ranges for the Specialist Sliders vary depending on each Example Sheet and may include, Slider1, Slider1Range (ie. 0|5), Slider1ManualSet (ie. 1, 2, 3 etc.), Slider1Step (ie. 3 for 3 Steps of the Slider), Slider1CursorDrag (ie. 10,000, the pull or drag effect on the Slider Button), Slider1ButtonDragSpeed (ie. 10 or 15, the speed when draggin the Slider Button) and Slider1Toggle (ie. TRUE or FALSE Boolean value indicating whether the Slider is on or off; for multiple Steps the SLider is considered on when the Step is > 0)

Setting Specialist Sliders Manually
You can use a Cell to directly change a Vertical Sliders position. The Code to do this is in all of the Worksheet Code Modules for any Example Sheet and will require a tiny bit of editing. We use the Worksheet_Change() Event Handler to capture when a Cell is edited so you just need to link your editable Cell up with the appropriate Slider - see the Code below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
   If Target.Address = "$J$5" Then
      SetHorizontalSlider "Slider1", Target.Value2, Target.Address
   ElseIf Target.Address = "$J$11" Then
      SetHorizontalSlider "Slider2", Target.Value2, Target.Address
   ElseIf Target.Address = "$J$16" Then
      SetHorizontalSlider "Slider3", Target.Value2, Target.Address
   End If
End Sub


Setting Specialist Sliders using VBA
You can also set Specialist Sliders using VBA. There are a couple of different methods. The Code to do this is shown below - simply Copy this Macro and link it up to your Vertical Slider Button (the settings to edit are marked out and you need to specify the Slider name ie. 'Slider1', the value to set, ie. '8', and the Range start and end ie. '1' and '10'. Please Note: this also updates the Named Range value and another Cell ie. one that you may be using to edit to set the Vertical Slider position

Public Sub SetHorizontalSliderByMacro()
   Dim SliderName As String
   Dim Value As Double
   Dim RangeStart As Double
   Dim RangeEnd As Double
   
   ' edit these settings
   Value = 50
   RangeStart = 0
   RangeEnd = 100
   SliderName = "Slider1"
   
   If Value < RangeStart Then Value = RangeStart Else If Value > RangeEnd Then Value = RangeEnd
   Dim SliderButton As Shape
   Dim SliderBar As Shape
   Dim SliderPlaceholder As Shape
   Set SliderButton = ActiveSheet.Shapes(SliderName)
   Set SliderBar = ActiveSheet.Shapes(CStr(SliderButton.Name & "Bar"))
   Set SliderPlaceholder = ActiveSheet.Shapes(CStr(SliderButton.Name & "Placeholder"))
   Dim SliderMin As Single
   Dim EndPosition As Long
   Dim ConstrainedValue As Double
   ConstrainedValue = (ScaleBetweenHorizontalSliders(Value, 0, 100, RangeStart, RangeEnd)) / 100
   SliderMin = SliderPlaceholder.Left
   EndPosition = SliderPlaceholder.Width - SliderButton.Width
   SliderButton.Left = SliderMin + (EndPosition * ConstrainedValue)
   SliderBar.Width = (EndPosition + (SliderButton.Width / 2)) * ConstrainedValue
   On Error Resume Next
   If ActiveSheet.Range(SliderName).Text Like "*%*" Then
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range(SliderName).Value2 = ConstrainedValue
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("$G$5").Value2 = ConstrainedValue
   Else
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range(SliderName).Value2 = Value
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("$G$25").Value2 = Value
   End If
   On Error GoTo 0
End Sub
Another option is to pass in the Setup values dynamically as in the Extensibility Specialist Sliders like this:

Public Sub SetExtensibilityStepSliderByMacro(ByVal Value As Double, ByVal RangeStart As Double, ByVal RangeEnd As Double, ByVal SliderName As String)
   If Value < RangeStart Then Value = RangeStart Else If Value > RangeEnd Then Value = RangeEnd
   Dim SliderButton As Shape
   Dim SliderBar As Shape
   Dim SliderPlaceholder As Shape
   Set SliderButton = ActiveSheet.Shapes(SliderName)
   Set SliderBar = ActiveSheet.Shapes(CStr(SliderButton.Name & "Bar"))
   Set SliderPlaceholder = ActiveSheet.Shapes(CStr(SliderButton.Name & "Placeholder"))
   Dim SliderMin As Single
   Dim EndPosition As Long
   Dim ConstrainedValue As Double
   ConstrainedValue = (ScaleBetweenHorizontalStepSliders(Value, 0, 100, RangeStart, RangeEnd)) / 100
   SliderMin = SliderPlaceholder.Left
   EndPosition = SliderPlaceholder.Width - SliderButton.Width
   SliderButton.Left = SliderMin + (EndPosition * ConstrainedValue)
   SliderBar.Width = (EndPosition + (SliderButton.Width / 2)) * ConstrainedValue
   On Error Resume Next
   If ActiveSheet.Range(SliderName).Text Like "*%*" Then
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range(SliderName).Value2 = ConstrainedValue
   Else
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range(SliderName).Value2 = Value
   End If
   On Error GoTo 0
End Sub


Specialist Slider Scales
When it comes to the Scales that sit beside a Slider, a couple of different options are shown. On the 'Example1' Sheet, the Sliders on this Sheet each use a Chart X or Y Axis with a single Series to create a Scale Bar that sits alongside the Slider. To view the Chart Series scroll to the right-hand side or Unprotect the Sheet, select the Chart Object and then 'Select Data' to see how I have linked the Sliders up to the Chart. It can be a litle tricky designing Sliders like this, but the look is nice. Another method is to simply use Cell Formatting as in the 'Example2' Sheet, the Sliders on this Sheet each use Cell Formatting to create a Scale Bar that sits alongside the Slider. For the Horizontal Slider the Column Width can be adjusted for spacing along with the Slider Placeholder Bar Width. For the Vertical Slider the Row Height can be adjusted for spacing along with the Slider Placeholder Bar Height. Try testing them out and seeing which you prefer. You can of course simply get your Slider working and then design a background Image placed behind the other normal 3 images required for a Slider as a Scale, which is what I do with Slider2 on the 'Example6' Sheet

Specialist Sliders

Specialist Horizontal and Vertical Sliders with quick Setup and Flexible Configuration, Example 2, Dragable Sliders using Chart Series for Bar Scales

Specialist Horizontal and Vertical Sliders with quick Setup and Flexible Configuration, Example 6, A Selection of different Sliders

Draggable Horizontal and Vertical Step Sliders, Example 3. When these Slider Buttons are dragged they exhibit a springy feel and will lock into situ to the nearest Step once the Mouse Button is released

Specialist Sliders using a Bespoke Image, Example 5

FAQ

Q. How do I set a default Slider value using Code or from a Cell entry?
A. See the 'Example1' Worksheet Code Module Sheet or read the Usage Section above


Specialist Sliders

This is a video of the Specialist Sliders in action. In this demonstration, I go through some of the examples in the download File. Music is 'Duh Fuse - French Fuse' · view the Specialist Sliders 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 identified

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

14.05.2022 - (Version 1)

General release of Specialist Sliders