Module 1 - Getting Started in VBA
Lesson 1.3 - Basic User Interfaces
Topic 1.3.3 - Shapes as Buttons

In an earlier part of this lesson you saw how to create boring, grey buttons which you could use to run your subroutines.  If you wish that you could make the appearance of the buttons more interesting, you may be interested to learn that you can attach a subroutine to a variety of shapes on a worksheet:


You can attach subroutines to any object that you can draw on a worksheet, including shapes, clipart and inserted pictures.


Files Needed

You can click here to download the example file used on this page.

Completed Code

You can click here to download the completed example.

Drawing a Shape

You can draw a variety of shapes on a worksheet and attach the subroutines you have written to them.  To insert a shape, choose Insert | Shapes from the Excel ribbon:

Choose shape

There are lots of shapes to choose from. Here we're using a relatively simple rectangle with rounded corners.


Once you've selected a shape to draw you can click or click and drag on the worksheet to draw it:

Draw shape

Here we're clicking and dragging so that we can set the dimensions of the shape when we draw it.


When you release the mouse button, your shape will be drawn on the worksheet:

Shape drawn

The shape will appear selected on the worksheet.


Formatting a Shape

The main advantage of using a shape rather than a formal button is the wealth of formatting options available to a shape:

Format options

When the shape is selected you can visit the Format tab of the ribbon to see its formatting options.

You can quickly apply a range of formatting settings by choosing one of the preset options as shown above.  You can add text to a shape when it's selected simply by starting to type:

Type text

Typing text while the shape is selected will automatically add the text to the shape.


Once you have added text to the shape you have even more options to format it:

Format text

Here we've altered the text alignment and added a reflection effect.


Assigning a Subroutine to the Shape

Once you've made your shape look beautiful you can assign a subroutine to it.  Start by right-clicking on the shape:

Right click

From the context menu that appears, select the Assign Macro... option


You can then choose which of the available subroutines you want to attach to the shape:

Select macro

Select a procedure from the list and click OK to assign it to the shape.

To finish assigning the subroutine to the shape, click away from it in the worksheet:

Click away

Click on a cell in the worksheet to finish assigning the procedure to the shape.


You can now run the subroutine by clicking on the shape:

Click shape

Click the shape to run the subroutine.


Editing the Shape

If you need to make changes to the shape after you've assigned a subroutine to it, you can't simply click on the shape to select it.  To start editing the shape, right-click on it to display its contect menu:

Context menu

You can make changes to the shape using either the context menu or the ribbon. Here we're choosing to assign a different subroutine to the shape.


To practise using shapes to run your code, try adding a new shape for each of the two remaining subroutines in the sample workbook.  Your final workbook could resemble the one shown below:

More shapes

The multi-coloured circle is an inserted picture rather than a drawn shape.  You can attach a procedure to a picture in the same way as for a shape.


Once you're happy that your buttons work, save and close the workbook.

This page has 0 threads Add post