VBA Tutorial P5 – How to connect a macro with a button?

Welcome back! I have a new post for you, this time regarding buttons.

As you probably remember from my post regarding how to create a simple vba macro, you can run a macro by going to View -> Macros -> View Macros and clicking on Run for the macro selected. Well, that might be hard to explain and to use by the people that need a two-part tutorial regarding how to open a computer.

Now that you know a thing or two regarding how to create macros, it should be a piece of cake for you to connect a macro with a button. And there are two ways of doing it.

1. Create a shape and assign a button to it

Yes, you can assign macros to shapes. To do that, go to Insert -> Shapes and choose a shape.

Image 1

Then draw that shape wherever you want in the workbook and as big as you want. I’m going to choose a rectangle. To add some text to that shape, right-click on it and select Edit Text. You can change the style of the text from the top-right menu, just like you change the style of every text (if you already wrote the text, you need to select it first before applying the style!).

Image 2

As a last step, for assigning the macro, right-click on the shape and select Assign Macro…

Image 3

From the list with all the macros (if you have more than one), select the macro you want to assign to that button and then click OK (make sure you select <This Workbook> to really assign the macro you want and not a macro from another open workbook).

Image 4

2. Create a command button and assign a button to it

To create a button, you firstly need to have access to the Developer tab. If you don’t see it in the ribbon (e.g. the main bar of Excel), then you need to add it first. To do this, click on File and then on Options. In the newly opened window, select Customize Ribbon, then check the box Developer in the right-hand side and click OK.

Image 5

Now you should be able to see the Developer option in the main ribbon.

Image 6

Click on Developer, then click on Insert and select Button.

Image 7

You can now draw the button just as you did with the shape to make it as big as you want. After you draw it, you will be able to select the macro. The procedure is the same as above (e.g. from the list with all the macros (if you have more than one), select the macro you want to assign to that button and then click OK (make sure you select <This Workbook> to really assign the macro you want and not a macro from another open workbook).

To edit the default text, right-click on the button and click on Edit Text.

That’s it! I hope you enjoyed it.

Next post -> VBA Tutorial P6 – What is a UserForm and how can you create one?

Other posts in this series -> VBA Tutorial for Beginners


PS: If you want to support this website, you can do it through the Show Your Support page!

Leave a Reply