Excel Productivity Guide
Useful Excel Tips | Keyboard Shortcuts | VBA Codes
Download this Free E-Book to take your excel skills to a whole next level using some smart Excel tactics.
10000+ Copies Already Downloaded
Today, we will learn that how to create an on-off button in excel.
Okay. So, Let’s start our hunt for an on-off switch.
It can help you to control two macros with one button. You can also use it to start or stop a macro.
I have inserted a simple shape to use as a button.
We need to write two macros to control both of the phases of the switch, on & off phase.
When our switch in off phase, we need a macro to on the switch(converting it to ON phase) by changing its position to the left, changing its color to green & changing its inner text to “ON”.
Now, It’s time to write a code to change its position, color & inner text on click. The below code will work perfectly fine.
'Select Button Worksheets(1).Shapes("Button").Select ' Change Position, Color & Text With Selection .ShapeRange.IncrementLeft 46 .ShapeRange.TextFrame2.TextRange.Characters.Text = "ON" .ShapeRange.Fill.ForeColor.RGB = RGB(0, 153, 0) End With
In the above code, I have fixed, its increment to left by 46, changed its text to “On” & changed its color to green.
Now, you have to assign the above macro to the switch, so that when you click on it, it will do all the things which we have written in the code.
And, now we have to write a code for the second phase of the switch (converting to off phase) which will include changing its position to the right, changing its color to red & changing its inner text to “OFF”.
The code will be.
'Select Button Worksheets(1).Shapes("Button").Select ' Change Position, Color & Text With Selection .ShapeRange.IncrementLeft -46 .ShapeRange.TextFrame2.TextRange.Characters.Text = "OFF" .ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0) End With
Here, we have to understand the assignment of both macros to the switch. “OnAction” property will help us here.
What we have to do is use the OnAction property to assign the second macro to the button once the first macro is executed & vice-versa.
Add this code at the end of “ON_BUTTON” macro to assign “OFF_BUTTON” macro to the switch.
Worksheets(1).Shapes("Button").OnAction = "OFF_BUTTON"
Add this code at the end of “OFF_BUTTON” macro to assign “ON_BUTTON” macro to the switch.
Worksheets(1).Shapes("Button").OnAction = "ON_BUTTON"
Now, we have a switch which will work perfectly fine. Have a look.
In the end, I have added code at the end of the both macros to un-select the switch once the code is executed. That is because, all the things which we have done with the shape is done after selecting it, so it is very important to un-select it.
Let me show you how above-created switch will work.
Just go with this sample file & explore what I have just done with this switch.
I believe you will find this switch very useful.
You can also do some dirty things with like change its shape, change the increment direction, etc.
So, hit the comment box with your stuff.