Adding a button to run a program

You need an easy way to start programs that you write. One way is to add a button to your spreadsheet.

Open a new workbook. Go to Sheet1. Go to the Developer tab. Click Insert, and choose Command button from the ActiveX controls:

Adding a button

(Use the ActiveX button, not the Form button.)

Click somewhere in the worksheet to place the button.

Notice that the Design mode button is active. When you’re in design mode and you click on the button, you select it, so you can change what the button does. When design mode is off and you click on the button, you’re telling the button to run any code that’s tied to it.

With the command button selected, click the Properties link on the Developer tab. The properties window will open:

Properties

Properties are attributes of the button that affect how it looks and acts. Change the Caption property to Click me. Change the name to cmdClickMe:

Setting the caption and name

OK, we’ve got the button looking right. Now go back to VBE (ALT+F11). Select cmdClickMe and Click in the code window:

Ready for code

cmdClickMe_Click() is a subroutine. It’s a block of code that Excel will run whenever someone clicks the button.

Put some code in the subroutine. Try this:

MsgBox("OK, I admit it, I'm a cow.")

It should look like:

Code

Notice that the line is indented. Press the Tab key to indent.

Go back to the worksheet. Turn off design mode, with the Design Mode button in ribbon.

Click the command button you made. You should see a message about having cow nature.

Now pick a cell in your worksheet that’s near the button. For me, cell G3 is to the right of the button.

G3

If you put your button on G3, choose another cell.

Cell G3 is in row 3, column 7. Change the code in the click sub:

Cells(3, 7) = "OK, I admit it, I'm a cow."

So you have:

Code

Adjust the 3 and 7 to refer to whatever cell you want.

Click the button now, and see what happens on the worksheet.

Let’s change the background color of the cell. Add this line to your sub:

Cells(3, 7).Interior.Color = vbRed

Try it.

So, that’s how you can have a button run some code.

You don’t have the make the button show “Click me”. You could make it show “Run”, or whatever you want. If you make the button’s caption “Run”, make the button’s name cmdRun to match. Your code will be in the Sub cmdRun_click().