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.
Button (Form control) | Command button (ActiveX control) |
The form control button (push button) is assigned to run a macro. You can right click on the button to edit the text or to format it. This is Excel 2010 screenshot. Easily insert multiple macro buttons to go to (each) other worksheets in Excel. For inserting a macro button to go to another worksheet, normally you have to finish all above 5 steps, and it will be quite tedious to insert many macro buttons to go to each worksheet in the current workbook. We could record a new macro, or edit it manually. Let’s edit the macro to see what its instructions look like. Click the Macros button. Here is the last macro I recorded, 'FillDaysRelative'. Select it, and then click Edit. This opens the macro in a special text editor called the Visual Basic Editor.
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)
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.
To specify the control properties of the button, right-click the button, and then click Format Control.
Add a command button (ActiveX control)
On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button .
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.
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.
Close the Visual Basic Editor, and click Design Mode to ensure design mode is off.
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.
To specify the control properties of the command button, on the Developer tab, in the Controls group, click Properties . You can also right-click the command button, and then click Properties.
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.
If you want to specify | Use this property |
|---|---|
General: | |
Whether the control is loaded when the workbook is opened. (Ignored for ActiveX controls.) The Silmarillion is a book written by J R Tolkien who is an. English writer. The book is a collection of narratives which talks about the universe of Ea in which different lands exist. It is in these lands that the stories of the Hobbit and Lord of The Rings take place. Download The Silmarillion – J. Tolkien ebook A new hardback edition with a cover design by Tolkien himself, to complement the popular Hobbit and Lord of the Rings hardbacks. Includes new Preface by J.R.R. Tolkien unique to this edition. The silmarillion free pdf download. | AutoLoad (Excel) |
Whether the control can receive focus and respond to user-generated events. | Enabled (Form) |
Whether the control can be edited. | Locked (Form) |
The name of the control. | Name (Form) |
The way the control is attached to the cells below it (free floating, move but do not size, or move and size). | Placement (Excel) |
Whether the control can be printed. | PrintObject (Excel) |
Whether the control is visible or hidden. | Visible (Form) |
Text: | |
Font attributes (bold, italic, size, strikethrough, underline, and weight). | Bold, Italic, Size, StrikeThrough, Underline, Weight (Form) |
Descriptive text on the control that identifies or describes it. | Caption (Form) |
Whether the contents of the control automatically wrap at the end of a line. | WordWrap (Form) |
Size and Position: | |
Whether the size of the control automatically adjusts to display all the contents. | AutoSize (Form) |
The height or width in points. | Height, Width (Form) |
The distance between the control and the left or top edge of the worksheet. | Left, Top (Form) |
Formatting: | |
The background color. | BackColor (Form) |
The background style (transparent or opaque). | BackStyle (Form) |
The foreground color. | ForeColor (Form) |
Whether the control has a shadow. | Shadow (Excel) |
Image: | |
The bitmap to display in the control. | Picture (Form) |
The location of the picture relative to its caption (left, top, right, and so on). | PicturePosition (Form) |
Keyboard and Mouse: | |
The shortcut key for the control. | Accelerator (Form) |
A custom mouse icon. | MouseIcon (Form) |
The type of pointer that is displayed when the user positions the mouse over a particular object (standard, arrow, I-beam, and so on). | MousePointer (Form) |
Whether the control takes the focus when clicked. | TakeFocusOnClick (Form) |
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.
In the Customize the Ribbon category, in the Main Tabs list, select the Developer check box, and then click Save.
Add a button (Form control)
Follow these steps:
On the Developer tab, click Button .
Click the worksheet location where you want the upper-left corner of the button to appear. The Assign Macro popup window appears.
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)
On the Developer tab, click Button .
Click the worksheet location where you want the upper-left corner of the command button to appear.
Note: If you have already inserted a button, you can right-click on it, and select Assign Macro.
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.
Click Design Mode to ensure that design mode is off, then close the Visual Basic Editor window.
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, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.
See Also
When I click the VBA buttons in Excel 2007 I go to edit mode. But when I'm trying to modify them inExcel 2011 - I click it, and it clicks! But how to switch command mode and edit mode?
Edit Macro Button In Excel Template
2 Answers
Excel 2011 doesn't support ActiveX Controls. It only supports Form Controls. And to access the code area you may follow any of the following steps
Right Click on the form button, then on
Assign Macro. In the next dialog box, Click onNewPress fn+ALT+F11 to access the VBA Editor. Create a sub in a module and then link it to the form button using
Assign Macroas mentioned aboveClick on the Excel Menu next to the Apple Icon, and then click on Preferences. Click on
RibboninSharing and Privacyand then select the Developer checkbox.
ScreenShot:
Siddharth RoutSiddharth RoutYou need to first make sure you have the developer tools added to the ribbon: Select File, Options, then select Customize Ribbon and make sure the 'Developer' tab is selected. Click Ok.Then you can go to the Developer ribbon tab and select 'Design Mode' This will let you edit things and will change the button behaviour so that double clicking will open associated code.