Assign a macro to a control on a worksheet

For forms and ActiveX controls , you can do the following:

  • For a form control, enable a user action by assigning a macro to the control.

  • For an ActiveX control, run Microsoft Visual Basic for Applications (VBA) code to process any events that occur when a user interacts with the control.

What do you want to do?

Add or edit a macro for a form control

Add or edit a macro for an ActiveX control

Add or edit a macro for a form control

  1. Right-click the control, and then click Assign Macro.

    The Assign Macros dialog box appears.

  2. To specify the location of an existing macro, select where the macro is located in the Macros in box by doing one of the following:

    • To search for the macro in any workbook that is open, select All Open Workbooks.

      Note: If the macro that you want to assign to the control is in a separate workbook, open that workbook first so that it will be available in the Macros in list box.

    • To limit the search for the macro to the workbook that contains the current worksheet, select This Workbook.

    • To limit the search for the macro to a specific workbook, select that workbook from the list of available open workbook names.

  3. Do one of the following:

    Assign a macro    Do one of the following:

    • Record a new macro    Click Record, and when you finish recording the macro, on the Developer tab, in the Code group, click Stop Recording Button image .

    • Assign an existing macro    Double-click a macro in the list or enter its name in the Macro name box.

    • Create a new macro    Click New and then, in the Visual Basic Editor, write a new macro.

      For more information about how to write macros, see Visual Basic Help.

      Modify an assigned macro    Do one of the following:

    • Edit the assigned macro    Click the name of the macro in the Macro Name box, and then click Edit.

    • Assign a different existing macro    Double-click a macro in the list or enter its name in the Macro name box.

Top of Page

Add or edit a macro for an ActiveX control

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

    Display the Developer tab

    1. Click the File tab, click Options, and then click Customize Ribbon.

    2. Under Main Tabs, select the Developer check box, and then click OK.

  2. To edit the ActiveX control, make sure that you are in design mode. On the Developer tab, in the Controls group, click Design Mode Button image .

  3. Select the control.

  4. On the Developer tab, in the Controls group, click View Code Button image .

    Note: You can also edit an existing macro by right-clicking the control, and then clicking View Code.

  5. In the Visual Basic Editor, write a new macro or change the existing macro.

    For more information about how to write macros, see Visual Basic Help.

  6. After you finish writing the macro, on the File menu in the Visual Basic Editor, click Close and Return to Microsoft Excel.

  7. After you finish designing the control, on the Developer tab, in the Controls group, turn off Design Mode Button image .

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×