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:
- Firstly, we assign the text of the shape button to a variable.
- Then, we check the text to see if it is Night.
- 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.