VBA – Create a toggle button with code

Posted on December 20, 2014 by

Sometimes, you may want a button to perform different actions. It is quite easy to make one with an On/Off, but when you would like to make a button, performing 4 different actions in VBA, the story goes a little more interesting. So, here is a small gif, which I have built, that sums, divides, subtracts and multiplies numbers and thus performing 4 different actions with one button.

So, how is the magic done? 🙂 You probably think that there is a global variable, assigned with value, holding the next action? (If you really thought of this, write in the comments below). Actually, the way it is done is a little more trivial – The code changes the caption of the button and based on its caption, a code changing the cells on line 12 is executed.

Here is how the code looks like:

Option Explicit

Sub Calculate()
    Application.ScreenUpdating = False
    With ActiveSheet.Buttons(Application.Caller)

        If .Caption = "Sum" Then
            Cells(12, 1).Value = "=R[-2]C+R[-1]C"
            Cells(12, 2).Value = "=R[-2]C+R[-1]C"
            Cells(12, 3).Value = "=R[-2]C+R[-1]C"
            Cells(9, 1).Value = .Caption
            .Caption = "subtract"

        ElseIf .Caption = "subtract" Then
            Cells(12, 1).Value = "=R[-2]C-R[-1]C"
            Cells(12, 2).Value = "=R[-2]C-R[-1]C"
            Cells(12, 3).Value = "=R[-2]C-R[-1]C"
            Cells(9, 1).Value = .Caption
            .Caption = "Multiply"

        ElseIf .Caption = "Multiply" Then
            Cells(12, 1).Value = "=R[-2]C*R[-1]C"
            Cells(12, 2).Value = "=R[-2]C*R[-1]C"
            Cells(12, 3).Value = "=R[-2]C*R[-1]C"
            Cells(9, 1).Value = .Caption
            .Caption = "Divide"

        ElseIf .Caption = "Divide" Then
            Cells(12, 1).Value = "=R[-2]C/R[-1]C"
            Cells(12, 2).Value = "=R[-2]C/R[-1]C"
            Cells(12, 3).Value = "=R[-2]C/R[-1]C"
            Cells(9, 1).Value = .Caption
            .Caption = "Sum"
        End If  
    End With

    Application.ScreenUpdating = True

End Sub

Option Explicit

Sub Calculate()

Application.ScreenUpdating = False

With ActiveSheet.Buttons(Application.Caller)

If .Caption = "Sum" Then

Cells(12, 1).Value = "=R[-2]C+R[-1]C"

Cells(12, 2).Value = "=R[-2]C+R[-1]C"

Cells(12, 3).Value = "=R[-2]C+R[-1]C"

Cells(9, 1).Value = .Caption

.Caption = "subtract"

ElseIf .Caption = "subtract" Then

Cells(12, 1).Value = "=R[-2]C-R[-1]C"

Cells(12, 2).Value = "=R[-2]C-R[-1]C"

Cells(12, 3).Value = "=R[-2]C-R[-1]C"

Cells(9, 1).Value = .Caption

.Caption = "Multiply"

ElseIf .Caption = "Multiply" Then

Cells(12, 1).Value = "=R[-2]C*R[-1]C"

Cells(12, 2).Value = "=R[-2]C*R[-1]C"

Cells(12, 3).Value = "=R[-2]C*R[-1]C"

Cells(9, 1).Value = .Caption

.Caption = "Divide"

ElseIf .Caption = "Divide" Then

Cells(12, 1).Value = "=R[-2]C/R[-1]C"

Cells(12, 2).Value = "=R[-2]C/R[-1]C"

Cells(12, 3).Value = "=R[-2]C/R[-1]C"

Cells(9, 1).Value = .Caption

.Caption = "Sum"

End If

End With

Application.ScreenUpdating = True

End Sub

Enjoy! 🙂