How to Assign a Macro to a Button in Excel (Easy Guide)

While there are many different ways to run a macro in Excel, none of those methods can be as easy and user-friendly as clicking on a button.

And for that to work, you need to assign a macro to a button first.

In this tutorial, I will show you a couple of ways to insert a button in  Excel and then assign a macro to that button (or shape). Once done, as soon as a user clicks on the button, the macro VBA code would be executed.

For the purpose of this tutorial, I will be using the below VBA macro code (which simply selects cell A1 in the active sheet and enters the text “Good Morning” in it and colors it red).

The above VBA code is placed in a regular module in the VB Editor

Now let’s dive right in and see how you can assign this macro to a button or shape in Excel!

This Tutorial Covers:

Insert a Shape and Assign Macro to that Shape

While there are dedicated buttons that you can insert in the worksheet and then assign the macro to it, I will first cover how to assign a macro to a shape .

I personally love this method and prefer it over the rest two methods covered later. You can easily insert a shape (square or rectangle) and can make it look like a button.

And since it’s a shape, you can easily format it to look perfect with your existing formatting or brand colors.

Below are the steps to insert a shape in Excel:

  • Resize the rectangle and format it (give it a border, color, shade if you want).

After you have done the above steps, you will have a rectangle shape in the worksheet, and now we will assign a macro to this shape.

Now let’s see how to assign a macro to this shape.

  • Right-click on the shape on which you want to assign the macro
  • In the Assign Macro dialog box, you will see a list of all the macros that you have in the workbook
  • Click on OK

That’s it!

The selected macro has now been assigned to the shape.

Now when you hover the cursor over the shape, it will show the hand icon. which indicates that now this shape has become clickable.

And now if you click on the shape, it will run the assigned macro .

You can type any text within the shape to make it more intuitive (such as ‘Click here to run the macro’). To do this. right-click on the shape and then click on Edit Text. Now you can type within the text box shape.

Note that you won’t be able to click and run the macro when the shape has been selected (i.e., you see a border around the shape that appears when you select it), To make it clickable, hit the Escape key or click anywhere in the worksheet.

Also, when you have assigned the macro to the shape already, you will not be able to select it by using the left mouse key (as it has become clickable and left-click would now execute the macro). In that case, select the shape, hold the control key and then press the left key.

Keeping Shape Visible When you Hide/Resize Rows/Columns

In Excel. when you insert a shape, it sits over the cells – like a chart/object.

This also has a drawback that when you resize or hide rows/columns that have the shape over it, the shape also follows suit.

In the below example, the shape gets hidden when I hide the column on which it’s placed.

If you don’t want this to happen, follow the below steps:

  • Right-click on the shape
  • In the Format Shape pane (or dialog box in case you’re using Excel 2010 or prior versions), select Size and Properties
  • Close the pane (or dialog box)

Now, when you resize rows/columns or hide these, the shape would stay in its place.

Assign a Macro to Form Control Button

If you’re not too concerned with the formatting of the button and are ok with regular gray buttons, you can quickly insert it from form control (or ActiveX control as shown next) and then assign a macro to it.

For this to work, you will need to have the Developer tab in your ribbon. If you don’t have it, here is a detailed step-by-step tutorial on getting the developer tab in the Excel ribbon .

Once you have the developer tab visible, you can use the below steps to quickly insert a button and assign a macro to it:

  • Click anywhere on the worksheet. This will insert the button wherever you click and automatically open the ‘Assign Macro’ dialog box.

The above steps would insert a button that has the specified macro assigned to it.

By default, it would be a small button with text such as ‘Button’ written on it. You can change the text to whatever you want and can also change the shape of the button (by dragging the edges).

Since this is an object that is placed over the worksheet (just like shapes/charts), you can drag and place it anywhere in the worksheet.

One drawback of using the Form Control button is that you don’t have much control over the formatting. For example, you can not change the color from gray to something else.

Although there is a little bit of formatting that you can do with a Form control button, it’s nowhere close to what you can do with shapes.

You get these button formatting options when you right-click on the button and then click on Format Control.

This will open the Format Control dialog box where you can change the font type/color, size, alignment, etc.

One good thing about this button is that it doesn’t hide or resize when you hide the rows/columns or resize them. It would, however, move in case you change the height or width or the row/column over which the button is placed.

In case you don’t want the button to stay in its place, you can change the setting by following the below steps:

  • Right-click on the button
  • Click on Format Control
  • Click on the Properties tab

Assign a Macro to an ActiveX Control Button

Apart from the Form Control button, there is also an ActiveX control button to which you can assign a macro.

In most cases, you won’t need to use the ActiveX control button, and I recommend you use it only when you completely understand what it is and you know what you’re doing.

This also, sometimes, make ActiveX a bit glitchy and unpredictable. So, while I cover it in this tutorial, I don’t recommend using ActiveX button and assign a macro to it.

To insert an ActiveX button and then assign a macro to it, follow the below steps:

  • Click on the Developer tab
  • In the Control group, click on Insert.
  • Click anywhere on the worksheet. This will insert the button wherever you click.
  • Double-click on the button and it will open the VB Editor backend where you can place the code for the ActiveX button

With ActiveX control, you get a lot more flexibility with a single button. For example, you can specify one macro to be run when you simply click on the button once and another macro when you double-click or even another one when you use the up/down arrow key.

Again, not something you need to be using in your regular work.

Hope you found this tutorial useful. If you’re interested in learning VBA, you can check out more in-depth Excel VBA tutorials here .

You may also like the following Excel tutorials:

  • How to Record a Macro in Excel
  • Creating a User Defined Function (UDF) in Excel VBA
  • Excel VBA MsgBox [Message Box]
  • Useful Excel Macro Examples for VBA Beginners
  • How to Remove Macros From an Excel Workbook
  • How to Enable Macros in Excel?

FREE EXCEL BOOK

Get 51 Excel Tips Ebook to skyrocket your productivity and get work done faster

1 thought on “How to Assign a Macro to a Button in Excel (Easy Guide)”

Following the procedure here, I cannot assign a Macros from an *.xlam workbook (addin). Is there a way to do this? thx

Leave a Comment Cancel reply

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Excel Skills

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

© TrumpExcel.com – Free Online Excel Training

Privacy Policy  | Sitemap

Twitter | Facebook | YouTube | Pinterest | Linkedin

FREE EXCEL E-BOOK

  • Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

Peltier Tech

Peltier Technical Services - Excel Charts and Programming

How To: Assign a Macro to an ActiveX Control

Thursday, March 13, 2008 by Jon Peltier 31 Comments

In the latest post , I showed how to assign a macro to a Forms menu control or other shape on a sheet. Macros can also be run from the ActiveX controls on the Control Toolbox.

We’ll use this simple macro for this example.

When it is run, we see a simple message.

Hello World

Control Toolbox ActiveX Controls

Forms toolbar controls have been part of Excel since (I think) Excel 4. They are stable and easy to use. And they are old-fashioned. The Control Toolbox has newer ActiveX controls, introduced with Excel 97. These are fancier controls, with more formatting options, and somewhat more dynamic behavior. They often are blamed for erratic behavior, and only work in Windows versions of Excel. Seasoned Excel developers generally prefer the Forms controls and avoid the ActiveX controls . However, these controls can provide some slick effects.

To use Control Toolbox controls, you need to make the Control Toolbox visible (actually it’s a toolbar). Use the same techniques as shown to make the Forms toolbar visible, or click the Control Toolbox button on the Visual Basic toolbar.

Controll Toolbox

Click the Command Button button, and drag a rectangle in the worksheet where you want the button. No Assign Macro dialog pops up, however, and when you right click on the button, the context menu has no Assign Macro element either.

Command Button

ActiveX controls work a bit differently. When they are clicked, or interacted with in other ways (e.g., when scrollbars are scrolled, when items in a combobox are selected, etc.), they raise events, which VBA can respond to. Instead of the missing Assign Macro item, click on View Code in the context menu. This inserts an event procedure in the code module behind the worksheet containing the button.

excel assign macro to activex button

You can write code within this event procedure that runs whenever CommandButton1 is clicked. Note the two dropdowns at the top of the code module. The left dropdown shows CommandButton1, and if there were other ActiveX controls on the worksheet, they would also be listed when this dropdown is pulled down. The right dropdown lists the events which the selected control in the left dropdown responds to.

excel assign macro to activex button

It is the wealth of these events which make ActiveX controls more powerful, and also perhaps a little flaky. But no rants now, this is a family blog.

To run the HelloWorld macro when the command button is clicked, call it from within the CommandButton1_Click event procedure. To do that, simply type the name of the macro, HelloWorld, within the event procedure.

excel assign macro to activex button

