Assign a Macro to a Button

In order to assign a macro to a button, first, we need to create a macro. If you don’t know how to do this, check my lesson about recording macros.

After we saved our macro, we can create a button.

Place a button on a worksheet

Open the Developer tab from the Ribbon.

By default, this tab is not present in the ribbon. Read this lesson to learn how you can add it.

Go to DEVELOPER >> Controls and click the Insert button.

From the contextual menu select Button (Form Control).

When you place the button on the worksheet a new window will appear.

Here, you can assign a macro. I used the macro that fills cells with color.

Place a button in Quick Access Toolbar

We can also place our macro in the Quick Access Toolbar. But before we start recording it, we need to choose the Personal Macro Workbook option.

After we choose this option and record a macro that fills cells, we will be able to place it in Quick Access Toolbar.

Click Customize Quick Access Toolbar.

Then click More Commands.

From Choose commands from select PERSONAL.XLSB!FillCell and move it to the right side.

You can click the Modify… button to change the icon of the button.

Click OK. Now, our button will be placed in Quick Access Toolbar.

Each time you press it, the macro is executed. In our example, it will be cell fill.

Unhide command

If you go to DEVELOPER >> Code >> Macros and try to delete the macro, you won’t be able to do this and the following message will appear.

Close the window and go to VIEW >> Window >> Unhide.

Click OK.

Now you will be able to delete the macro.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba