How to Insert a Button in Excel

Excel provides the option for users to add macro-enabled buttons to their worksheets, allowing them to execute macros with just a single click. These buttons can be used to perform a variety of tasks, including data filtering, selection, printing, formula and calculation execution.

Adding buttons and attaching macros to them is a straightforward process in Excel, with several methods available to users. Below, we have outlined some quick and easy techniques that you can use to add macro buttons to your Excel worksheets.

How to Add Macro Buttons in Excel Using Shapes

Creating buttons in Excel using shapes provides users with more formatting options compared to those created using Control buttons or ActiveX buttons. By using shape buttons, users can change the design, color, font, and style of the button.

To add a shape button, follow these steps:

1. Go to the “Insert” tab and click on the “Illustrations” icon.
2. Select the “Shapes” option and choose any rectangle button.
3. Use the mouse to draw the rectangular button onto the worksheet.
4. Double-click on the button to enter text.
5. Format the button using the options available in the “Shape Format” tab. You can change the font style, font color, button color, button effects, and more.

To edit the text, add a hyperlink, or add a macro to the button, right-click on the button to open the pop-up menu. From here, select the desired option.

To add a macro to the button, select the “Assign Macro” option. This will open the “Assign Macro” dialogue box. Choose the macro you want to assign and click OK.

Once a macro is assigned, the button becomes macro-enabled, and you can execute the assigned macro by clicking on the button.

To prevent the button from moving or resizing with the cells, right-click on the button and select “Format Shape”. Then choose the “Don’t move or size with cells” option.

Adding Macro Buttons Using Form Controls

To add macro buttons using form controls in Excel, follow these steps:

1. Go to the “Developer” tab on the ribbon and click on the “Insert” icon under the “Control” group.
2. From the “Form Controls” menu, select the first button option and draw a button on the worksheet.
3. Type or select the macro name from the “Assign Macro” dialogue box and click OK. If you don’t have a macro created yet, click cancel and add the macro at a later stage.
4. To format the button’s font size, style, color, etc., select the “Format Control” option.
5. Clicking on “Format Control” opens the “Format Control” window, allowing you to format the button’s font.
6. To freeze the button movement, select the “Properties” tab, and choose the “Don’t move or size with cells” option. Finally, click OK.

Add Macro Buttons Using ActiveX Controls

To add a macro to an ActiveX Control button in Excel, follow these steps:

1. Go to the “Developer” tab on the ribbon and click on the “Insert” icon under the “Control” group.
2. Select the first button option from the “ActiveX Controls” menu and draw a button on the worksheet.
3. Click on the button to select it and then right-click to bring up the context menu.
4. From the context menu, select “View Code” to launch the VBA editor.
5. In the VBA editor, select the “CommandButton1” on the subprocedure and choose the “Click” option from the drop-down list on the right side of the editor.
6. Now, enter the macro code that you want to associate with the button.
7. Save the changes and close the VBA editor.

To format the button, you can right-click on the button and select “Properties” to open the properties window. From here, you can customize the button’s appearance, such as its font size, style, color, etc.

To freeze the button movement, you can select the “Properties” tab and choose the option “Don’t move or size with cells” and then click OK.