Share this:

  • Click to share on Twitter (Opens in new window)
  • Click to share on Facebook (Opens in new window)
  • Click to share on LinkedIn (Opens in new window)
  • Click to share on Tumblr (Opens in new window)
  • Click to share on Pinterest (Opens in new window)
  • Click to share on Reddit (Opens in new window)
  • Click to print (Opens in new window)
  • Click to email a link to a friend (Opens in new window)

Posted: Thursday, March 13th, 2008 under VBA . Tags: . Comments: 31

Peltier Tech Charts for Excel

Reader Interactions

Jeff Weir says

Wednesday, March 23, 2011 at 7:58 pm

Hi Jon. I’ve posted the following question over at http://www.excelguru.ca/forums/showthread.php?42-How-do-you-reference-the-name-of-an-activex-control-within-the-control-s-change-event&p=122#post122 but I thought I’d post it here too. Apologies for the cross post.

Do you know whether you can return the name of an activex control within that activex control’s change event?

I’ve got some activex textboxes in a sheet with some default text in them. Once the user clicks on a particular textbox, I want to clear the default text in that textbox so that the user can add their own text without having to first select the existing text and then delete it.

So I’m using this: Code:

Private Sub TextBox1_GotFocus() If Sheet1.TextBox1.Text = Range(“TextBox1_default”).Value Then Sheet1.TextBox1.Text = “” End Sub

But because I’m doing this with a lot of text boxes, I want to have a function that does this, that automatically gets passed the name of the calling activex object.

But I can’t work out how to pass the activex name/details to the function, and how I would dim the activex name/details within that function?

While something like application.caller works fine for a forms control, It doesn’t seem to work for an activex control: I’m using _GotFocus and _Change events to run the code. But for some reason, Application.Caller or ME does not seem to return details of the activex control that raised the event. I’ve tried these variations

* If I try application.caller in the _GotFocus event sub, I get “Type mismatch” error * If I try application.caller.name in the _GotFocus sub, I get a “Object Required” error * If I try With Me / MsgBox .Name / End With (with “/” denoting new lines) I get a very unhelpful “Sheet1” returned * Same goes for With Application.Caller / MsgBox Name / End With

I could replace all my activex controls with form controls, or I could forget about trying to ‘functionize’ my code, and just write seperate routines for each activex contlol. Or perhaps there’s another solution.

Regards Jeff

Jon Peltier says

Wednesday, March 23, 2011 at 8:52 pm

Jeff –

Here’s how to do that. You need to set up a class for the textboxes. You don’t get as many events in the class as in the worksheet class, but you get enough.

Insert a new class module, call it CText. After “Option Explicit” at the top, add this:

Choose MyText from the left hand dropdown atop the module, then click on Enter in the right hand dropdown. You get an Event procedure like the following. I’ve inserted the MsgBox just to show that it worked and could identify which textbox was clicked on.

Now you need to activate the textboxes. Put this into the declarations section of a regular module:

and insert these procedures:

Run the activation procedure, then click in a textbox. The message box will tell you which one it is, and you can follow up with whatever you need to do.

Wednesday, March 23, 2011 at 9:27 pm

THanks Jon. For all my questions you’ve answered over the last 2 years I owe you a night on the town in Wellington NZ, at the very least! Redeem at your leisure. (Truth be known, I probably owe you the airfare here, too).

Thursday, March 24, 2011 at 4:23 am

Note that I was getting “Object doesn’t support this property or method” on the line If sh.OLEFormat.Object.OLEType = xlOLEControl Then in the ActivateActiveXTextBoxes sub.

This had me scratching my head for a while. Finally tracked it down to having some shapes in the sheet that were not ole objects. So I added an IF wrapper If sh.Type = msoOLEControlObject Then

One thing I don’t follow is what is going on in the right hand side of the equality in this line: Set TheTextBoxes(iTextBoxCount).MyText = sh.OLEFormat.Object.Object . Object.object?

Finally, is there any improvement you’d suggest for the ‘business end’ procedure (i.e. ‘point’) of all this: Private Sub MyText_MouseDown(ByVal Button As Integer, ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single) On Error Resume Next If MyText.Text = Range(MyText.Name & "_Default") Then MyText.Text = "" On Error GoTo 0 End Sub

Thanks again, Jon

Thursday, March 24, 2011 at 10:26 am

“Object.Object” – well, it’s what works. If you look in the Object Browser, the OLEFormat has an Object member, which is the OLEObject. OLEObject in turn also has an Object member, which in this case is the textbox.

What I would do is select the entire contents of the textbox when it gains focus, so the user can change his mind and not delete it, or change one character in it, or whatever. With it all selected, typing replaces the text, as if it were deleted.

What I would really do is try to make the interface work using cells, not controls.

Thursday, March 24, 2011 at 2:10 pm

Very good idea re selecting the entire textbox.

I’m using textboxes because I wanted to escape the ‘grid’ for a form I’m putting together. http://screencast.com/t/5mYAEhDgAZX

I thought I’d have more chance of getting people to fill in a form properly if it looks just like a ‘bought one’.

So far my favorite part of the form is the ‘007’ reference at the top left.

Pieter says

Tuesday, April 12, 2011 at 1:51 am

I need to create a “configurator” where I have 5 ActiveX Control Pictures. If you click on the first picture, it must give me 4 boxes. 1st box – must be a box that have general info in it about the item in the picture. 2nd, 3rd & 4th box- must bring up a box that several items can be selected and later carried over to another sheet that can be printed out.

Then there must be a clear button to clear all 4 box and only leave the 5 pictures there. then when you click on another picture the 4 boxes must appear with info about that specific item in the picture.

THen after everything has been selected there must be a Quote button that take all the selected data to another sheet to be compiled into a Quote sheet.

Can all this be done??

Great site!

Thursday, April 14, 2011 at 10:16 am

Pieter –

Could you use regular inserted pictures? You can assign each to a macro that shows the relevant text boxes. Clear and Compile buttons can be done in the same way with Forms menu buttons, and macros assigned to them.

Thursday, April 14, 2011 at 4:53 pm

Jon: Here’s a very sneaky way to do this from Sam (one of my fellow participants on the Excel Hero course, and a regular on some of the boards). He puts a tranparent textbox over the top of the activex controls, so that someone trying to select the activex control inadvertantly selects the textbox, which then uses application.caller to pass the name of the calling textbox to the activex control with the same name. Ingenious! Sneaky!

Workbook at https://docs.google.com/leaf?id=0B1hgC5lSuLjVZGEwZmRhNGYtNDA2NS00MWU4LThjYjQtMmI5MGFjMTBhYjYy&hl=en

Friday, April 15, 2011 at 1:09 am

Ive tried it with inserting a regular picture. What must I put in the macro to attach it to the regular picture? I’ve tried to record a macro, copy a text box and then run it, but then it just flickers and i dont see anything. Ive tried to record and then insert a tect box, copy text into it and then run it, but then it flickers and gives me the last text I typed? I am not that clued up with macros, but my boss requested this, and I do not know how to create something that works and look professional

Hope someone can help me with this.

Saturday, April 16, 2011 at 10:15 am

As described in How To Assign a Macro to a Button or Shape :

Right click on the inserted picture, choose Assign Macro from the popup toolbar, and select the macro from the Assign Macro dialog.

Amr El Khodary says

Wednesday, March 13, 2013 at 5:46 am

HI, I am doing a questionare that requires a user to select through checking an ActiveX Button. I want him when he checks a button, other buttons, if checked by mistake, to be unchecked..can you help advising how can I do that?

Many thanks

Appreciate your help

Wednesday, March 13, 2013 at 9:33 pm

Use a regular button, not a checkbox. When the user clicks on it, change all the true linked cells to false.

James Haughton says

Wednesday, March 27, 2013 at 2:05 am

I am trying to get a macro to work with a drop down form control so that when a selection is made it runs a macro to refresh all. All that I have tried so far never seems to work.

Wednesday, March 27, 2013 at 8:41 am

James – Right click on a drop down control from the forms menu, and you will see Assign Macro in the context menu. Select a macro that is written in a regular code module.

Monday, February 17, 2014 at 9:59 am

I just want to know if how can i make an interactive questionnaire in powerpoint which it has multiple of correct answers, using option button. for example :

which of the following are green objects? Select 3 answers: a. leaf b. caterpillar c. apple d. mango e. grass

please reply very soon. I really need it, thank you in advance! God bless! :)

Monday, February 17, 2014 at 10:17 am

Gail – You should find a PowerPoint site to answer this. Try searching for PowerPoint questionnaire.

Monday, February 9, 2015 at 11:52 am

How can I easily prevent an Active X checkbox from running code in the _click() sub when a user hasn’t actually clicked it? Application.EnableEvents = FALSE doesn’t seem to work. And TypeName(Application.Caller) appears to return “Error” (meaning called by VBA) even when I click the box. I understand it should return “String” if called by a control. But maybe doesn’t apply to Active X controls?

Monday, February 9, 2015 at 12:36 pm

How would the code run if the user hasn’t clicked the checkbox? You mean if the linked cell was changed directly?

You could use a Form Control checkbox, and assign a macro to run when it is clicked on.

Monday, February 9, 2015 at 12:54 pm

For some reason, my _click() code is executed when I set the .value for the OLEObject in a separate sub…

Example: Private Sub myCheckBox_Click() MsgBox “Checkbox code is running.” end Sub

Sub ViewCheckbox() Sheets(“Sheet1”).OLEObjects(“myCheckBox”).Object.Value = 1 End Sub

Execute ViewCheckbox() and you get the prompt, “Checkbox code is running.”

Monday, February 9, 2015 at 4:22 pm

The Checkbox_Click event runs whenever the checkbox value is changed.

Monday, February 9, 2015 at 4:43 pm

Jon, I understand this to be the case. That’s the basis of my question. “How can I easily prevent an Active X checkbox from running code in the _click() sub when a user hasn’t actually clicked it?”

Monday, February 9, 2015 at 5:15 pm

You could declare a public variable in the Declarations section of a regular code module:

Then in your calling code:

And finally, in the control event code:

Monday, February 9, 2015 at 5:44 pm

Excellent, I’ll give that a shot. Thanks

ROBERT C says

Friday, August 21, 2015 at 11:21 pm

I AM CURRENTLY TRYING TO CREATE A BUTTON THAT PLACES THE CURRENT TIME THE BUTTON WAS CLICKED. HOWEVER I AM HAVING MAJOR ISSUES. I WANT TO USE ONE BUTTON THAT WILL FILL A ROW WITH DIFFERENT TIMES SO WHEN IT IS CLICK IT WILL FILL COLUMN 1 ON THE TABLE WITH THE CURRENT TIME THAN LATER IF CLICKED IT WILL FILL COLUMN 2 ON THE TABLE AND SO ON. I WANT TO USE A COMMAND BUTTON BUT IF I HAVE TO USE REGULAR TEXT I WILL BUT IM SO STUCK. ANY HELP WILL BE GREAT.

Saturday, August 22, 2015 at 10:48 am

Your caps lock key is broken.

Does the button do anything else besides enter the time? If not, Ctrl+Shift+: enters the current time in the active cell.

Saturday, August 22, 2015 at 11:04 am

Sorry about that im used to using it in the field i work in. always forget to turn it off. it only puts in the current time however i am on word so it may be different than excel an i need it to be inserted into a table i have put in place. I want to be able to have my guys click the button and the time will show up in the next cell in the table is this at all possible. My guys arent taking the time for the other chortcuts

Saturday, August 22, 2015 at 12:22 pm

It would probably take me at least six hours to figure out the answer in Word.

Wednesday, May 11, 2016 at 5:14 am

bit of a work around for a sheet with mixed form and avctiveX controls (Jeff Weir) For Each sh In ActiveSheet.Shapes If TypeName(sh.OLEFormat.Object) Like “OLEObject” Then If TypeName(sh.OLEFormat.Object.Object) Like “TextBox” Then iTextBoxCount = iTextBoxCount + 1 ReDim Preserve TheTextBoxes(1 To iTextBoxCount) Set TheTextBoxes(iTextBoxCount).myText = sh.OLEFormat.Object.Object End If End If Next

Tuesday, June 28, 2016 at 9:16 pm

I’m having problems with a ListBox ActiveX I placed on my 1st worksheet (called “Dashboard”). I have a 2nd page (called “FieldData”). I think I’ve populated it correctly, but I cannot get a single click on any ListBox item to run a Sub (macro) named after the ListBox item.

I’m past my deadline for submitting it to my boss and I need help!

[CODE] Option Explicit Private Sub ListBox1_Click()

Sheets(“Dashboard”).Select With Dashboard.ListBox1 .AddItem “Field_1” .AddItem “Field_2” .AddItem “Field_3” .AddItem “Field_4” End With End Sub

Sub ListBox1_new()

Call ListBox1_Click Dim ListBox1 As Object If ListBox1.Text = “Field_1” Then Call Field_1 ElseIf ListBox1.Text = “Field_2” Then Call Field_2 ElseIf ListBox1.Text = “Field_3” Then Call Field_3 ElseIf ListBox1.Text = “Field_4” Then Call Field_4 Else End If

End Sub Sub Field_1() Call Clear ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”1” Call Copy Call Paste End Sub Sub Field_2() Call Clear ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”2” Call Copy Call Paste End Sub Sub Field_3() Call Clear ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”3” Call Copy Call Paste End Sub Sub Field_4() Call Clear ActiveSheet.Range(“$A$1:$J$137″).AutoFilter Field:=2, Criteria1:=”4” Call Copy Call Paste End Sub Sub Clear() ‘ Clear Report Area Sheets(“Dashboard”).Select Range(“F11”).Select Range(“F11:O22”).Select ‘ Range(Selection, Selection.End(xlToRight)).Select ‘ Range(Selection, Selection.End(xlDown)).Select Selection.ClearContents Sheets(“FieldData”).Select Selection.AutoFilter End Sub Sub Copy() ‘ Copy the data Range(“A2”).Select Range(Selection, Selection.End(xlToRight)).Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy End Sub Sub Paste() ‘ Paste the data Sheets(“Dashboard”).Select Range(“F11”).Select ActiveSheet.Paste Application.CutCopyMode = False End Sub

It’s supposed to be pretty simple, click on the Field# in the ListBox. That Sub will filter the FieldData to that criteria, copy the data, and paste it in a specified location on the “Dashboard” page. I want the user to be able to repeat for any Field# one right after the other. I have built-in clearing functions (found above) to clear the specified location.

Can you help? JDS

Thursday, June 30, 2016 at 3:15 pm

1. This code goes into the code module behind the “Dashboard” worksheet.

2. You don’t want to populate the listbox within its own click event. How about in the Worksheet_Activate event? Also, clear it first, so you don’t get multiple blocks of the items you want.

3. Dashboard.ListBox1 does not reference anything valid, unless you’ve given your sheets code names. You could use Worksheets(“Dashboard”).Listbox1, but since the code resides behind the sheet, you can get by with simply me.Listbox1.

4. Don’t use keywords (Copy, Paste, Clear) for function or variable names. You’ll get confused and Excel might get confused.

5. You don’t want to be declaring a variable named Listbox1, because you already have an object with the name.

6. I think you want to filter what’s on “FieldData”, but probably “Dashboard” is the activesheet. Anyway, you don’t need to keep switching sheets.

I don’t have an autofilter on my “FieldData” worksheet, so I can’t really test this, but here’s some code that’s a lot closer to what you need. This code could also be streamlined further a great deal.

Leave a Reply

Your email address will not be published. Required fields are marked *

Notify me of follow-up comments by email.

Notify me of new posts by email.

This site uses Akismet to reduce spam. Learn how your comment data is processed .

Peltier Tech Charts for Excel

Exceldome

Assign a macro to a button

This tutorial shows how to assign a macro to a form control and activex control button using excel or vba.

EXCEL VBA EXPLANATION

METHOD 1. Assign a macro to a Form Control button

Method 1. assign a macro to an activex control button, explanation about how to assign a macro to a button.

Excel Off The Grid

Assign macro with arguments to a Form Control button

One of the most popular methods of running a macro is having a button on the face of the worksheet.  These are simple enough to create with the basic Form Controls found on the Developer Ribbon, which is what makes them a popular option.

But what if you had lots of buttons, all of which did a similar thing apart from a few different variables, arguments or parameters?  One option is writing separate code for each button, or a second option maybe a big If statement to handle the logic for all the buttons.  But please don’t do either of those, that would be crazy.

To pass an argument to a macro just requires the right syntax when assigning the macro to the button.

The Example

For the purposes of proving how this works, here is our example scenario.  There are two Listboxes, each containing a list, with a button below each.   Whenever the button is clicked, the count of items in the Listbox above it is displayed in a message box.

Example scenario

Lets consider how we can achieve this in the most efficient way possible.

Setting up the VBA code

I will assume you already know how to create a button and assign a macro to it.

If we had created separate code for each button, then the VBA code for clicking the button below the lstBox1 would be as follows:

If you notice, the sheet name and Listbox name are hardcoded into the macro, therefore we would need one macro for each button.  Now imagine we had 30 Listboxes and 30 buttons… that would require 30 macros!  Bad idea.

This is where arguments are useful.  We can pass the worksheet name and Listbox name into the macro as arguments, by doing this, we can use a single piece of VBA code.

The code above can be used with any ListBox.  There are no hardcoded variables within the code; they are passed to the code when it is called.

Running a macro with arguments

Having created a macro with arguments in the previous section, it raises a few new issues when assigning it to a button.

  • The macro does not appear in the list of available macros.  We can still use the macro, but we have to know it’s name.
  • We need to know the right syntax to pass the arguments to the macro

