excel assign macro to activex button

Assign a macro to a Form or a Control button

You can use a Form control button or a command button (an ActiveX control) to run a macro that performs an action when a user clicks it. Both these buttons are also known as a push button, which can be set up to automate the printing of a worksheet, filtering data, or calculating numbers. In general, a Form control button and an ActiveX control command button are similar in appearance and function. However, they do have a few differences, which are explained in the following sections.

In the sections below, learn how to add a macro to a button in Excel—for Windows or the Mac.

Note: ActiveX controls are not supported on the Mac.

Macros and VBA tools can be found on the Developer tab, which is hidden by default.

The first step is to enable it. For more information, see the article: Show the Developer tab .

Add a button (Form control)

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 .

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

Add a command button (ActiveX control)

Click the worksheet location at which you want the upper-left corner of the command button to appear.

In the Controls group, click View Code . This launches the Visual Basic Editor. Ensure that Click is chose in the drop-down list on the right. The sub procedure CommandButton1_Click (see the figure below) runs these two macros when the button is clicked: SelectC15 and HelloMessage .

A subprocedure in the Visual Basic Editor

In the subprocedure for the command button, do either of the following:

Enter the name of an existing macro in the workbook. You can find macros by clicking Macros in the Code group. You can run multiple macros from a button by entering the macro names on separate lines inside the subprocedure.

As necessary, add your own VBA code.

To run the VBA code that is now part of the button, click the ActiveX command button that you just created.

To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, turn on Design Mode .

Note: Before you click Properties , make sure that the object for which you want to examine or change properties is already selected.

The Properties box appears. For detailed information about each property, select the property, and then press F1 to display a Visual Basic Help topic. You can also type the property name in the Visual Basic Help Search box. The following table summarizes the properties that are available.

Macros and VBA tools can be found on the Developer tab, which is hidden by default, so the first step is to enable it

Go to Excel > Preferences… > Ribbon & Toolbar .

Select the checkbox next to Developer to add it to the ribbon

Follow these steps:

Note: If you have already inserted a button, you can right-click on it, and select Assign Macro .

Assign a macro to the button and click OK .

To specify the control properties of the button, right-click it, and then select Format Control... .

Add a command button (Visual Basic control)

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

In the Assign Macro dialog box, select New , which will open the Visual Basic Editor ( VBE ) to a pane with the following code:

In the subprocedure for the command button, between the Sub and End Sub lines, do either of the following:

Enter the name of an existing macro in the workbook. You can run multiple macros from a button by typing the macro names on separate lines inside the sub procedure

Add your own VBA code.

To edit the button, right-click it and choose Visual Basic .

To specify the control properties of the button, right-click it, and then select Format Control... .

Need more help?

You can always ask an expert in the Excel Tech Community or get support in the Answers community .

Add or edit a macro for a control on a worksheet

Enable or disable macros in Office files

Enable or disable ActiveX settings in Office files

Want more options?

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

Microsoft 365 subscription benefits

Microsoft 365 training

Microsoft security

Accessibility center

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

Ask the Microsoft Community

Microsoft Tech Community

Windows Insiders

Microsoft 365 Insiders

Was this information helpful?

Thank you for your feedback.

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

-- By Sumit Bansal

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.

Edit Text option to add text to the 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

Right click and then click on Format 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.

Formatting options for a form control button

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:

Select do not move or size with cells options for the form control button

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 ActiveX Control Command Button option in the developer tab

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 .

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

Comments are closed.

BEST EXCEL TUTORIALS

Best Excel Shortcuts

Conditional Formatting

Creating a Pivot Table

Excel Tables

INDEX- MATCH Combo

Creating a Drop Down List

Recording a Macro

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.

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.

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.

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.

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.

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.

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.

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 .

assigning a macro to an activex command button

Help! Attempting to get a command button to run a simple pre-recorded macro. Zero experience of visual basic , office assistant telling me to type in the macro!!??!! Hello , what. Please help me or shoot me.Brain hurting.

