Sliders

Purchase · £2.99

Introduction

Introducing Sliders

Sliders - Bars with a Button that you can Slide back and forth to increase or decrease a value for a Range or Defined Name for Excel

Sliders are small bars with Buttons that you click and then drag left or right to increase or decrease a value range from 1 to 100. Slider can have a coloured Slider Bar that sits underneath them and expands or contracts as the Slider Button is dragged. Slider Shapes are linked to Macros that enable their movement and can be Zoomed in a Worksheet. Sliders can be set dynamically either by Cell adjustment or via a VBA Macro. From Version 1.4 Sliders_2022 use rewritten, advanced Code to ease the setup, design and implementation of the Slider Shapes

If you like this Project you may also enjoy


Specialist Sliders ·  Vertical 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. Also, the ability to edit the Code and adjust some Constants in the Macros for bespoke Sliders
  • Windows PC · NOT a Mac!
  • No support is provided for customization of this Software

Features

  • From Version 1.4 I have changed the Code to allow you to dynamically create the minimum and maximum settings without any user interaction. This means that you can simply Copy the 3 Shapes required for each 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
  • 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
  • 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
  • Adjust the length of travel of the Slider and output the value or choose to maintain between a fixed range ie. 0-100 or 1-100 (useful as a percentage ie. 50 would equal 50%)
  • Put the Slider ontop of a coloured Bar that expands or contracts as you drag the Slider Button along
  • 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

Sliders.zip contains older Sliders and the very latest Sliders_2022.xlsm (Advanced Sliders). Right-click and choose Extract All to extract the Files from the Zip Archive. Open the Sliders.xlsm File and test out the Sliders on all of the Example Worksheets 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. Now open the Sliders_2022.xlsm and test all of the very latest Sliders with quick setup and flexible configuration. You will love these ones - you can simply create 3 Shapes and name them SliderX, SliderXBar and SliderXPlaceholder where X is the next number in the Sliders used in your Project - then drag the Slider Button and you are good to go! 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

Sliders.xlsm


Test the Demos
Before you start editing and trying to create your own Sliders, take some time to look at all of the Example Sheets in the Sliders file. There are many different variations of Sliders in the file, from simple Shapes that you overlay to more complex Grouped Shapes with effects

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. There are a couple of Sliders on this 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 like this:



The Slider Defined Names or Named Ranges
In order for the Sliders to operate correctly and output values as they move back and forth, they all require a Defined Name or Named Range to be set up ie. Cell "E6" 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$E£6, Slider1 = Example2!$E$6

Example1 (default)
This Sheet demonstrates a very simple approach to creating and using a Slider in Excel that can be pressed by the Mouse and dragged along to output a value to the Sheet. This Sheet has a Zoom of 150% for better visibility of the Sliders (I do this throughout, but they can viewed at any Zoom size where they can be successfully operated by the Mouse). Click and drag the Sliders using the Mouse Button to update the values for their Defined Names from 1 to 100. These Sliders update the Defined Names after you finish dragging. Notice the naming conventions of these Sliders for the Macros that they link to. You need to ensure that you give the Slider Button the same Name as your Defined Name ie. Slider1, Slider2 etc. These Sliders link to the 'Slider()' Macro in the 'mdSliders' Code Module. All of these Objects have their Properties set to 'Move but don't size with cells'. These Slider Buttons and Bars can be moved anywhere on the Worksheet and the Code will constrain the Button to the parameters set in the Slider() Macro - don't believe me? Let's try it! Unprotect the Sheet. Select the Slider1Bar Shape and drag it somewhere on the Sheet. Now select the Slider1 Shape and position it centrally to the Bar at any position along the Bar. Now drag the Slider back and forth:



Example2 (default)
This Sheet is exactly the same as the Example1 Sheet, however the Zoom is set to 100% and the Sheet background is set to No Fill with Gridlines switched Off