We can handle both of these issue; no big deal.

The syntax required to a call a macro from the same workbook is:

Take careful note of where the single quotes, double quotes, commas and spaces are.  There is nothing to help us complete this, apart from an error message to taunt us when we’ve got it wrong.

Assign Macro with aruments error message

To call our lstBoxCount macro from above, the text in the Assign Macro window would be:

Assign Macro with arguments - entry

Where  Sheet1  is the name of the worksheet and  ListBox1 is the name of the first ListBox.

The same macro could be called from the second button, but the arguments would be different. Notice below the Listbox name has changed.

We can now use the same VBA code no matter how many Listboxes there are, or which worksheets they are on.  We just change the values in the arguments.

Passing Numbers as arguments

If passing a number as an argument do not surround it in double quotes.

Assigning a macro from another workbook

When returning to the Assign Macro window, you will notice Excel has added the name of the workbook into the Macro name box.

Assign Macro with arguments - entry different workbook

At first, this may seem annoying.  But Excel is helping us here, as this is now displaying the syntax required to call a macro from another workbook.

Running a macro with arguments based on a cell value

So far we have assumed we know the arguments when creating the buttons.  That might not be the case; maybe the argument is based on a cell value.  But that is OK too; we can dynamically pass a cell value into the macro at the point the button is clicked by assigning a macro using the following syntax.

The example above assume the argument is contained in Cell A1.  Once again, take careful note of where the single and double quotes are.

Using our specific example.  Let’s assume the name of the Listbox is contained in Cell B2:

Assign Macro with arguments - from spreadsheet

This will work too!  Cool, eh?

Wrapping it all up

Hopefully, you see this is very powerful and being able to set arguments based on a cell value is simply amazing.  You no longer need lots of buttons.  Instead, a drop-down box and one button might be sufficient.

Discover how you can automate your work with our Excel courses and tools.

Excel Academy

Excel Academy The complete program for saving time by automating Excel.

Excel Automation Secrets

Excel Automation Secrets Discover the 7-step framework for automating Excel.

Office Scripts Course

Office Scripts: Automate Excel Everywhere Start using Office Scripts and Power Automate to automate Excel in new ways.

5 thoughts on “Assign macro with arguments to a Form Control button”

Go(o)d trick! Trick is compatible: *.xlsm / *.xls (until 2003) – no compatible: *.xlsb (variors error) – if convert: *.xls > *.xlsm … then reassign all “macro” parameters, because rename at convert action.

Thanks, that is useful to know.

This was extremely helpful to solve a problem, thank you. FWIW, Boolean arguments are like numbers and should also not be wrapped in quotes.

Generally: ‘MacroName Boolean’

My specific example: ‘ClearData True’

Then upon saving, Excel 365 added the workbook name: ‘yourworkbookname.xlsm’!’ClearData True’

What I don’t understand is why it is adding the workbook name, because it IS in the same workbook. Either way, it works, so thanks!

To automatic Excel function: full path, see above text: “Assign macro with arguments to a Form Control button”.

Curious if anyone else is seeing this. Used the above to pass a text string to a procedure via a form control button and it works fine. However after I save, close, reopen the area where I assigned the call with the argument has been prepended with the workbook name and clicking the button now fails. Behavior seems the same regardless of what the “Macros In” drop down is set to.

Leave a Comment Cancel reply

#1 Excel tutorial on the net

  • ActiveX Controls

Learn how to create ActiveX controls , such as command buttons, text boxes, list boxes etc. To create an ActiveX control in Excel VBA , execute the following steps.

1. On the Developer tab , click Insert.

2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.

Create an ActiveX control in Excel VBA

3. Drag a command button on your worksheet.

4. Right click the command button (make sure Design Mode is selected).

5. Click View Code.

View Code

Note: you can change the caption and name of a control by right clicking on the control (make sure Design Mode is selected) and then clicking on Properties. Change the caption of the command button to 'Apply Blue Text Color'. For now, we will leave CommandButton1 as the name of the command button.

The Visual Basic Editor appears.

6. Add the code line shown below between Private Sub CommandButton1_Click() and End Sub.

Add Code Lines

7. Select the range B2:B4 and click the command button (make sure Design Mode is deselected).

Run Code

Learn more, it's easy

  • Option Buttons
  • Spin Button
  • Loan Calculator

Download Excel File

  • activex-controls.xlsm

Next Chapter

Follow excel easy.

Excel Easy on Facebook

Become an Excel Pro

  • 300 Examples

ActiveX Controls • © 2010-2024 Popular Excel Topics: Pivot Tables • Vlookup • Formulas • Charts • Conditional Formatting

Excel Dashboards

Excel Tutorial: How To Use Activex Controls In Excel

  • Introduction To The Npv Function In Excel
  • Understanding Cash Flow Analysis
  • Setting Up Your Data For Npv Calculation
  • Step-By-Step Guide To Using The Excel Npv Function
  • Practical Examples Of Npv In Action
  • Troubleshooting Common Npv Function Issues
  • Conclusion & Best Practices For Utilizing Excel'S Npv Function

Introduction: Understanding ActiveX Controls in Excel

ActiveX controls are a powerful tool within Excel spreadsheets that allow users to interact with and manipulate data in a more dynamic and user-friendly way. Understanding how to use ActiveX controls is essential for anyone looking to take their Excel skills to the next level and create more advanced and interactive spreadsheets.

A Definition and purpose of ActiveX controls within Excel spreadsheets

ActiveX controls are small programs or add-ins that can be used to create interactive elements within Excel spreadsheets. These controls can be used to create buttons, text boxes, combo boxes, checkboxes, and more, allowing users to input data, make selections, or trigger specific actions within the spreadsheet.

Overview of the types of ActiveX controls available

There are various types of ActiveX controls available in Excel, each serving a specific purpose. These include:

  • Buttons: Used to trigger macro scripts or perform specific actions when clicked.
  • Text boxes: Allow users to input text or data into the spreadsheet.
  • Combo boxes: Provide a dropdown list of options for users to select from.
  • Checkboxes: Allow users to make binary choices by checking or unchecking the box.
  • Radio buttons: Used to present a list of mutually exclusive options for users to choose from.

Importance of knowing how to use ActiveX controls for advanced Excel functionality

Having the knowledge and skills to use ActiveX controls in Excel is crucial for anyone looking to enhance the functionality and interactivity of their spreadsheets. Whether it's creating user-friendly forms, integrating interactive elements, or automating processes using macros, ActiveX controls play a vital role in taking Excel spreadsheets to the next level. By learning how to use ActiveX controls effectively, users can add a new dimension of usability and interactivity to their Excel workbooks, ultimately improving their efficiency and productivity.

  • Understanding ActiveX controls in Excel
  • Inserting and customizing ActiveX controls
  • Linking ActiveX controls to Excel data
  • Using ActiveX controls for interactive spreadsheets
  • Best practices for using ActiveX controls in Excel

Getting Started with ActiveX Controls

ActiveX controls are a powerful feature in Excel that allow you to add interactive elements to your spreadsheets. Whether you want to create a user form, insert a button, or add a calendar control, ActiveX controls can help you enhance the functionality of your Excel workbook. In this tutorial, we will walk you through the process of using ActiveX controls in Excel.

A. How to access the Developer tab and ActiveX controls in Excel

In order to start using ActiveX controls in Excel, you first need to access the Developer tab. The Developer tab is not visible by default, so you will need to enable it in the Excel settings. Here's how you can do it:

  • Open Excel and click on the File tab.
  • Click on Options to open the Excel Options dialog box.
  • In the Excel Options dialog box, click on Customize Ribbon in the left pane.
  • Check the box next to Developer in the right pane and click OK .

Once you have enabled the Developer tab, you can access ActiveX controls by clicking on the Developer tab in the Excel ribbon.

B. Steps to insert your first ActiveX control into an Excel spreadsheet

Now that you have access to the Developer tab and ActiveX controls, you can start inserting ActiveX controls into your Excel spreadsheet. Here's a step-by-step guide to help you insert your first ActiveX control:

  • Click on the Developer tab in the Excel ribbon.
  • Click on the Insert drop-down menu in the Controls group.
  • Select the type of ActiveX control you want to insert, such as a button, text box, or combo box.
  • Click and drag on the spreadsheet to draw the control at the desired location.

Once you have inserted the ActiveX control, you can resize and move it as needed to fit your layout.

C. Initial property adjustments and settings for the most common ActiveX controls

After inserting an ActiveX control into your Excel spreadsheet, you may need to adjust its properties and settings to customize its behavior. Here are some initial adjustments you can make for the most common ActiveX controls:

  • Button: Right-click on the button and select Properties to change the caption, font, color, and other properties.
  • Text Box: Right-click on the text box and select Properties to change the font, color, and other properties.
  • Combo Box: Right-click on the combo box and select Properties to change the list items, font, color, and other properties.

