Make a toggle button in Excel – How to

Make a toggle button in Excel – How to

Make a toggle button in Excel - How to

Make a toggle button in Excel

This quick video tip shows you how to make a toggle button in Excel, with a VBA macro.

The video

The code

Below is the code for the toggle button.

Public Sub NightDay()
    shpText = Shapes("shpNightDay").TextFrame2.TextRange.Characters.Text
    
    If (shpText = "Night") Then
        Shapes("shpNightDay").TextFrame2.TextRange.Characters.Text = "Day"
    Else
        Shapes("shpNightDay").TextFrame2.TextRange.Characters.Text = "Night"
    End If
End Sub

As you see, we use an IF…Then…Else statement to make the button work. Also notice, only one equal sign is neccessary when testing for equality in VBA. Other programming languages use two or three equal signs.

Here is what we did in the code:

  1. Firstly, we assign the text of the shape button to a variable.
  2. Then, we check the text to see if it is Night.
  3. If the text of the button is Night, then we change it to Day.

One more thing… Remember, if you do not know the commands you need, then run Record Macro. That will help you get started.

That is all to it. Of course, if you plan to change something when the user clicks, then you may need additional code.


Related

 

Affiliate stuff – Move up and excel with Excel