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
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]
In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image […]
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]
I will now demonstrate with the following table how to add check-boxes and sum enabled check-boxes using a formula. Add […]
Macro category
This article demonstrates a macro that copies values between sheets. I am using the invoice template workbook. This macro copies […]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]
In this post I am going to show how to create a new sheet for each airplane using vba. The […]
This blog post demonstrates how to create, populate and change comboboxes (form control) programmatically. Form controls are not as flexible […]
The image above shows an array formula in cell D6 that extracts missing numbers i cell range B3:B7, the lower […]
This article demonstrates how to run a VBA macro using a Drop Down list. The Drop Down list contains two […]
This workbook contains two worksheets, one worksheet shows a calendar and the other worksheet is used to store events. The […]
In this article I will demonstrate how to quickly change chart data range utilizing a combobox (drop-down list). The above […]
In this blog article, I will demonstrate basic file copying techniques using VBA (Visual Basic for Applications). I will also […]
Excel does not resize columns as you type by default as the image above demonstrates. You can easily resize all […]
This article describes how to create an interactive chart, the user may press with left mouse button on a button […]
Rahul asks: I want to know how to create a vlookup sheet, and when we enter a name in a […]
This article describes how to create a button and place it on an Excel worksheet then assign a macro to […]
Question: hi all, thanks for the great formula/array formula. it works great. lately, i noticed that the array formula will […]
This article demonstrates a macro that inserts new worksheets based on names in a cell range. The cell range may […]
In this article, I am going to demonstrate a simple workbook where you can create or delete projects and add […]
Today I would like to share with you these small event handler procedures that make it easier for you to […]
This article explains how to hide a specific image in Excel using a shape as a button. If the user […]
This article describes different ways to locate literal or hardcoded values in formulas. The image above shows the result from […]
This article demonstrates macros that save worksheets to a single pdf file. What's on this webpage Export all worksheets in […]
Excel categories
4 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
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: