Adding a Button to an Excel Spreadsheet

In the previous lesson, you created a simple Sub in the code window. In this lesson, you'll activate that Sub from a button on a spreasheet.

 

At the top of the VBA Editor, locate the Excel icon, just under the File menu:

Returning to Excel from the VBA Editor

Click this icon to return to your spreadsheet. We'll now place a button control on the spreadsheet.

Locate the Controls panel on the Developer toolbar, and then click the Insert item. From the Insert menu, click the first item, which is a button:

The button item on the Form Controls menu

Now move your mouse to your spreadsheet. Hold down your left mouse button somewhere on the F column (F3 will do). Keep it held down and draw out a rectangular button. Let go of the left mouse button when your cursor is on H4

As soon as you let go of the left mouse button you'll see the Assign Macro dialogue box appear:

The Assign Macro dialogue box

Select your Macro from the list and click OK. The button on your spreadsheet should now look like this:

A button drawn on an Excel spreadsheet

You can edit the text on a button quite easily. Right click the button to see a menu appear. From the menu, select Edit Text:

Editting the text of the command button

When you select Edit Text, a cursor will appear at the start of the text. Use the arrow keys on your keyboard to move the cursor to the end of the line. Delete the text Button 1 and type Range("A1:D6").Select instead (If you accidentally click away from the button, click on it again with right mouse button and not the left button. This will select your button again.):

A renamed command button

Click away from the button to exit edit mode and you'll see the sizing handles disappear.

You can now test your button out. Give it a click and you'll see the cells A1 to D6 highlighted:

A range of spreadsheet cells selected when a button is clicked

Congratulations! You have now written Excel VBA code to select a range of cells on a spreadsheet. And all with the click of a button!

 

Now return to the Visual Basic editor (From the Developer toolbar, click Visual Basic on the Code panel.) Type a single quote before your Range line. The line should turn green:

An Excel VBA comment

The reason it turns green is because a single quote is used for comments. When the line is commented out it means Visual Basic will no longer see it as code, so doesn't do anything with it. You can add comments to remind yourself what your code does, as in the image below:

Commenting code to make it more readable

Adding comments to your code is a good habit to get in to. Especially when you come back to your code after a few weeks or so. If you haven't added comments you may not quite understand what it was you were trying to do.

Back to the Range code, though. Notice how we referred to the range of cells A1 to D6:

Range("A1:D6")

Another way to refer to the same range is like this:

Range("A1", "D6").Select

This time, the start cell A1 and the end cell D6 are enclosed with double quotes. In between the two we have a comma.

Both the examples above do the same thing: they first select the top left cell of the range, and then the bottom right cell of the range. It's entirely up to you which you use. But with the second version you can use something called the ActiveCell.

 

ActiveCell

Instead of typing the name of a cell you can also refer to which cell on your spreadsheet is currently highlighted. The currently highlighted cell is called the ActiveCell (no spaces and with capital letters for the "A" and "C"). You can use this in your code. Let's see how it works.

After the End Sub of your first Subroutine, add the following:

Sub ActiveCell_Example()

Press the enter key on your keyboard to let the VB editor add the End Sub for you. Now add the following line between the Sub and End Sub of your code:

Range(ActiveCell, "D6").Select

Your coding window will then look like this:

ActiveCell coding example

So the top left cell we want to select is the ActiveCell, which is whatever cell you clicked in on your spreadsheet. The bottom right cell we want to select is D6.

Click the icon to return to your Excel spreadsheet. Now draw another button on your form, just below the first one. You should see the Assign Macro dialogue box appear again:

Selecting an item from the Assign Macro dialogue box

Select your new Macro (your Sub) from the list and click OK.

When you get back to your spreadsheet, edit the text of the button again. Type ActiveCell as the text. When you have finished editing the text, click away. Click inside another cell on your spreadsheet, cell A2 for example. Now click your button. You should see the cells A2 to D6 highlighted:

An Excel spreadsheet with two command buttons

Click inside any other cell on your spreadsheet and click the button again. The cells from your active cell to D6 will be selected.

 

In the next part of this tutorial, we'll take a look at the Offset property. So save you work before moving on.