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.1 - Worksheet Buttons

So far, you've mainly been running your code as a developer from within the VBE. If you're developing code for others to use, it's likely that they'll demand a more convenient way to execute the code you've provided them with. One way to satisfy this demand is to create a basic menu system by adding buttons to a worksheet.

A set of sensibly-labelled buttons makes it much easier for your users to run the code you've written for them.

Files Needed

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

Completed Code

You can click here to download the finished example.

Download, extract and open the file from the Files Needed section above. In the VBE, open Module1 to see the three subroutines it contains:

Although you can run these procedures from the VBE, it's more convenient for the end-user to have buttons in the worksheet which they can click.

Drawing a Button

To begin adding a button, return to the Excel window and choose Developer | Insert from the ribbon:

Click Insert to see a drop down list of controls you could draw. Choose the option in the top left corner of the Form Controls section of the list to draw a Button.

You can now choose where to draw your button on the worksheet:

You can click once to drop a button with a default size on the worksheet, or click and drag to draw a button of any size.

When you release the mouse button, you'll be presented with a list of subroutines that you could attach to the button you have drawn:

Choose the subroutine you want to attach to the button then click OK.

Once you've clicked OK the button will appear on the worksheet:

The button appears with some default text.

You can change the text in the button by selecting it and typing your own label:

A sensible label will help the user to understand what will happen when they click the button.

When you've finished editing the text you can simply click away from the button to accept your changes:

Click on a cell in the worksheet to confirm the changes you've made to the button.

You can now use the button to run your code by clicking on it:

Click the button to run the subroutine you attached to it.

Editing a Button

If you want to make changes to a button that you've created, you can't simply click on it to select it as this will run the code attached to it. The simplest way to start editing a button is to right-click on it:

Right-clicking a button allows you to, for example, alter its label by choosing Edit Text, or change the subroutine assigned to it by choosing Assign Macro...

While you're editing a button, you can change its position by clicking and dragging it with the mouse:

Click and drag the button to move it to a new location.

You can resize the button by clicking and dragging one of the eight resizing handles:

Click and drag any of the white circles around the border of the button to resize it.

If you hold the Alt key while moving or resizing a button, you'll find that it snaps to the borders of cells on the worksheet.

Perhaps the only downside to using the standard buttons described here is their lack of formatting features. You can see the available formatting options in the Format Control dialog box:

Right-click on the button and choose Format Control... to open the dialog box.

You can use the dialog box to make simple formatting changes to the button:

You can change several font formatting properties using this page of the dialog box. Click OK to confirm your changes.

If you want a more exciting-looking menu you may wish to consider using Excel's drawing shapes instead. See a later part of this lesson to learn how!

To practise using buttons to run your code, try creating a button for each of the remaining two subroutines in the sample file. Your final menu worksheet could resemble the one shown below:

Test each of your buttons to make sure they behave as expected.

This page has 0 threads

Add a new post

MODULES

LESSONS

TOPICS

Files Needed

Completed Code

Drawing a Button

Editing a Button

Head office

London

Manchester