By adjusting the properties and settings of ActiveX controls, you can tailor them to meet your specific requirements and create a more interactive and user-friendly Excel spreadsheet.

Configuring ActiveX Controls Properties

When working with ActiveX controls in Excel, it's important to understand how to configure their properties to customize their appearance and behavior. The properties window for ActiveX controls allows you to make these adjustments, and it's essential to know how to use it effectively.

A Detailed explanation of the properties window for ActiveX controls

The properties window for ActiveX controls provides a comprehensive list of properties that can be adjusted to customize the control. This includes properties related to appearance, behavior, data, and more. By accessing the properties window, you can modify these settings to tailor the control to your specific needs.

Customizing ActiveX controls through property adjustments (size, color, font, etc)

One of the key benefits of using ActiveX controls in Excel is the ability to customize their appearance. Through the properties window, you can adjust properties such as size, color, font, alignment, and other visual aspects to create a control that fits seamlessly into your Excel project. This level of customization allows you to create a professional and polished look for your user interface.

Best practices for naming ActiveX controls to keep your Excel project organized

As you work with ActiveX controls in Excel, it's important to follow best practices for naming these controls to keep your project organized. By giving each control a clear and descriptive name, you can easily identify and reference them within your VBA code. This practice helps to streamline your development process and makes it easier to maintain and update your Excel project in the future.

Writing and Assigning Macros to ActiveX Controls

When working with Excel, ActiveX controls can be a powerful tool for creating interactive spreadsheets. These controls can be used to perform various actions, such as data input, selection, and manipulation. In this tutorial, we will explore how to write and assign macros to ActiveX controls to enhance the functionality of your Excel spreadsheets.

A Introduction to the Visual Basic for Applications (VBA) environment for writing macros

The Visual Basic for Applications (VBA) environment in Excel allows users to write and run macros to automate tasks and perform complex operations. Macros are essentially a set of instructions that can be executed to perform a specific task within Excel. To access the VBA environment, you can use the Developer tab in Excel and open the Visual Basic Editor.

Within the VBA environment, you can write and edit macros using the VBA programming language. This language allows you to manipulate Excel objects, such as worksheets, ranges, and controls, to create custom functionality.

B Step-by-step guide on how to write a simple macro for an ActiveX control

To write a simple macro for an ActiveX control, you can follow these steps:

  • Step 1: Open the Visual Basic Editor by clicking on the Developer tab and selecting 'Visual Basic.'
  • Step 2: In the Visual Basic Editor, insert a new module by right-clicking on the Modules folder in the Project Explorer and selecting 'Insert' > 'Module.'
  • Step 3: Write your macro code within the new module. For example, you can create a macro that performs a calculation based on user input from an ActiveX control.
  • Step 4: Once you have written the macro, you can save the module and return to the Excel workbook.

By following these steps, you can create a simple macro that can be assigned to an ActiveX control to perform specific actions within your Excel spreadsheet.

C How to assign a macro to an ActiveX control to perform actions in the spreadsheet

After writing a macro for an ActiveX control, you can assign it to the control to enable it to perform actions in the spreadsheet. To assign a macro to an ActiveX control, follow these steps:

  • Step 1: Ensure that the Developer tab is visible in Excel. If not, you can enable it in the Excel options.
  • Step 2: Click on the Developer tab and select 'Insert' > 'More Controls' to insert an ActiveX control, such as a button or textbox, into the spreadsheet.
  • Step 3: Right-click on the ActiveX control and select 'Properties' to open the properties window.
  • Step 4: In the properties window, locate the 'OnAction' property and enter the name of the macro you want to assign to the control.
  • Step 5: Close the properties window and return to the Excel workbook. The macro is now assigned to the ActiveX control and can be executed when the control is interacted with.

By following these steps, you can effectively assign a macro to an ActiveX control and leverage its functionality to perform specific actions within your Excel spreadsheet.

Interactive Excel Dashboards and Forms with ActiveX Controls

ActiveX controls in Excel allow users to create interactive dashboards and user input forms, enhancing data visualization and user interaction. In this tutorial, we will explore how to use ActiveX controls to build interactive Excel dashboards and create user input forms for data entry.

A. Building interactive Excel dashboards using ActiveX controls

ActiveX controls can be used to create interactive elements in Excel dashboards, such as buttons, checkboxes, and list boxes. These controls enable users to interact with the data and perform various actions, such as filtering data, navigating through different views, and triggering macros.

  • Buttons: ActiveX command buttons can be added to the dashboard to perform specific actions, such as running macros or navigating to different sheets within the workbook.
  • Checkboxes: Checkboxes can be used to toggle the visibility of certain elements in the dashboard, allowing users to customize their view based on their preferences.
  • List boxes: List boxes can be used to create dropdown menus for selecting different options or filtering data based on specific criteria.

B. Creating user input forms for data entry using ActiveX controls

ActiveX controls can also be used to create user input forms for data entry, providing a structured and user-friendly way for users to input data into the Excel workbook. This can be particularly useful for collecting data, conducting surveys, or creating interactive reports.

  • Textboxes: Textboxes can be used to allow users to input text or numerical data into specific cells in the workbook.
  • Option buttons: Option buttons can be used to present users with a set of mutually exclusive choices, allowing them to select one option from a list.
  • Combo boxes: Combo boxes can be used to create dropdown lists for users to select options from predefined lists.

C. Examples of scenarios in which ActiveX controls enhance data visualization and user interaction

ActiveX controls can greatly enhance the user experience and data visualization in Excel. Here are a few examples of scenarios where ActiveX controls can be used to improve user interaction:

  • Interactive sales dashboard: Use ActiveX controls to create buttons for filtering sales data by region, product category, or time period, allowing users to dynamically analyze the data.
  • Data entry form: Create a user input form with textboxes and dropdown lists to collect survey responses or input data into a structured format.
  • Interactive report: Use checkboxes and option buttons to allow users to customize the view of a report by selecting specific metrics or data points to display.

Troubleshooting Common Issues with ActiveX Controls

ActiveX controls are a powerful feature in Excel that allow users to interact with and manipulate data in a more dynamic way. However, like any technology, they can sometimes encounter issues that need to be resolved. Here are some common problems encountered when working with ActiveX controls and how to troubleshoot them:

A. Solving frequent problems encountered when working with ActiveX controls

  • Controls not responding: If you find that your ActiveX controls are not responding when you interact with them, it could be due to a variety of reasons. One common issue is that the control may be disabled. To resolve this, go to the Developer tab, click on 'Design Mode' to enable it, and then try interacting with the control again.
  • Control properties not updating: Sometimes, the properties of an ActiveX control may not update as expected. This could be due to incorrect settings or conflicts with other controls. Check the properties of the control and ensure that they are set correctly. Also, make sure that there are no overlapping controls that could be causing conflicts.

B. Ensuring ActiveX controls work properly across different Excel versions

  • Compatibility issues: ActiveX controls may not work properly across different versions of Excel. To ensure compatibility, it is important to test the controls in different versions of Excel to identify any issues. If compatibility issues are found, consider using alternative controls or updating the controls to be compatible with the specific Excel version.
  • Testing in different environments: It is also important to test ActiveX controls in different environments, such as different operating systems and configurations, to ensure that they work properly across a variety of setups.

C. Steps to take when ActiveX controls fail to function or display properly

  • Check for updates: If ActiveX controls fail to function or display properly, it may be due to outdated versions of the controls. Check for updates from the control provider and ensure that you are using the latest version.
  • Re-register the controls: Sometimes, re-registering the ActiveX controls can resolve issues with their functionality. To do this, open the Command Prompt as an administrator and run the command 'regsvr32 ' to re-register the control.
  • Check for conflicts: Conflicts with other add-ins or controls in Excel can also cause ActiveX controls to fail. Check for any conflicts and disable or remove any conflicting elements to see if the issue is resolved.

Conclusion & Best Practices for Using ActiveX Controls in Excel

ActiveX controls are powerful tools that can greatly enhance the functionality and user experience of Excel workbooks. In this tutorial, we have explored the importance and capabilities of ActiveX controls, as well as best practices for their optimal use. It is important to recap the key points and encourage further exploration and practice to become proficient in advanced Excel features.

A Recap of the importance and capabilities of ActiveX controls in Excel workflows

ActiveX controls provide a wide range of interactive elements that can be added to Excel worksheets, such as buttons, checkboxes, list boxes, and more. These controls allow users to interact with the data and perform various actions, enhancing the usability and functionality of Excel workbooks. They can be used to create dynamic dashboards, interactive forms, and user-friendly interfaces for data entry and analysis.

