Add a button and assign a macro to it in a worksheet

You can use a button (a Form control) or a command button (an ActiveX control) to run a macro that performs an action when a user clicks it.

What do you want to do?

Learn about the button and the command button

Add a button (Form control)

Add a command button (ActiveX control)

Learn about the button and the command button

Both a Form control button and an ActiveX control command button are also referred to as a push button. You might use a button or a command button to automate the printing of a worksheet, the filtering of data, or the calculation of 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)

Example of a Forms toolbar button control

Command button (ActiveX control)

Example of an ActiveX command button control

Top of Page

Add a button (Form control)

  1. If the Developer tab is not available, display it.

    Display the Developer tab

    1. Click the Microsoft Office Button Office button image, and then click Excel Options.

    2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

      Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  2. On the Developer tab, in the Controls group, click Insert, and then under Form Controls, click Button Button image.

    controls group

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

  4. Assign a macro to the button.

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

Top of Page

Add a command button (ActiveX control)

  1. If the Developer tab is not available, display it.

    Display the Developer tab

    1. Click the Microsoft Office Button Office button image, and then click Excel Options.

    2. In the Popular category, under Top options for working with Excel, select the Show Developer tab in the Ribbon check box, and then click OK.

      Note   The Ribbon is a component of the Microsoft Office Fluent user interface.

  2. On the Developer tab, in the Controls group, click Insert, and then under ActiveX Controls, click Command Button Button image.

    controls group

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

  4. In the Controls group, click View Code.
    This starts the Visual Basic Editor. Make sure that Click is selected in the dropdown list on the right. The sub procedure called CommandButton1_Click, as shown in the following picture, runs two macros when the button is clicked: SelectC15 and HelloMessage.

    A subprocedure in the Visual Basic Editor

  5. In the sub procedure for the command button, do one of the following:

    • Type 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 typing the macro names on separate lines inside the sub procedure.

    • Type your own VBA code.

      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.)

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)

Top of Page

Applies To: Excel 2007



Was this information helpful?

Yes No

How can we improve it?

255 characters remaining

To protect your privacy, please do not include contact information in your feedback. Review our privacy policy.

Thank you for your feedback!

Support resources

Change language