Excel Facts

Andrew Poulsom

Mrexcel mvp.

If you CommandButton is from the Control ToolBox, right click it and choose View Code. You should something like this: Code: Private Sub CommandButton1_Click() End Sub You can copy your recorded code into there (excluding its Sub and End Sub lines) or run the existing Macro like this: Code: Private Sub CommandButton1_Click() Call Macro1 End Sub where Macro1 is the name of your recorded Macro.

PEACHY said: Please help me or shoot me. Click to expand...

Oct 29, 2013

Board Regular

Mar 21, 2014

Richie(UK) said: BANG! Seriously though, lets assume that you already have your recorded macro. If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your workbook. Under the project will be a folder called 'Modules' - double-click on this and it will show that it contains a module called 'Module1'. Within this module you will see the macro that you recorded, make a note of the name (probably Macro1). Now, go back to the Excel window where you have added the Command Button from the Controls Toolbox. Double-click on the CommandButton and you will be taken back to the VBE between some lines of code like this: Code: Private Sub CommandButton1_Click() End Sub Between those lines write the name of your macro (Macro1). Alternatively, you can type 'Module1' immediately followed by a full stop (.) - this will then give you the option to choose from the macros contained in Module1. Now go back to Excel and exit Desin Mode. That's it, whenever you click the button your routine will be executed. HTH Click to expand...

May 7, 2015

Hi, I used macros for my activex controls button. But it works wrong when I click (copies the same thing), but works properly when I run macro's itself. How can I fix it? code: Private Sub CommandButton1_Click() Sheets("Archive").Select ActiveSheet.unprotect Password:="111" Call Macro1 Sheets("Archive").Select ActiveSheet.protect Password:="111" End Sub Thanks in advance

Well-known Member

Jun 13, 2017

Thanks Richie (UK)!! Clarifying that you need to exit design mode afterwards was useful, and saved me some time.

Jan 18, 2018

Private Sub CommandButton1_Click() ' I have utilized the "Custom UI Editor For Microsoft Office", and made a Custom UI Ribbon ' The macro is called: Sub Process_Bookmarks(control As IRibbonControl) ' To get it to work here with the button, remove this part: control As IRibbonControl ' So, it will be renamed to: Sub Process_Bookmarks() Call Process_Bookmarks End Sub

Similar threads

Forum statistics

Share this page.

We've detected that you are using an adblocker.

Which adblocker are you using.

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock

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

Add a Button and Assign a Macro in Excel

Excel buttons.

In Excel, Buttons are used to call Macros. This tutorial will cover how to create Excel buttons, assign Macros to them, adjust their properties, and more.

By default, Excel macros are accessible in a list via the “Macros” button on the View ribbon.

Often though, you’ll want to provide easy access to a particular macro directly on your worksheet. This can be achieved using a Button control.

A Button control looks like a Microsoft Windows button, and runs a macro when clicked. It’s a much handier way to access your most commonly used macros, and is an easy way to expose custom functionality to other users of your workbook.

Run a Macro From a Button

To run a Macro from a button in Excel, simply click the button:

The Excel Developer Tab

Buttons are accessible via the Developer Tab.

Unfortunately, Excel hides the Developer tab by default. If you don’t see the Developer Ribbon, follow these steps:

Click File > Options in the list on the left-hand border

In the Options dialog select C ustomize Ribbon > Customize the Ribbon > Main Tabs and add a check-mark in the box for “Developer”, and click OK .

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 worksheet surface, then hold left-click and drag the mouse to draw the outline of a rectangle. When you release left-click, a new dialog will appear titled “ Assign Macro ”.

Assigning a Macro to a Button

Here you can assign an existing Macro to the button, record a new macro, create a new macro from scratch using VBA, or click “Cancel” and return to your button later.

Assign Existing Macro to a Button

To assign an existing Macro, you simply select the macro’s name in the list, then click OK.

