Making a slick on/off switch using Excel & little bit of VBA [case study]
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.
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.
- 3D Dancing pendulums simulated in VBA
- Clocks ant timers using Excel & VBA
- A replacement for scrollbar control – picture calendar case study
- More VBA examples (40+)
My name is Chandoo. Thanks for dropping by. My mission is to make you awesome in Excel & your work. I live in Wellington, New Zealand. When I am not F9ing my formulas, I cycle, cook or play lego with my kids. Know more about me.
Thank you and see you around.
Leave a Reply
|« Holiday Sale is on, Save upto $50 on your favorite Excel courses||Creating Triangular Plots using Excel »|