Example3 (background bars)
This Sheet now bring in the Background 'Bars' that overlay the 'Placeholder Bars'. What this means is that when the Slider is dragged back and forth the Bar will also expand and contract with the Slider Button to give the effect of turning the Slider up higher or moving the value of the Slider up higher but more visually than just outputting the value. Editing the Shapes is done in exactly the same way on this Sheet, however there is one extra Shape. The Shapes are named Slider1, Slider1Bar and Slider1Placeholder and they use the 'SliderAndBar()' Macro in the 'mdSliders' Code Module. Remember to name them consecutively for each new set of Shapes that make up each Slider



Example4 (different effects)
Example4 Sheet is similar to Example3 Sheet, however it adds different effects to the Slider Button, Background Bars and Placeholders. You can use any of the effects on the 'Shape Format' Tab on the Ribbon such as Shadow, Glow and Bevel. I went for a shiny, more metallic look and messed around with the gradient fades - here is what I made:



Example5 (RadiObutton style)
The Sliders on the Example5 Sheet are designed similar to the Buttons in my RadiObutton Project. With three bold colours and patterns for the Slider Button Fill, they look really quirky. All of these Sliders update their respective Defined Names directly as you Scroll the Slider Button. The Fill Paterns are changed by Formatting the Slider Button, Shape 'Slider1', 'Slider2' etc. Right-click Format Shape and go to Fill, Pattern Fill, select a Pattern and then update the Fill with a bold colour, like this:



Example6 (RadiObutton style Grouped Sliders)
Okay so now we move from Sliders where each set of Sliders are made from separate Shapes to sets of Shapes that are then Grouped into one easily, moveable Shape Object. You can Nest Shapes, but none of my demos currently show you how to do this even though it is perfectly possible. Anyhow, unprotect the Sheet, hover near the Placeholder Shape and left-click to grab the Grouped Shape. Now move it around the Workbook and then try dragging the Slider Button back and forth again. These Sliders have their Slider Button linked to the 'GroupedSliderAndBar()' Macro in the 'mdGroupedSliders' Code Module. The naming convention of the Shapes are GroupedSlider1 (main Grouped Shape), Slider1 (the Button), Slider1Bar (the coloured Bar) and Slider1BarPlaceholder (the background Bar that holds the coloured Bar). Whenever Ungrouping these set of Shapes always remember to set the Properties of the Grouped Shape to 'Move but don't size with cells' otherwise some parts of the Sliders may move when you drag the Slider back and forth. You can also drag the Defined Name Cell around next to your moved Slider position as it will always relate to the same name as the Slider Button ie. Slider1, Slider2, Slider3 etc.



Example7 (Bespoke)
Without doubt, my most favourite Sheet, Example7 demonstrates a variety of different Sliders and respective output scenarios that you can use. You have a longer bar with an output measured in Percentage (%) so Off is 0% and fully On is 100%, a longer bar with an output measured in numbers so Off is 1 and fully On is 310, a longer bar with a direct output whenever the Slider is moved and increased Speed, a resized bar with a direct output whenever the Slider is moved, a Rectangular Slider Button and a Slider that constrains values between 0-10 (see Code for 1-10). All of the Sliders have been slightly tweaked and they link up to various Bespoke Macros, specified on the right-hand side. Here are all of the Bespoke Sliders - I have selected the thin grey Slider with the Outline Circle Slider Button using the Selection Pane:



