Introduction

Introducing Vertical Sliders

Vertical 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 Optional Bar Scale)

Vertical 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. A Vertical Slider has 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. Vertical Sliders use advanced Code to ease the setup, design and implementation of the Slider Shapes

If you like this Project you may also enjoy

Specialist 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 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
Use Pattern Fills to emulate the Sliders in a RadiObutton style
Protect the Shape and Graphic Drawing Objects via VBA Code to prevent Selection

Advanced Vertical Sliders with minimum Setup and flexible Useage

Installation

Open the VerticalSliders.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

VerticalSliders.xlsm

Test the Demos (Example Sheets)
Before you start editing and trying to create your own Sliders, take some time to look at all of the Examples in the 'Example1' Sheet. There are a few subtle variations of the Sliders and the Scale Bar I have designed and used for this Project is on the 'Scale Bars' Sheet

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

Setting Vertical Sliders Manually
You can use a Cell to directly change a Vertical Sliders position. The Code to do this is in the Worksheet Code Module 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 = "$G$6" Then
      SetSlider "Slider1", Target.Value2, Target.Address
   ElseIf Target.Address = "$B$31" Then
      SetSlider "Slider2", Target.Value2, Target.Address
   ElseIf Target.Address = "$C$31" Then
      SetSlider "Slider3", Target.Value2, Target.Address
   ElseIf Target.Address = "$G$31" Then
      SetSlider "Slider4", Target.Value2, Target.Address
   End If
End Sub

Setting Vertical Sliders using VBA
You can also set a Vertical Slider using VBA. 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 SetSliderByMacro()
   Dim SliderName As String
   Dim Value As Double
   Dim RangeStart As Double
   Dim RangeEnd As Double

   ' edit these settings
   Value = 8
   RangeStart = 1
   RangeEnd = 10
   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 = (ScaleBetween(Value, 0, 100, RangeStart, RangeEnd)) / 100
   SliderMin = SliderPlaceholder.Top
   EndPosition = SliderPlaceholder.Height - SliderButton.Height
   SliderButton.Top = SliderMin + (EndPosition * ConstrainedValue)
   SliderBar.Height = (EndPosition + (SliderButton.Height / 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$6").Value2 = ConstrainedValue
   Else
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range(SliderName).Value2 = Value
      ThisWorkbook.Worksheets(SliderButton.Parent.Name).Range("$G$6").Value2 = Value
   End If
   On Error GoTo 0
End Sub

Vertical Sliders

Vertical Sliders with quick Setup and Flexible Configuration

Bar Scales

Bar Scale images that can be used together with the Vertical Sliders

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

Vertical Sliders

This is a video of the Vertical Sliders in action. In this demonstration, I go through each of the examples on the 'Example1' Sheet. Music is 'Don't Wanna Let Myself Down (Instrumental)' · view the Vertical 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


07.05.2022 - (Version 1)

General release of Vertical Sliders