Today, I’d like to share with your simple steps to create an on-off button in Excel.
I have used this button to create a greeting card for Christmas.
So, let’s start our hunt for an on-off switch/button.
What Will it Do?
It can help you to control two macros with one button. You can also use it to start or stop a macro.
But, Some Points to Consider
- It should change its position on click.
- It should change its color on click.
- It should change its color on click.
How to create an on off switch?
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.
Worksheets(1).Range("AC1").Activate
How it all works?
Let me show you how above-created switch will work.
Sample File
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 things like changing its shape, change the increment direction, etc.
MERRY CHRISTMAS.
VBA is one of the Advanced Excel Skills, and if you are getting started with VBA, make sure to check out there (What is VBA, Excel Programming, and Useful Macro Examples and VBA Codes).
About the Author
Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.
How can I open Insert object file from VBA code.
kindly provide us VBA code for open file.
thank you
Thank you for your hard work and sharing.
Best wishes.
Very nice work, Thanks.
very nice
Thanks puneet
Hey thank you Puneet.
Can you show all the code put together please? Are you creating two subs? when you add things to the end of your macros is that coming before end sub?
Download sample file, code is there.
How can you create the same effect in a userform in Excel?
Hey thank you Puneet. This is an example which can come in very handy
Thanks
Puneet! I enjoyed your ON OF Button. This is a great idea and I like to use it in different applications where the user can select one out of two!
I have made some modifications, to have one macro. What do you think of this macro?
Sub ON_BUTTONx()
‘Macro By ExcelChamps.com
‘Select Button
Worksheets(1).Shapes(“Button”).Select
‘ Change Position, Color & Text
If Selection.ShapeRange.TextFrame2.TextRange.Characters.Text = “OFF” Then
With Selection
.ShapeRange.IncrementLeft 46
.ShapeRange.TextFrame2.TextRange.Characters.Text = “ON”
.ShapeRange.Fill.ForeColor.RGB = RGB(0, 153, 0)
End With
Else
With Selection
.ShapeRange.IncrementLeft -46
.ShapeRange.TextFrame2.TextRange.Characters.Text = “OFF”
.ShapeRange.Fill.ForeColor.RGB = RGB(255, 0, 0)
End With
End If
‘Assign OFF_BUTTON macro after changing position
‘Worksheets(1).Shapes(“Button”).OnAction = “OFF_BUTTON”
‘Macro to Run when switch is ON
‘Call StartCelebration
‘Activate some other cell after completion
Worksheets(1).Range(“AC1”).Activate
End Sub
Torstein, Thanks for your words. Your code is outstanding, You just have to incorporate two macros in your conditions. So that, once condition change macro will also change along with it.
Please Subscribe to our newsletter to get all the stuff in your mail box.
With Love PG