Example8 (Slider Toggle Button style)
The Sliders on the Example8 Sheet have been desinged like my Slider Toggle Buttons Project. The naming convention of the Shapes are GroupedSlider1 (main Grouped Shape), Slider1Icon (the Icon), Slider1 (the Button), Slider1Bar (the coloured Bar) and Slider1BarPlaceholder (the background Bar that holds the coloured Bar) and Slider1Container (the outer most container that holds everything. The Sliders on this Sheet use the 'GroupedToggleSliderAndBar()' Macro in the 'mdGroupedSliders' Code Module. This Macro has been heavily modified to take account of the Icon and Button, where both Icon and Button Shapes must have the same Macro linked - which ever Shape, Icon or Button is pressed, the Code then assigns the respective Variables to the Objects. The Icon is a standard Graphic Object, behaving similar to a normal Shape, however you can load in any of the SVG Icons that come with Excel 365 - here I am changing the default Burger Icon to a Hand Press Icon:



Copying a Slider into your own Project
This is a relatively easy process. Open your Project and the Sliders.xlsm file - your project should also be a Macro-enabled file. Now Unprotect one of the Sheets in the Sliders.xlsm file. Select all of the Shapes that make up a Slider set or a Grouped Slider. Copy. Paste into your own Project. Add a Defined Name to your Project ie. Slider1, Slider2 etc. which should match the numeric numbering of the set of Shapes or Grouped Shape that you just copied. Now in the Slider.xlsm file click on a Button or Icon and use right-click 'Assign macro' to see what Macro is assigned - click Edit to enter the Code. Drag the respective Code Module into your own Project and then link up the Icon or Buttons for the Slider that you just copied into your own Project. You can drag the Code Modules easily using the Project Explorer Window in the Code, drag from one Workbook into another - simple! Okay so now test your Slider

SVG Icons
The Icon Buttons use an SVG Icon. To change the Icon, you first need to Unprotect the Worksheet. Then right-click the Graphic Object or the Icon Shape and use the Selection Pane on the Editing Group of the Home Tab of the Ribbon. Now choose, change Graphic, from Icons on the Change Group of the Graphics Format Tab on the Ribbon and pick another Icon

Tips
Resizing the Grouped Sliders to create smaller ones:
Unprotect the Worksheet and select one of the Grouped Button Shapes. Hold down Shift and then drag the object to resize it. Please note: you will have to tweak the positioning of the Bars and Placeholders and/or use the Align Shape on the Arrange Group of either the Graphic Format or Shape Format Tab on the Ribbon. You may also have to adjust the Code to take account of your changes



Setting the Slider Button Position by Cell entry or via a Macro
You can do both. If you want to set the Slider Button by capturing a user Cell entry then you can do this via the Worksheet Code Module and the Code in Example 9 Sheet in Version 1.3 of Sliders. This is the Code that handles the Cell change (Please note: Cell "$B$9" is the Cell that will be changed by a user, the EndPosition is a Constant set in the Code Module where the Sliders Code is added and a Named Range is used to update the Percentage output for the new position which can be removed if required so you do not have it twice on a Sheet): 

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
 If Target.Address = "$B$9" Then
  
   Dim SliderButton As Shape
   Dim SliderBar As Shape
   Dim Min As Long
   Dim Percentage As Double
   Dim NewXPosition As Long

   If Target.Value2 = vbNullString Then Percentage = 0 Else Percentage = Target.Value2
   NewXPosition = EndPosition * Percentage

   Set SliderButton = ActiveSheet.Shapes("Slider1")
   Set SliderBar = ActiveSheet.Shapes(CStr(SliderButton.Name & "Bar"))
   Min = SliderBar.Left - 1
   SliderButton.Left = Min + NewXPosition
   SliderBar.Width = 1
   SliderBar.Width = SliderBar.Width + NewXPosition
   ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("Slider1").Value2 = Percentage
  
 End If
End Sub
To do the same thing via a Macro you can use Code like this (the first example lets you set this in the Code ie. Percentage = 0.5 and the second picks up the Percentage values from the Cell that may have changed): 
    
' SetSliderByMacro, allows you to set the Slider position via a VBA Macro ie. 0.5 or 50%
Public Sub SetSliderByMacro()

   Dim SliderButton As Shape
   Dim SliderBar As Shape
   Dim Min As Long
   Dim Percentage As Double
   Dim NewXPosition As Long

   Percentage = 0.5
   NewXPosition = EndPosition * Percentage

   Set SliderButton = ActiveSheet.Shapes("Slider1")
   Set SliderBar = ActiveSheet.Shapes(CStr(SliderButton.Name & "Bar"))
   Min = SliderBar.Left - 1
   SliderButton.Left = Min + NewXPosition
   SliderBar.Width = 1
   SliderBar.Width = SliderBar.Width + NewXPosition
   ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("Slider1").Value2 = Percentage

End Sub

           
' SetSliderByCell, allows you to set the Slider position via a VBA Macro and Cell Value ie. 0.5 or 50%
Public Sub SetSliderByCell()

   Dim SliderButton As Shape
   Dim SliderBar As Shape
   Dim Min As Long
   Dim Percentage As Double
   Dim NewXPosition As Long

   Percentage = ActiveSheet.Range("B9").Value2
   NewXPosition = EndPosition * Percentage

   Set SliderButton = ActiveSheet.Shapes("Slider1")
   Set SliderBar = ActiveSheet.Shapes(CStr(SliderButton.Name & "Bar"))
   Min = SliderBar.Left - 1
   SliderButton.Left = Min + NewXPosition
   SliderBar.Width = 1
   SliderBar.Width = SliderBar.Width + NewXPosition
   ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("Slider1").Value2 = Percentage

End Sub

Sliders

Sliders 2022 - New Advanced Sliders with quick Setup and Flexible Configuration

Example1 - the default Sliders

Example3 - the Sliders with a coloured Bar that moves alongside them as they are dragged by the Mouse

Example5 - the Sliders in a RadiObutton style

Example7 - bespoke Sliders in a variety of different sizes and styles

FAQ

Q. How do I set a default Slider value using Code or from a Cell entry?
A. Version 1.3 now has an Example that details how to do both - check out the Changelog or the Usage Section

Sliders

This is a video of the Sliders in action

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

* Test converting the Cursor Position into the correct Screen Position to keep it a lot closer to the Sliders when scrolling with the Mouse

Validate whether when grouping Shapes the Code needs to maintain the Psuedo Top Positions for the Objects to fix the Zoom issue where Grouped Shapes may move - setting the Grouped Shape Properties to 'Move but don't size with cells' may mitigate this requirement in the Code * Update on the Shape Grouping, only the main Grouped Shape requires the Psuedo fix for its .Top member to correct the issue when Zooming

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

01.07.2022 - (Version 1.4)

Rewritten Code for a New Version 1.4 (Sliders_2022)
Optimise and reduce the Code in the Pause() Subroutine, used to stop time ;)
Change the Code to allow us to dynamically create the minimum and maximum settings without any user interaction. This means that you can simply Copy the 3 Shapes required for each Slider, rename them Slider2, Slider2Bar, Slider2Placeholder, format them how you like and then just drag the Slider Button to see your masterpiece in action
Optimise the Code to allow us to set the Slider Button as a Percentage of the Width of any designed Slider Bar and Slider Placeholder
Allow the use of a Cell value to change both the Slider Button position and update a Named Range
You can change the Custom Cell Format to be Percentage (%) or Numeric and the Code will detect the underlying Cell Format
You can now set the range of your Slider dynamically using a Named Range. 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