A compilation of best practices to ensure optimal use of ActiveX controls

  • Security settings: It is important to be mindful of the security implications of using ActiveX controls. Ensure that the security settings in Excel are configured to allow the use of ActiveX controls, while also being cautious of potential security risks associated with their use.
  • Updating controls: Regularly update and maintain ActiveX controls to ensure compatibility with the latest versions of Excel and to benefit from any improvements or bug fixes provided by the developers.
  • Backup strategies: Implement backup strategies to safeguard workbooks that contain ActiveX controls. Regularly back up the workbooks to prevent data loss in case of unexpected issues or errors.

Encouragement to explore and practice using ActiveX controls to become proficient in advanced Excel features

While ActiveX controls can greatly enhance the functionality of Excel workbooks, they require practice and exploration to master. Users are encouraged to experiment with different types of controls, customize their properties, and integrate them into various Excel workflows. By gaining proficiency in using ActiveX controls, users can unlock the full potential of advanced Excel features and create more dynamic and interactive workbooks.

Related aticles

Mastering Excel Dashboards for Data Analysts

The Benefits of Excel Dashboards for Data Analysts

Exploring the Power of Real-Time Data Visualization with Excel Dashboards

Unlock the Power of Real-Time Data Visualization with Excel Dashboards

How to Connect Your Excel Dashboard to Other Platforms for More Focused Insights

Unlocking the Potential of Excel's Data Dashboard

10 Keys to Designing a Dashboard with Maximum Impact in Excel

Unleashing the Benefits of a Dashboard with Maximum Impact in Excel

Essential Features for Data Exploration in Excel Dashboards

Exploring Data Easily and Securely: Essential Features for Excel Dashboards

Real-Time Dashboard Updates in Excel

Unlock the Benefits of Real-Time Dashboard Updates in Excel

Interpreting Excel Dashboards: From Data to Action

Unleashing the Power of Excel Dashboards

Different Approaches to Excel Dashboard Design and Development

Understanding the Benefits and Challenges of Excel Dashboard Design and Development

Best Excel Dashboard Tips for Smarter Data Visualization

Leverage Your Data with Excel Dashboards

How to Create Effective Dashboards in Microsoft Excel

Crafting the Perfect Dashboard for Excel

Dashboards in Excel: Managing Data Analysis and Visualization

An Introduction to Excel Dashboards

Best Practices for Designing an Insightful Excel Dashboard

How to Create an Effective Excel Dashboard

  • Choosing a selection results in a full page refresh.
  • Online Documentation Microsoft Office Excel Microsoft Office Word JavaScript Programming VBA Programming C# Programming
  • Rows Columns
  • Cells Ranges
  • Data Validation
  • Data Analysis
  • Pivot Tables
  • Power Query
  • User Defined
  • Dynamic Formulas
  • Track Changes
  • Named Ranges
  • Conditional Format
  • Dates Times
  • Illustrations
  • Microsoft PowerPoint
  • Shortcut Keys
  • Quant Finance
  • Microsoft Outlook
  • Microsoft Office Excel Worksheet Functions List Ribbon Tabs Explained Keyboard Shortcut Keys Best Practices Search Excel ...
  • Visual Basic Editor
  • Collections
  • Error Handling
  • Files Directories
  • Best Practices
  • Subroutines
  • Enumerations
  • Class Modules
  • Web Services
  • JS Programming
  • VBA Programming Complete Functions List Recording Macros Syntax And Loops Best Practices Search VBA ...
  • Bespoke Development Office Add-in Development JavaScript Office Add-ins VSTO and C# Integration Macros and VBA Programming High Value Consultancy

The ActiveX Command Button can be placed onto a worksheet. Display the "Developer" tab, Controls group and select from the Insert drop-down.

Draw the control onto the worksheet.

When an ActiveX control is added to a worksheet it is actually embedded and becomes an Object of the worksheet. Unlike the Button from the Form Controls there is no quick way to assign a macro.

When you right mouse click on this control the following shortcut menu will be displayed.

Properties - Displays the floating "Properties" window from the VBE Editor. View Code - Opens the Visual Basic Editor application window. Format Control - Displays the "Format Control" dialog box.

To assign a macro to this button you need to use the Visual Basic Editor. Right mouse click on the button and select "View Code". This will open the VBA Editor window and insert the following lines of code.

Notice that this code appears in the worksheet code module that corresponds to the worksheet that contains the button. If you have recorded your macro you need to call the corresponding subroutine from here.

Every time you insert an ActiveX control onto a worksheet, you will be automatically placed in Design Mode. Before you can actually run your macro using the button you need to exit "Design Mode".

Switching to design mode makes moving and resizing the ActiveX controls easier.

The Format Control dialog box is the same for all the ActiveX controls. SS

MrExcel Message Board

  • Search forums
  • Board Rules

Follow along with the video below to see how to install our site as a web app on your home screen.

Note: This feature may not be available in some browsers.

  • If you would like to post, please check out the MrExcel Message Board FAQ and register here . If you forgot your password, you can reset your password .
  • Question Forums
  • Excel Questions

Assign macro to Button inside ActiveX Frame

  • Thread starter Llupo01
  • Start date Feb 13, 2019
  • Tags assign button frame macro work

Active Member

  • Feb 13, 2019

Excel Facts

Norie

Well-known Member

Tom Why are you using a Frame?  

I wanted to learn something new...also I do not want to protect sheet, and I dont want user to move group of controls (if its in group box, they still can move groupbox without turning on designer mode) and I also cannot have it in front of some shape, because they could delete it. And I need to have controls on different color background then is rest of the sheet. And I also dont want to use UserForm for this  

ZVI

MrExcel MVP

You are welcome! May be Ribbon customization is a better way, Ron de Bruin provides excelent examples on that - Ribbon Examples files and Tips  

Similar threads

  • Jan 4, 2024
  • Dave_george
  • Feb 12, 2024
  • Nov 21, 2023
  • CLDMister1xbilliontwos
  • Sep 19, 2023

AndyTampa

  • Feb 7, 2024

Forum statistics

Share this page.

excel assign macro to activex button

We've detected that you are using an adblocker.

Which adblocker are you using.

AdBlock

Disable AdBlock

excel assign macro to activex button

Disable AdBlock Plus

excel assign macro to activex button

Disable uBlock Origin

excel assign macro to activex button

Disable uBlock

excel assign macro to activex button

excel assign macro to activex button

Assign a macro to a button

You can use a button (a form control) to run a macro that performs an action when a user clicks it. For example, you might use a button to automate the printing of a worksheet, the filtering of data, or the calculation of numbers.

After you create a macro , you can assign it to a button you click to run the macro . You can assign a macro to a button on the Quick Access Toolbar or to a button in your own personal group on the ribbon.

If you want a macro button to be available in other workbooks, assign it to a macro that was created in a personal workbook .

Add a macro button to the Quick Access Toolbar

Click File > Options > Quick Access Toolbar .

In the Choose commands from list, click Macros .

Commands for Quick Access Toolbar

Select the macro you want to assign a button to.

Click Add to move the macro to the list of buttons on the Quick Access Toolbar.

To replace the default macro icon with a different button for your macro, click Modify .

Under Symbol , select a button icon for your macro.

Modify Button dialog box

To use a friendlier name for the button, in the Display name box, enter the name you want.

You can enter a space in the button name.

Click OK twice.

The new button appears on the Quick Access Toolbar, where you can click it to run the macro.

Tip:  When you save the workbook , buttons you assign to macros in the personal workbook will be available in every workbook you open.

Add a macro button to your own group on the ribbon

Click File > Options > Customize Ribbon .

Under Customize the Ribbon , in the Main Tabs list , check the Developer box if it is not already checked.

Customize Ribbon dialog box

Pick the tab where you want to add your own group.

For example, pick Home , to add your group to the Home tab.

Select New Group .

That adds New Group (Custom) to the tab you picked.

To use a better name for your new group, click Rename , type the name you want in the Display name box, and then click OK .

You can enter a space in the name. For example, type My Macros .

To add a macro to the group, in the Choose commands from list, click Macros .

Select the macro you want to add to your new group, and then click Add . The macro is added to the My Macros group.

To use a friendlier name, click Rename , and then type the name you want in the Display name box.

You can enter a space in the name.

Your new group appears on the tab you picked, where you can click the button to run the macro.

On the Developer tab, in the Controls group, click Button .

If the Developer tab is not available

Go to Excel > Preferences... > Ribbon & Toolbar .

In the Customize the Ribbon section, under Main Tabs , check the Developer check box, and press OK.

Click the worksheet location where you want the upper-left corner of the button to appear.

In the Assign Macro dialog box, click the name of the macro that you want to assign to the button, and then click OK .

To resize the button, drag the sizing handles.

To specify the control properties of the button, Control+Click or right-click the button, and then click Format Control .

Facebook

Need more help?

Want more options.

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

excel assign macro to activex button

Microsoft 365 subscription benefits

excel assign macro to activex button

Microsoft 365 training

excel assign macro to activex button

Microsoft security

excel assign macro to activex button

Accessibility center

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

excel assign macro to activex button

Ask the Microsoft Community

excel assign macro to activex button

Microsoft Tech Community

excel assign macro to activex button

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

  • Generative AI
  • Office Suites
  • Collaboration Software
  • Productivity Software
  • Augmented Reality
  • Emerging Technology
  • Remote Work
  • Artificial Intelligence
  • Operating Systems
  • IT Leadership
  • IT Management
  • IT Operations
  • Cloud Computing
  • Computers and Peripherals
  • Data Center
  • Enterprise Applications
  • Vendors and Providers
  • United States
  • Netherlands
  • United Kingdom
  • New Zealand
  • Newsletters
  • Foundry Careers
  • Terms of Service
  • Privacy Policy
  • Cookie Policy
  • Copyright Notice
  • Member Preferences
  • About AdChoices
  • E-commerce Affiliate Relationships
  • Your California Privacy Rights

Our Network

  • Network World

shimon brathwaite

How to use Excel macros to save time and automate your work

Excel macros let you automate repetitive tasks for substantial time savings. here’s how to put them to work for you..

interconnecting gears on a keyboard / process / automation / machinery / mechanism / efficiency

If you regularly work with Excel spreadsheets, you probably find yourself repeating the same steps over and over. Wouldn’t it be nice to click a button and have those tasks happen automatically?

That’s where Excel macros come in. You can use macros to automate repetitive tasks, which can save you a lot of time and effort.

What is an Excel macro?

An Excel macro is a recorded sequence of Excel commands and actions that you can play back as many times as you want.  Macros can be used to automate just about any sequence of tasks in Excel, from something as simple as entering your company’s name and address into a spreadsheet to something as complex as creating a custom report. If you can do it in Excel, you can probably automate it with a macro.

To use a macro, you first need to record it. You perform the sequence of steps you want to automate, and Excel tracks them all and saves them in the macro. Once you’ve recorded a macro, you can run it again at any time. You can even assign a keyboard shortcut to a macro, so you can run it with just a few keystrokes.

Excel macros are based on Microsoft’s Visual Basic for Applications (VBA) programming language. When you record a macro, Excel translates your actions into VBA code under the hood. So in addition to creating macros by recording them, you can also write them manually in VBA code. In this article, I will focus primarily on creating macros by recording them — the simplest and quickest method. After that, I will discuss how you can edit or write macros from scratch using VBA and provide some resources for self-learning.

How to record a macro

To help illustrate this process, I will use a small sample data set. Let’s suppose that we are responsible for taking customer names and balances and performing two tasks: first splitting the customer’s name into separate first and last names, and then highlighting everyone who has a balance due that is greater than zero. In this example, we have been given seven customers to work with.

excel macros 01 sample data

Our starting data set. Note the File menu at the left end of the Excel Ribbon. (Click image to enlarge it.)

To create a macro, we’ll use the Developer tab in the Ribbon toolbar at the top of the Excel window. This is not present by default, so we will need to add it. Click on the File tab at the far left of the Ribbon (highlighted in the screenshot above) and then, on the screen that appears, click Options at the bottom of the left column.

The Excel Options screen appears. Select Customize Ribbon from the left navigation bar. Then, in the “Customize the Ribbon” area on the right, look in the “Main Tabs” list and check the Developer checkbox. Click OK .

excel macros 02 excel options developer tab

Check the Developer checkbox to turn on the Developer tab in the Ribbon. (Click image to enlarge it.)

(In macOS, click the Excel menu at the top of the screen and choose Preferences > Ribbon & Toolbar . In the “Customize the Ribbon” area on the right, look in the “Main Tabs” list and check the Developer checkbox. Click Save .)

Once you have the Developer tab, click on it and you will see options similar to those shown on the screen below.

excel macros 03 macro commands ribbon

The highlighted commands on the Developer tab help you record and manage macros. (Click image to enlarge it.)

To start recording your first macro, click on the Record Macro button, and you will be presented with the options below. First, create a name for your macro, keeping in mind that you can’t use spaces. For readability, you may want to separate words with something like _ or – . Then add a shortcut key or description if you like, but these are not required.

excel macros 04 record macro

To get started, give your macro a name. (Click image to enlarge it.)

Once you hit OK , the icon should change to indicate that the macro is recording your actions. It’s important that you only perform the actions that you want the macro to do and nothing else from this point until you click on Stop Recording .

Now that the macro is recording, let’s begin our tasks. First, highlight the Balance Due column, then right-click and select Insert Column . This will add a new column in between the Customer Name and Balance Due columns.

excel macros 05 column inserted

Insert a new column to the left of the Balance Due column. (Click image to enlarge it.)

Next, we’ll rename the columns, replacing “Customer Name” with “First Name” and adding the heading “Last Name” to the column we just created. Select the customer names in the first column (cells A:2 to A:8), select the Data tab in the Ribbon, and select the Text to Columns command.

A wizard appears showing the following options. Select Delimited and hit Next .

excel macros 06 text to columns wizard

On the first screen of the Convert Text to Columns Wizard, select Delimited . (Click image to enlarge it.)

Next, select the Space checkbox to designate that words separated by a space should go into separate columns. Click Next .

excel macros 07 text to columns wizard2

On screen 2 of the wizard, select Space as your delimiter. (Click image to enlarge it.)

For the last option, keep everything the same and hit Finish .

excel macros 08 text to columns wizard3

Don’t make any changes to the last screen of the wizard. (Click image to enlarge it.)

You should have the following result, with first and last names in separate columns.

excel macros 09 first last names separated

The first and last names are now in separate columns. (Click image to enlarge it.)

Lastly, we need to highlight every customer with a balance of more than zero. Highlight all of the data in the third column (cells C:2 to C:8) and then click Home > Conditional Formatting .

excel macros 10 ribbon conditional formatting

The final step is to apply conditional formatting rules to the data. (Click image to enlarge it.)

Choose Highlight Cell Rules and then Greater Than . Enter 0 and click OK to highlight every customer who has a balance greater than zero.

excel macros 11 format cells dialog box

Formatting cells that are greater than zero.

This should be the final result:

excel macros 12 formatted data in spreadsheet

All cells with a balance of greater than zero are now highlighted in light red. (Click image to enlarge it.)

Now that you have completed the tasks sequence, go back to the Developer tab and click Stop Recording . Your first Excel macro is complete.

excel macros 13 stop recording

When you’re done recording your macro, hit Stop Recording . (Click image to enlarge it.)

Important notes about working with macros

If you want to run your macro again, simply click on the Macros button and it will be available for you to run. Or, if you assigned the macro a shortcut, simply press the key combination to run it.

excel macros 14 macros list

Click the Macros button at any time to see the list of macros available for the workbook. (Click image to enlarge it.)

Note that you cannot save a spreadsheet with macros as a traditional .xlsx workbook. You must save it as an Excel Macro-Enabled Workbook (.xlsm) to avoid losing your macros.

excel macros 15 save as xlsm

Save your workbook in .xlsm format to preserve its macros. (Click image to enlarge it.)

Once you’ve made that change, any time you want to work with a fresh data set you can simply reopen the workbook and import the data you want to work on by going to the Data tab and selecting Get Data . You will be able to import data from files, databases, and other online services.

excel macros 16 import data

Be aware that in many cases macros are disabled by default. That’s because, as Microsoft notes, “VBA macros are a common way for malicious actors to gain access to deploy malware and ransomware.” To protect organizations from such threats, Microsoft now blocks macros in files from the internet — and sometimes in those stored on the company’s shared drives. Your organization may have imposed additional restrictions on macros.

So whenever you open an Excel workbook with macros in it (including your own), there is a chance that you will see a warning message like the one below. If your workbook is one that you created or from a trusted source, go ahead and enable it. If the macro is from an untrusted source, however, don’t enable it, because it may be malware.

excel macros 17 enable macros

You might see a warning when you open a workbook with macros in it.

Another important feature to be aware of when recording macros is the use of relative references. This feature makes it so that regardless of where the data begins on the spreadsheet, the macro will be able to find it and start the processing there.

For example, the macro we created will always begin processing at column A because relative references were not toggled on. However, if we did the same operations but clicked on Use Relative References first, then the macro would be able to detect where the information starts (Column C, for instance) and begin its processing from that point. This feature is useful if the data you are working with will not always start at the same point.

excel macros 18 relative references

Select Use Relative References if your data won’t always start at the same point in the worksheet.

How to edit or create a macro with code

If you want to see the VBA code behind a macro, go to the Developer tab, click Macro , select the macro, and then click Edit .

You will be taken to a pane where you can see the source code for the macro you created. In the screenshot below, the underlined items show actions that we performed, such as changing the names of the headings and selecting rows. You can alter these for different use cases. For example, if the data you work with runs from range A1:A20, you may want to expand the range to include all possible cells.

