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 a 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.
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.
Now you will be able to delete the macro.