02.05.2022 - (Version 1.3)

Changed all of the Public Pause() SUbroutines to Private as I realised that I had exposed multiple occurences of the same Sub!
Tidied up the Code in one of the Examples, Bespoke4 for declarations that were not required
Added an additional Sheet Example 9 (Set by Cell or VBA) to demonstrate setting a Slider Button position (or value as a Percentage) either by a Cell entry capturing the specific Cell that has changed in the Worksheet Code Module or via a VBA Macro that can either set to a predetermined value or also set to the Cell value entered

04.07.2021 - (Version 1.2) Not Released!

Sliders 1.2 was an experiment into using Twips to convert the Cursor position from Screen Points and to use Win32API to Update the Excel Workbook Window. Only partial success with some smoothing at faster speeds, hoever this and did not really improve the feel of the Drag on the Slider Buttons so I have set this version aside for now

03.07.2021 - (Version 1.1)

Complete rewrite of all of the main Subroutines as it was too difficult for new users to setup the Shapes and get them working correctly in all positions in their own Workbooks. Now the relevant Macros behind the Sliders take their settings from the Bar Left Position and then fit the Buttons around this. The internal Code settings are easier to adjust and understand when considering the overall distance that you want to allow your Slider to travel. The use of a ScaleBetween() function also keeps the min and max within a specific range of 0-100 or 1-100 as in value or percent (%). You can still output numbers ie. 1-200 by adjusting the Code and I have added comments to do this and adjusted the online help documentation

01.07.2021 - (Version 1)

General release