Edit an Existing Macro Before Assigning to a Button

To edit a macro before assigning it to the button, select the macro’s name in the list and click the “Edit” button (the “New” button text changes to “Edit”).

Record a Macro and Assign to Button

Write VBA Procedure and Assign to Button

To write a new macro for the button, type a new name for your macro in the textbox at the top of the dialog, then click “New”. Excel will bring up the VB Editor, in which you’ll see a new empty macro procedure with the name you entered. This procedure will be stored in a new module, visible in the Project window.

Change Macro Assigned to Button

To change the Macro that’s assigned to a button, simply right-click the button and select Assign Macro :

Here you can see the assigned Macro and make any desired changes.

How to Adjust Button Properties in Excel

Move or resize excel button.

After you’ve placed a button, you can easily move or resize it. To perform any of these actions, right-click on the button. Then you can left-click and drag the button to your desired location or resize it.

Rename Button

With the button selected, left-click on the button text to edit.

To add multiple lines, simple press the ENTER key.

Format Button

To format other button properties, Right-Click > Format Control

Here you can adjust font sizes, and many other button properties:

Of particular note is the “Properties” tab, which changes how the button behaves as surrounding rows and columns are inserted, deleted, resized, or hidden/unhidden.

Assign a Macro to a Shape

Besides buttons, macros can assigned to other objects like Pictures, Textboxes, and Shapes. With a Picture or Shape, you can make a button that looks any way you like. Excel includes a wide variety of customizable Shapes including polygons, arrows, banners, and more that may be better suited to your worksheet than a regular button control.

Shapes are accessed from the Insert tab:

Select the shape you want from the Shape dropdown, draw it onto your worksheet as you would a button control, then right-click it and select “Assign Macro…” from the pop-up dialog. The options are the same as assigning a macro to a button.

Assign a Macro to a Hyperlink

Macros can also be assigned to hyperlinks by using VBA Events . Events are procedures that are triggered when certain actions are performed:

Events require knowledge of VBA. To learn more about events, visit our VBA Tutorial .

Ultimate VBA Add-in

Offer expires soon!

Sale Ends Friday at Midnight

VBA (Macros) Code Examples Add-in

How to Assign a Macro to a Button in Excel

March 07, 2018

Running macros in your worksheet is a tremendous help. You can automate so many things with VBA that there have been countless books written on the topic. However, it’s not always so simple to run a macro. Your users may not know how to run a macro and you want to make it easy for your users to get their work done. Wouldn’t it be great to have a button on your worksheet and you can tell your users “Just click on this and such-and-such will be done automatically”?

That’s what this post is all about. Let’s get started.

Assign a Macro to a Simple Button

To begin, you’ll need to have the Developer Tab enabled. Click here for more info on that.

After you have the developer tab enabled, open up the Visual Basic Editor, add a new module and let’s add this simple code snippet in there:

This will be our simple macro to run when we click on our button.

Since we have the Developer Tab enabled, we can create a button and assign it to a macro:

Here’s an animated gif to help illustrate the point:

Does this article help you? If so, please consider supporting me with a coffee ☕️

Assign a Macro to a Shape

Now that we have the main idea down, let’s add some style to our worksheet. Using the Button from the Developer Tab is not the only way to run a macro from a button. You can also use Excel Shapes!

To achieve this, we follow a similar procedure from before, but the difference is where we get our button from. Here are the steps to assign a macro to a shape in your worksheet:

Here’s an animated gif to show more detail:

Changing the Assigned Macro

To change the macro assigned to the button, simply right-click on it, then choose Assign Macro. From there, you can select a new macro to use.

Deleting the Button

To delete the button, you need to get it back into Edit Mode. To do this, right-click on the button. When you do this, the context menu will show up. You can click back to the worksheet (but DON’T double-click!) and you will still be in Edit Mode. From here, you can hit the Delete key on your keyboard and it will remove the button.

