How to VBA ON-OFF Button in Excel

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.