Excel Visual Basic for Applications online training

This page has 0 threads | Add post

Finding our tutorials useful? Please consider helping us to share more!

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:

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.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Drawing a Shape

Formatting a Shape

Assigning a Subroutine to the Shape

Editing the Shape

Head office

London

Manchester