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.
You can click here to download the example file used on this page.
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:
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:
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:
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:
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:
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:
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:
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 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 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 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:
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:
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.