Toggle a macro on/off using a button
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?
- Copy VBA code above.
- Press short cut keys Alt+F11 to open the VB Editor.
- Press with left mouse button on "Insert" on the top menu.
- Press with left mouse button on "Module".
- Paste code to module.
- Return to Excel.
How to assign a macro to a specific button?
- Press with right mouse button on on the button and a context menu appears.
- Press with mouse on "Assign Macro..." and the following dialog box shows up.
- Select the macro you want to assign.
- 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
Check boxes category
Table of Contents Add Checkboxes to a Worksheet Copy selected rows based on check boxes Worksheet data VBA code Where […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
Macro category
Table of Contents Excel monthly calendar - VBA Calendar Drop down lists Headers Calculating dates (formula) Conditional formatting Today Dates […]
Table of contents Save invoice data - VBA Invoice template with dependent drop down lists Select and view invoice - […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
Excel categories
5 Responses to “Toggle a macro on/off using a button”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
I need a formula with no Marcos – here an example of what I’m trying to do.
Column A contains:
Head-Phones-Sony
Black-Pen,Skilcraft
AAA-Batteries,24pk
Eraser,5pk
Ink-Pen,Fine-Point-Blue
Column B contains:
M412
M123
M784
M143
M572
In Cell D1 I want to ENTER *Pen* and have it list all corresponding values which is Cell A2 & Cell A5
It sounds like a “Lookup one value with multiple corresponding values” but when I use a wildcard in my search it doesn’t work... Do you have a solution for it? Thank you
Yes, you can use a wildcard in your search. You can use the INDEX and MATCH functions to look up one value with multiple corresponding values. For example, in cell D1 you can use the following formula:
=INDEX($B$2:$B$5,MATCH(“*Mouse*”,$A$2:$A$5,0))
This will return the values from column B that correspond to the cells in column A that contain the keyword “Mouse”. Make sure to use the wildcard “*” before and after the search term in order to get the results you need. Hope this helps!
John Paul,
Read this post:
Search for a text string and return multiple adjacent values
Thanks for commenting!
un codigo para invertir las casilla sseleccionas con las no seleccionadas se podra?
gracias
godofredo
a code to reverse the select box with unselected could?
Thank you (Google Translate)
The following code toggles checkboxes on a worksheet:
Sub ToggleCheckboxes() Dim chk As CheckBox For Each chk In ActiveSheet.CheckBoxes If chk.Value = 1 Then chk.Value = -4146 Else chk.Value = 1 End If Next chk End Sub