You can automate a form control or an ActiveX control by doing the following:
-
Perform an action or operation when a user clicks a form control by assigning a macro to it.
-
Run Microsoft Visual Basic for Applications (VBA) code to process any events that occur when a user interacts with an ActiveX control.
For more information about how to create macros, see Create or delete a macro.
Add or edit a macro for a form control
-
Right-click the control, and then click Assign Macro.
The Assign Macros dialog box appears.
-
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.
-
-
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, 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.
-
Add or edit a macro for an ActiveX control
-
If the Developer tab is not available, display it.
In Excel 2016, 2013, 2010:
-
Click File > Options > Customize Ribbon.
-
Under Customize Ribbon, select the Developer check box, and then click OK.
In Excel 2007:
-
Click the Microsoft Office Button
-
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.
-
-
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
-
Select the control.
For more information, see Select or deselect controls on a worksheet.
-
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.
-
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.
-
After you finish writing the macro, click Close and Return to Microsoft Excel on the File menu in the Visual Basic Editor.
-
After you finish designing the control, on the Developer tab, in the Controls group, turn off Design Mode
