Show All

Hide All

For forms and ActiveX controls (control: A graphical user interface object, such as a text box, check box, scroll bar, or command button, that lets users control the program. You use controls to display data or choices, perform an action, or make the user interface easier to read.) , you can do the following:

• Perform an action or operation when a user clicks a form control by assigning a macro (macro: An action or a set of actions that you can use to automate tasks. Macros are recorded in the Visual Basic for Applications programming language.) to it.
• Run Microsoft Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) code to process any events that occur when a user interacts with an ActiveX control.

For more information about how to create and edit macros, see the Help section, Macros.

• 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 .
   • 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 (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), write a new macro.

     For more information about how to write macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft 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 Microsoft Office Button , 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. 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 .
3. Select the control.

   For more information, see Select or deselect controls on a worksheet.

4. on the Developer tab, in the Controls group, click View Code .

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

5. In the Visual Basic Editor (Visual Basic Editor: An environment in which you write new and edit existing Visual Basic for Applications code and procedures. The Visual Basic Editor contains a complete debugging toolset for finding syntax, run-time, and logic problems in your code.), write a new macro or change the existing macro.

   For more information about how to write macros, see Visual Basic Help (Microsoft Visual Basic Help: To get help for Visual Basic in Excel, point to Macro on the Tools menu, and then click Visual Basic Editor. On the Help menu, click Microsoft Visual Basic Help.).

6. After you finish writing the macro, click Close and Return to Microsoft Excel on the File menu in the Visual Basic Editor.
7. After you finish designing the control, on the Developer tab, in the Controls group, turn off Design Mode .

 Top of Page