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.

Worth $20, Absolutely Free

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.

how to create an on off button in excel

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?

On off button

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”.

on off button

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.
ON-OFF-Switch-In-Excel - A-Macro-Button6In 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.

ON-OFF-Switch-In-Excel - A-Macro-Button5

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. 

  • Torstein S Johnsen

    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

    • Puneet Gogia

      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

  • Inet Kemp

    Hey thank you Puneet. This is an example which can come in very handy

    • Puneet Gogia

      Thanks

  • Sheepdisease

    How can you create the same effect in a userform in Excel?

  • Seth

    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?

    • Puneet Gogia

      Download sample file, code is there.

  • Navnath Phapale

    Hey thank you Puneet.