How to run macro in Excel and create your own macro button

In this tutorial, we'll cover many different ways to run a macro in Excel - from the ribbon and VB Editor, with a custom keyboard shortcut, and by creating your own macro button.

Though running an Excel macro is a simple thing for experienced users, it might not be immediately obvious to beginners. In this article, you will learn several methods to run macros, some of which may completely change your way of interacting with Excel workbooks.

How to run a macro from Excel ribbon

One of the fastest ways to execute VBA in Excel is to run a macro from the Developer tab. If you have never dealt with VBA code before, you may need to activate the Developer tab first. And then, do the following:

Tip. If the Developer tab is not added to your Excel ribbon, press Alt + F8 to open the Macro dialog.

Run a macro with custom keyboard shortcut

If you execute a certain macro on a regular basis, you can assign a shortcut key to it. A shortcut can be added while recording a new macro and to an existing one. For this, carry out these steps:

On the Developer tab, in the Code group, click Macros .

Click Options to edit the macro parameters.

For lowercase letters, the shortcut is Ctrl + letter .

Close the Macro dialog box.

Tip. It is recommended to always use uppercase key combinations for macros ( Ctrl + Shift + letter ) not to override the default Excel shortcuts. For example, if you assign Ctrl + f to a macro, you will lose the ability to call the Find and Replace dialog.

Once the shortcut is assigned, simply press that key combination to run your macro.

How to run macro from VBA Editor

If you aim to become an Excel pro, then you should definitely know how to start a macro not only from Excel, but also from the Visual Basic Editor. The good news is that it's a lot easier than you might expect :)

Alternatively, you can use one of the following shortcuts:

Tip. If you like operating Excel from you keyboard, this tutorial may come in handy: 30 most useful Excel keyboard shortcuts .

How to create a macro button in Excel

The traditional ways of running macros are not hard, but still might present a problem if you are sharing a workbook with someone who has no experience with VBA - they simply won't know where to look! To make running a macro really easy and intuitive for anyone, create your own macro button.

Click anywhere in the worksheet. This will open the Assign Macro dialogue box.

If the text does not fit in the button, make the button control bigger or smaller by dragging the sizing handles. When finished, click anywhere on the sheet to exit the edit mode.

Tip. You can also assign a macro to an existing button or other Form controls such as spin buttons or scrollbars. For this, right-click the control inserted in your worksheet and choose Assign Macro from the pop-up menu.

Create a macro button from a graphic object

Regrettably, it is not possible to customize the appearance of button controls, because of which the button we created a moment ago does not look very nice. To make a really beautiful Excel macro button, you can use shapes, icons, images, WordArt and other objects.

As an example, I'll show you how you can run a macro by clicking a shape:

How to add a macro button to Quick Access Toolbar

The macro button inserted in a worksheet looks good, but adding a button to each and every sheet is time-consuming. To make your favorite macro accessible from anywhere, add it to the Quick Access Toolbar . Here's how:

Click OK twice to close both dialog windows.

How to put a macro button on Excel ribbon

In case you have a few frequently used macros in your Excel toolbox, you may find it convenient to have a custom ribbon group, say My Macros , and add all popular macros to that group as buttons.

First, add a custom group to an existing tab or your own tab. For the detailed instructions, please see:

And then, add a macro button to your custom group by performing these steps:

Click OK to save your changes and close the main dialog box.

How to run a macro on opening a workbook

Sometimes you may want to run a macro automatically on opening a workbook, for example, to display some message, run script or clear a certain range. This can be done in two ways.

Run macro automatically by using Workbook_Open event

Below are the steps to create a macro that automatically runs whenever you open a specific workbook:

For example, the following code will display a welcome message each time the workbook is opened:

Trigger macro on workbook opening with Auto_Open event

Another way to run a macro automatically on workbook opening is by using the Auto_Open event. Unlike the Workbook_Open event, Auto_Open() should sit in a standard code module, not in ThisWorkbook .

