Author: Oscar Cronquist Article last updated on May 15, 2019

This article demonstrates how the user can run a macro by clicking on a button, the text on the button changes based on what the macro has performed. The macro itself reads the button and executes 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 execute.
  • 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 click "Insert" button. Click on the "button" button and then click 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, click 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 click 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 execute.

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.

    '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. Click "Insert" on the top menu.
  4. Click "Module".
  5. Paste code to module.
  6. Return to Excel.
Note, make sure you save the workbook with the file extensions *.xlsm (macro-enabled) in order to keep the code.

How to assign a macro to a specific button?

  1. Right click on the button and a context menu appears.
  2. Click on "Assign Macro..." and the following dialog box shows up.
  3. Select the macro you want to assign.
  4. Click "OK" button.

The selected macro is executed every time the user clicks 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 execute 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_Click()

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

End Sub

Download Excel file