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
(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 are attributes of the button that affect how it looks and acts. Change the
Caption property to
Click me. Change the name to
OK, we’ve got the button looking right. Now go back to VBE (ALT+F11). Select
Click in the code window:
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:
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.
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:
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
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