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.
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
- 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.
- Select the circle, name it using namebox (top-left in formula bar). I called mine as btnToggle.
- Write a macro that takes care of the switch behavior. The macro looks like this:
- Change a cell value to true or false based on the switch position.
- Move the btnToggle from left edge to right edge (or reverse) in a loop.
- Change switch color to GREEN or dark black based on its position.
- Use the linked to cell value to make changes to your charts / dashboards / formulas as needed.
- 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.
30 Responses to “Making a slick on/off switch using Excel & little bit of VBA [case study]”
Now that is one slick trick! I can use this in many of the dashboard that I am building. Thanks Chandoo!
I have only this to say 🙂
http://www.youtube.com/watch?v=1zj418rKTqU
Neat. very neat. Thanks.
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
I usually rely on browser bookmarks to save my favorite pieces code / technique.
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.
how do you define the moveBy variable?
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.
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
Good idea.. 🙂
Thanks all for the love. I am glad you like this 🙂
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
The macro is attached to the outer rounded rectangle. try clicking on that.
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?
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.
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.
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
[…] Making a slick on/off switch using Excel & little bit of VBA […]
Chandoo,
Please teach us how to construct the ACME inc Chart. I have attempted but with no success 🙁
Chandoo,
Please teach us how to construct the ACME inc Chart. I have attempted but with no success 🙁
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.
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
(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
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
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.
Hi Chandoo,
Thanks so much for this toggle switch idea! I have used it a bunch of times and it works great.. the one problem I find though is that if you are zoomed in or out on your spreadsheet, then the button breaks and ends up out of wack.. any way you can think of addressing this?
Thanks again!
This is very cool! Just borrowed the idea for one of my work. Wanted to say thanks!
Chandoo,
I use these slide buttons all the time. They work so much better than any activeX control. And they look much better too!
However, I have a question. I want to write code that checks the buttons (I have 30 of them on the same worksheet) to see if they are on or off. If they are on, turn them off (on is green, off is black), but if they are off, leave them off and ignore that button. How can I do that?
Could I get to download the graph example, which is displayed in the beginning?
regards
Could I get to download the graph example which is displayed in the beginning?
Thanks