Here are the steps to create such a macro:

A macro runs automatically whenever the workbook is opened.

Here's an example of the real-life code that displays a message box on workbook opening:

Note! The Auto_Open event is deprecated and available for backwards compatibility. In most cases, it can be replaced with the Workbook_Open event. For more information, please see Workbook_Open vs. Auto_Open .

The message box is displayed every time you open the workbook.

Now that you know lots of ways to run a macro in Excel, you just need to choose the one best suited for your needs. I thank you for reading and hope to see you on our blog next week!

You may also be interested in

Table of contents

How to Add a Button in Excel

In Excel, users can add macro-enabled buttons on the worksheets and can run macros by just clicking on them.

Users can use these macro-enabled buttons to perform several different tasks like filtering data, selecting data, printing a worksheet, running formulas, and calculations just by clicking on the buttons.

Adding buttons and embedding the macros to them is easier. Excel has multiple ways to add the macro-enabled buttons to the worksheet. Below, we have some quick and easy ways mentioned for you to add the macro buttons in Excel.

Add Macro Buttons Using Shapes

Users can create buttons in excel using shapes. Creating buttons using shapes has more formatting options over the buttons created from Control buttons or ActiveX buttons. Users can change the design, color, font, and style of the button created using shapes.

Add Macro Buttons Using Form Controls

Add Macro Buttons Using ActiveX Controls

IMAGES

Assigning A Macro To A Button
Assign a Macro to a Button
Assign A Macro To A Button
Assigning A Macro To A Button
Macro Buttons in Excel
Assign a macro to a command button in Excel

VIDEO

VBA Macros: Automate Excel Functions With Macros
Excel Delete Blank Rows VBA Assign Macro To Button
Easy Assign values in Excel #exceltips #exceltutorial #exceltricks #finance #geekinthesheet
How to Enable Macros in Excel 2013
Change The Color Of ActiveX Control Button With Changing The Properties
How To Add Button And Macro To Open Form

COMMENTS

Assign a macro to a Form or a Control button
Add a command button (ActiveX control) · Enter the name of an existing macro in the workbook. You can find macros by clicking Macros in the Code group. You can
How to Assign a Macro to a Button in Excel (Easy Guide)
Assign a Macro to an ActiveX Control Button · Click on the Developer tab · In the Control group, click on Insert. · In the options that appear, in
How To: Assign a Macro to an ActiveX Control
Click the Command Button button, and drag a rectangle in the worksheet where you want the button. No Assign Macro dialog pops up, however
assigning a macro to an activex command button
If you press Alt&F11 you will be taken to the Visual Basic Editor (VBE). Here you should see, in the Project window, the VBAProject associated with your
How to Assign Macro to a Command Button in Excel
Please watch: "25 Advanced Excel Shortcuts" https://www.youtube.com/watch?v=mRpKwsnydM4 --~--How to Assign a Macro to a Command Button in
How to Create a Macro Button in Excel
In this video, I demonstrate how to assign a macro to a button in Excel. The video runs you through how to create a simple navigation button
Excel Macros
The ActiveX Command Button can be placed onto a worksheet. Display the "Developer" tab, Controls group and select from the Insert drop-down.
Add a Button and Assign a Macro in Excel
Select the shape you want from the Shape dropdown, draw it onto your worksheet as you would a button control, then right-click it and select “Assign Macro…”
How to Assign a Macro to a Button in Excel
Assign a Macro to a Simple Button · In the Developer Tab of the ribbon, simply click on Button (in Windows, click on Insert, and then under Form
How to run macro in Excel and create a macro button
How to add a macro button to Quick Access Toolbar · Right-click the Quick Access Toolbar and choose More Commands… from the context menu. · In the
How to Add a Button in Excel
Add Macro Buttons Using Form Controls · First, go to the “Developer” tab and click on the “Insert” icon under the “Control” group on the ribbon. · After that