This article demonstrates how the user can run a macro by press with left mouse button oning on a button, the text on the button changes based on what the macro has performed. The macro itself reads the button and runs code based on that text.

The animated image above shows a macro that hides and shows checkboxes using only a button. The text on the button shows if they are visible or invisible.

You will in this article learn how to:

• Insert a button (Form Control) on the worksheet
• Change the size of the button.
• Identify button name
• Create a macro
• Manipulate the text on a button programmatically.
• Read button text to determine what VBA lines to run.
• How to start another macro within a macro.
• Assign a macro to a specific button.
• Save the workbook as a macro-enabled workbook.

How to insert a button?

To create a button go to tab "Developer" and press with left mouse button on "Insert" button. Press with mouse on the "button" button and then press and hold with left mouse button on the worksheet.

Now drag with the mouse to create the button, lastly release the left mouse button and the button is now visible on your worksheet.

This allows you to control the size of the button. Don't worry, you can adjust the size later if you didn't get it right.

How to determine the name of a button?

In order to manipulate the button text, you need to know the name of the button. Simply select the button you just created and read the name box.

The dots around the button demonstrated in the picture above tells you that the button is selected, press and hold on one of the dots to change the size of the button.

The name box is located to the left of the formula bar, it contains text "Button 1" without the double quotes in the example image above.

How to toggle button text when you press with left mouse button on the button?

The macro below changes the text shown on "Button 1", this lets you create the toggle effect. It also guides the macro so it knows what lines of code to run.

Make sure you assign this macro to the button you just created, however, first you need to copy the code shown below to a regular code module.

VBA Macro

'Name of macro
Sub ToggleMacro()

'Simplify code by refering to object once
With ActiveSheet.Shapes("Button 1").TextFrame.Characters

'Check if button text is equal to a specific string.
If .Text = "Checkboxes: On" Then

    'Change button text.
    .Text = "Checkboxes: Off"

'This happens if button text is not equal to the specific string.
Else

    'Change button text.
    .Text = "Checkboxes: On"
End If

End With

End Sub

Where to put the VBA code?

1. Copy VBA code above.
2. Press short cut keys Alt+F11 to open the VB Editor.
3. Press with left mouse button on "Insert" on the top menu.
4. Press with left mouse button on "Module".
5. Paste code to module.
6. Return to Excel.

How to assign a macro to a specific button?

1. Press with right mouse button on on the button and a context menu appears.
2. Press with mouse on "Assign Macro..." and the following dialog box shows up.
   
3. Select the macro you want to assign.
4. Press with left mouse button on "OK" button.

The selected macro is rund every time the user press with left mouse button ons on the button.

How to toggle checkboxes?

There are two different macros being used that add and delete checkboxes demonstrated here: Add checkboxes to a sheet (1/2)(VBA). Those two macros have to be added to a code module in your workbook in order to make this work.

The macro code below shows how to determine which macro to run by reading the button text displayed on "Button 1". It also shows that the macro is unnecessary long because I am not using the With - With End statement.

VBA Macro

Sub Button1_Press with left mouse button on()

If ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: On" Then
    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: Off"
    Call RemoveCheckboxes
Else
    ActiveSheet.Shapes("Button 1").TextFrame.Characters.Text = "Checkboxes: On"
    Call Addcheckboxes
    ActiveSheet.Range("A1").Select
End If

End Sub

Get the Excel file

Toggle-macro-on-off.xlsm