excel macros 19 vba code

Peeking at the code that underlies a macro. (Click image to enlarge it.)

If want to try your hand at writing macros from scratch, there are several resources online for learning to write VBA scripts, including  Codewars , Udemy , and Codecademy . Back in Excel, click Developer > Macros > Create . You’ll be taken to a blank pane where you can write VBA code.

Related content

Feds say microsoft security ‘requires an overhaul’ — but will it listen, about the best places to work in it, how dhl harnessed genai to unify 200 career sites into a single platform, how many jobs are available in technology in the us, from our editors straight to your inbox.

shimon brathwaite

Shimon Brathwaite is a cybersecurity professional, consultant, and writer at SecurityMadeSimple . He is a graduate of Ryerson University in Toronto, Canada and has worked in several businesses in security-focused roles. His professional certifications include GCIH, Security+, CEH, and AWS Security Specialist. Contact him for writing engagements, consulting, or to ask questions!

More from this author

How (and why) to use conditional formatting in excel, how to use excel formulas and functions, microsoft forms cheat sheet: how to get started, microsoft visio cheat sheet: how to get started, most popular authors.

excel assign macro to activex button

  • Howard Wen Contributing Writer

Show me more

Apple's find my system is coming to android.

Image

How to use a smartphone as a mobile hotspot

Image

Android 14 Upgrade Report Card: Predictable unpredictability

Image

The link between smartphones and social media addiction

Image

Sam Bankman-Fried gets 25 years in prison

Image

How to combat social media addiction

Image

IMAGES

  1. Excel Macros

    excel assign macro to activex button

  2. How to Assign a Macro to a Button in Excel

    excel assign macro to activex button

  3. Excel Assign Macro to Button (3 Suitable Examples)

    excel assign macro to activex button

  4. Add a Button and Assign a Macro in Excel

    excel assign macro to activex button

  5. How to Assign Macro to Button in Excel (2 Easy Methods)

    excel assign macro to activex button

  6. How to Assign a Macro to a Button in Excel (Easy Guide) » Trump Excel

    excel assign macro to activex button

VIDEO

  1. [Excel Series] กว่าจะเป็นฐานข้อมูล EP5 : Command Button (Form Control และ ActiveX Control)

  2. Assign a macro to command button in excel

  3. excel userform part 2 assign macro to command button

  4. Active X Drop Down List Setup

  5. Excel 2007

  6. Option Button (ActiveX) control in Excel VBA- Example 2

COMMENTS

  1. Assign a macro to a Form or a Control button

    Add a button (Form control) On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button . Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears. Assign a macro to the button, and then click OK.

  2. How to Assign a Macro to a Button in Excel (Easy Guide)

    To insert an ActiveX button and then assign a macro to it, follow the below steps: Click on the Developer tab. In the Control group, click on Insert. In the options that appear, in the ActiveX Controls options, click on the Command Button option. Click anywhere on the worksheet.

  3. How To: Assign a Macro to an ActiveX Control

    Instead of the missing Assign Macro item, click on View Code in the context menu. This inserts an event procedure in the code module behind the worksheet containing the button. You can write code within this event procedure that runs whenever CommandButton1 is clicked. Note the two dropdowns at the top of the code module.

  4. Excel Assign Macro to Button (3 Suitable Examples)

    In order to add an Activex Control button, go to the Developer tab > Insert tab > ActiveX Control From there click on the Command Button icon. Then draw the button in the worksheet, and then right-click on it. From the context menu, click on the View Code. And then paste the code into the code editor.

  5. Add a Button and Assign a Macro in Excel

    Add a Macro Button. In Excel, select the Developer tab, then click on the " Insert " dropdown in the Controls section. There are several types of controls divided into two sections, "Form Controls" and "ActiveX Controls". For now, just click on the Button control under " Form Controls ". Next, move the mouse anywhere over the ...

  6. Assign a macro to a button using Excel and VBA

    METHOD 1. Assign a macro to a Form Control button. Right-click in a button > Select Assign Macro > Select a macro > Click OK. 1. Right-click on the Form Control button. 2. Select Assign Macro. 3. Select the macro that you want to assign to the button.

  7. Assign a macro to a button

    Add a macro button to the Quick Access Toolbar. Click File > Options > Quick Access Toolbar. In the Choose commands from list, click Macros. Select the macro you want to assign a button to. Click Add to move the macro to the list of buttons on the Quick Access Toolbar. To replace the default macro icon with a different button for your macro ...

  8. How to create and set a ActiveX Control CommandButton as a variable

    I would like to have the button set as a Variable (i think), so I can edit the formatting/properties and hopefully add a macro to the button later. Dim buttonControl As MSForms.CommandButton. Set buttonControl = _. ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _. Link:=False, _. DisplayAsIcon:=False, _.

  9. Excel Tutorial: How To Assign A Macro To A Button In Excel

    By linking a specific macro to a button, users can quickly execute complex tasks with a single click. In this tutorial, we will walk through the process of assigning a macro to a button in Excel. A. Linking the macro to the button. Step 1: Open the Excel workbook. First, open the Excel workbook in which you want to assign a macro to a button.

  10. Excel Add VBA Button

    Adding a Form Control Button. In the Ribbon, select Developer > Insert > Form Controls > Button. Click and drag in the worksheet to create a button. As soon as you release the mouse button, the assign macro dialog box will appear. Scroll down to 1) select the macro you wish to assign to the button, and then, 2) click OK.

  11. Assign macro with arguments to a Form Control button

    When returning to the Assign Macro window, you will notice Excel has added the name of the workbook into the Macro name box. 'Name of workbook.xlsm'!'lstBoxCount "Sheet1", "lstBox1"' At first, this may seem annoying. But Excel is helping us here, as this is now displaying the syntax required to call a macro from another workbook.

  12. Macro Buttons in Excel

    Steps to Assign a Macro to an ActiveX Button: Go to your Developer tab; In the Controls section, click on Insert (it looks like a toolbox); Under ActiveX Controls, click on "Button"; With your cursor, draw the button on your sheet; Right-click on your new button and select View Code

  13. How to Create a Macro Button in Excel (3 Quick Methods)

    Steps: Click as follows: Developer > Insert > ActiveX Controls > Button. Like the previous section, set the button size using the mouse. Next, right-click on the button and select View Code from the context menu. By using the other menu you can edit the macro button as you require.

  14. ActiveX Controls in Excel VBA (In Easy Steps)

    Learn how to create ActiveX controls, such as command buttons, text boxes, list boxes etc. To create an ActiveX control in Excel VBA, execute the following steps. 1. On the Developer tab, click Insert. 2. For example, in the ActiveX Controls group, click Command Button to insert a command button control.

  15. Excel Tutorial: How To Use Activex Controls In Excel

    Step 1: Open the Visual Basic Editor by clicking on the Developer tab and selecting 'Visual Basic.'. Step 2: In the Visual Basic Editor, insert a new module by right-clicking on the Modules folder in the Project Explorer and selecting 'Insert' > 'Module.'. Step 3: Write your macro code within the new module.

  16. I can't assign a macro to a button.

    Platform. Windows. Oct 18, 2023. #1. I have a macro that I want to assign to a button, but when I right-click>assign macro, the name of the macro won't show on the list. I have macros enabled in the trust center, and, this is the only workbook open. I can see the macro in the VBA editor, and it is under the right module in the correct VBA ...

  17. Excel Macros

    When an ActiveX control is added to a worksheet it is actually embedded and becomes an Object of the worksheet. Unlike the Button from the Form Controls there is no quick way to assign a macro.. Shortcut Menu. When you right mouse click on this control the following shortcut menu will be displayed.

  18. Assign macro to Button inside ActiveX Frame

    Usual way "double click to button" to assign macro does not work, in design mode right click on frame -> Frame object -> edit and then trying to assign macro also does not work (not available to assign code), view code also does not work (it shows only code of the Frame). Please can anybody help, thank you very much in adavance.

  19. Assign a macro to a button

    Click File > Options > Quick Access Toolbar. In the Choose commands from list, click Macros. Select the macro you want to assign a button to. Click Add to move the macro to the list of buttons on the Quick Access Toolbar. To replace the default macro icon with a different button for your macro, click Modify. Under Symbol, select a button icon ...

  20. How to use Excel macros to save time and automate your work

    Select Customize Ribbon from the left navigation bar. Then, in the "Customize the Ribbon" area on the right, look in the "Main Tabs" list and check the Developer checkbox. Click OK. Shimon ...

  21. excel

    In order to run the macro attached to an ActiveX control you can directly call the sub with the following command: CallByName Worksheets("Sheet1"), "CommandButton21_Click", VbMethod. Note, that (by default) Excel will create a Private Sub on the worksheet for the button. If you wish to call these subs from a module then you will have to remove ...