Making a slick on/off switch using Excel & little bit of VBA [case study]

Posted on December 5th, 2013 in VBA Macros - 25 comments

I have a confession to make.

I am not sure how to describe this new thing I made in Excel / VBA. So first take a look at it.

On off swtich linked to a chart - Made with Excel & VBA - Demo

Inspiration for the on/off switch

As you know, there is a form control in Excel that behaves like on/off switch. It is called check box. Although they are easy to use, check boxes are not very slick. So I though, why not make an on/off switch like the ones we see in our iPhones / tablets. And the rest is what you see.

How to make this on/off switch using Excel & VBA

  1. Create an on/off switch using drawing tools in Excel insert ribbon. I used a rounded rectangle for outer container and small circle for switch.
  2. Select the circle, name it using namebox (top-left in formula bar). I called mine as btnToggle.
  3. Write a macro that takes care of the switch behavior. The macro looks like this:
    1. Change a cell value to true or false based on the switch position.
    2. Move the btnToggle from left edge to right edge (or reverse) in a loop.
    3. Change switch color to GREEN or dark black based on its position.
  4. Use the linked to cell value to make changes to your charts / dashboards / formulas as needed.
  5. That is all!

Video tutorial – On/off switch using Excel & VBA

To make you even more awesome, I made a short video tutorial explaining the whole thing. Watch it below:

Note: This video shows another example of on/off switch (not the chart one you see above), but equally awesome.

Alternative ways to watch this video – on our YouTube Channel or Facebook Fan Page.

Download Example Workbook

Click here to download the example workbook. Play with it to understand this concept better. Examine the moveBtn2() macro to learn more.

Do you like the on/off switch concept?

I had fun making this. It looks great and makes my workbook attractive. But I find one nagging problem with it. You have to set up multiple macros if you want several of them in a workbook. Of course there is a work around. With a little bit of clever programming we can make one macro that can talk to all on/off switches and update their individual linked cells. We will save it for another day.

What about you? Do you like the on/off switch concept? How are you planning to use it? Go ahead and tell me in comments.

Learn more about VBA from these examples

If you like the on/off switch example, you are going to love these other examples.

Your email address is safe with us. Our policies

