How to VBA ON-OFF Button in Excel

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 some dirty things with like change its shape, change the increment direction, etc.

So, hit the comment box with your stuff.

MERRY CHRISTMAS.

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 ﬁnd him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.

Learn Excel (Videos)

Connect with Puneet