25 Responses to “Making a slick on/off switch using Excel & little bit of VBA [case study]”

  1. Pete says:

    Now that is one slick trick! I can use this in many of the dashboard that I am building. Thanks Chandoo!

  2. Khushnood Viccaji says:

    I have only this to say :)

  3. Ninad Pradhan says:

    Neat. very neat. Thanks.

  4. Hamy says:

    I am not sure if this is done, but websites like make avail so many nice codes and tips, that needs to be reviewed more than once. I have tried storing them in programs such as Treepad, OneNote but its has not been very effective.. May be you can write a blog making your point and allowing people to comment on a best way to catalog these codes

    • Chandoo says:

      I usually rely on browser bookmarks to save my favorite pieces code / technique.

    • Khushnood Viccaji says:

      I have a whole bunch of xls and bas files saved in a separate folder, with all the sample code and Excel features that can be used for various purposes.
      These files are named in as detailed manner as possible using keywords to indicate the feature or purpose of the code.

      After that, whenever I need to use some code or feature, I use a small but powerful filename search utility, ‘Everything’ from http://www.voidtools.com, to locate the required file.

      E.g. in another thread I had asked Chandoo about how to get the color index for conditionally formatted cells in a UDF.
      I have now saved the details in a file named “CELLS_Color Index Conditional Formatting CF Colors Chip Pearson.bas” for future use.

  5. Gene says:

    how do you define the moveBy variable?

    • Chandoo says:

      I started macro recorder and moved a drawing shape by using left arrow key. The increment left value was 0.75. That is how I arrived at the value for moveby.

  6. Darin Myers says:

    Had an idea to add a fade effect to the example workbook for a smoother transition. (Code Below) There are a few other improvements I have in mind to improve the abstraction and re-usability of the control, but this was just a quick and fun addition.

    Also, I have the same question as Gene: how did you determine the moveby value. Was it a mathematical solution or more experimental?

    ”’BEGIN CODE”’

    Option Explicit

    Sub moveBtn2()
    Dim objToggle As Shape ‘Toggle Button
    Dim objHide As Shape ‘Rectangle to hide content
    Dim CellLink As Range ‘Provide a cell link that can be used in formulas

    Dim moveBy As Double
    Dim transBy As Double
    Dim i As Long
    Dim iSteps As Long

    Const ON_COLOR As Long = 5287936 ‘Green RGB(0, 176, 80)
    Const OFF_COLOR As Long = 2171169 ‘Black RGB(33, 33, 33)

    Set objToggle = ActiveSheet.Shapes(“btnToggle”)
    Set objHide = ActiveSheet.Shapes(“shpHideSale”)
    Set CellLink = ActiveSheet.Range(“F3″)

    iSteps = 25
    moveBy = 0.75
    transBy = 1 / iSteps

    If objToggle.Fill.ForeColor.RGB = OFF_COLOR Then ‘Turn On
    CellLink = True
    objToggle.Fill.ForeColor.RGB = ON_COLOR
    Else ‘Turn Off
    CellLink = False
    moveBy = -1 * moveBy
    transBy = -1 * transBy
    objToggle.Fill.ForeColor.RGB = OFF_COLOR ‘Black
    End If

    For i = 1 To iSteps
    objToggle.IncrementLeft moveBy
    objHide.Fill.Transparency = objHide.Fill.Transparency + transBy
    DoEvents
    Next i

    objHide.Visible = Not (objHide.Fill.Transparency = 1)

    End Sub

  7. Chandoo says:

    Thanks all for the love. I am glad you like this :)

  8. vishwanath says:

    Hi ,

    I am not able to get the “Hand symbol” to the cursor , when i click on the Round circle it will just SELECT and goes to design mode

  9. Oz says:

    Thanks for this. I’m playing with this and have the ball traveling in a square. One thing doesn’t make sense:

    What is the relationship between moveBy and increment?
    Moving either one of the variables changes the distance the ball travels. I thought i would only impact speed but it does impact the distance.

    What’s going on?

    • Chandoo says:

      There is no way (well, almost) to control the speed. It just depends on how fast your Excel is. The moveBy tells Excel how many pixels to move each time the line runs.

      • Oz says:

        Right. I was able to find out that the moveBy says how far to move. the i say how many times to move. that was the mystery for me.

        • Test says:

          Try adding timedelay

          Application.Wait Now + TimeSerial(0, 0, 1)

          It worked for me
          With ActiveSheet.Shapes.Range(Array(“btnToggle”))
          For i = 1 To 25
          .IncrementLeft moveBy
          Application.Wait Now + TimeSerial(0, 0, 1)
          DoEvents
          Next i
          End With
          After some code as well if you are moving the object then it should work
          Move the object code
          Task Code
          Move
          Code
          Move
          end

  10. […] Making a slick on/off switch using Excel & little bit of VBA […]

  11. Amzo says:

    Chandoo,
    Please teach us how to construct the ACME inc Chart. I have attempted but with no success :(

  12. Amzo says:

    Chandoo,
    Please teach us how to construct the ACME inc Chart. I have attempted but with no success :(

  13. Randy says:

    This works great in 2010 and above but i get a Run Time error on the “.IncrementLeft moveBy” line in 2007. Many of my clients still use older versions of Excel. Any ideas?
    Thanks so much for the great articles and info.

  14. Randy says:

    Ahhh.. Works in 2007 when the sheet is unprotected, but the bug only appears when protection sheet is on because the btnToggle shape is locked. So either unlocking this shape, or unprotecting and reprotecting the sheet in the VBA code will solve this bug. (In case anyone else ran into this issue)
    Thanks for the great button. I will be using it

  15. Randy says:

    (last comment)
    Also for a smother, slower action, you can try this:
    If ActiveSheet.Shapes.Range(Array(“shpHideSale”)).Visible Then
    moveBy = -0.095
    With ActiveSheet.Shapes.Range(Array(“btnToggle”)).Fill
    .ForeColor.RGB = RGB(33, 33, 33)
    End With
    Else
    moveBy = 0.095
    With ActiveSheet.Shapes.Range(Array(“btnToggle”)).Fill
    .ForeColor.RGB = RGB(0, 176, 80)
    End With
    End If

    With ActiveSheet.Shapes.Range(Array(“btnToggle”))
    For i = 1 To 200
    .IncrementLeft moveBy
    DoEvents
    Next i
    End With

  16. John Hackwood says:

    Hamy
    I long struggled with your question on the storage of tips and code and accessing them. I couldn’t find the one answer so I came up with a 3 prong approach….

    1) I use Excel as a flat database with columns & dropdowns defining the broadest grouping say VBA, Formulas, Excel Application, Pivot Tables eg “VBA”, then the next column broad grouping eg “Copying Ranges” , then another eg “within the same workbook” and then the detail of the tip plus links if any plus a comment box for long entries. Autofilter and Ctrl+F find allows fairly good access. Perhaps there are other tools but if you are an Excel believer you have to make this work and make it a project in itself that you tinker with over time. I tried MindMaps and MS Notes in the past but it gets all too hard as the topic is just too big.
    2) I save more complex tips or articles with illustrations as web archive files in structured windows folders – then search these with Windows or maybe there are better tools like Ultrasearch
    3) Use MZ Tools to save VBA code snippets – this is a double up for me as these snippets are also in my Excel file usually in a comment box associated with a topic but I reserve the code I put into MZ Tools (fantastic tool for VBA it really is) as the standard I use for a particular situation.

    Hope this makes sense….if there are better approaches out there I am all ears.
    Cheers
    John Hackwood

  17. Dhiraj says:

    Hi Chandoo,

    As much as I love going through your new Excel templates and tutorials, I feel that your instructions are almost always incomplete. It is may be because I am a novice and will take some time to get a hang of it. I know you are trying to make everyone awesome in Excel but you seem to be targeting on the advanced users.

Leave a Reply