This was a request from a reader who wanted to know how to add and use a command button that was controlled from a worksheet in Excel.

Lets assume we will use a worksheet called “Sheet1”.  If you don’t have the Visual Basic tool bar open then first of all let’s open that so we can see it.  Go to View > Toolbars and then select Visual Basic.  This will open the tool bar, and you can then drag this to the menu at the top.  Next click the Control Toolbox button.  This is represented by a small hammer and screwdriver icon (as shown in the image to the right).  Clicking this will activate the Control Toolbox that will allow us to insert a command button into the worksheet.  The Control Toolbox can also be dragged and inserted into the menu at the top.

Inserting the Command Button

OK, third of the way now.  On the Control Toolbox is a small icon that looks like a grey
rectangle.  If you hover over it with the mouse a tooltip will appear that says Command Button.  Clicking this will cause a small box to appear (it means you are in Design Mode), and you will notice that the mouse cursor has changed to crosshairs.  All you do, is click with the left mouse button anywhere on the worksheet and draw a rectangle with it.  In my example I have made the button nice and big. This will then place your command button, and don’t worry as it can be moved from this position very easily simply by clicking and dragging with the mouse whilst in design mode.  You can click in out of design mode by clicking on the button represented by a triangle,ruler and pencil icon.  So, now there is a command button on your worksheet.  If you come out of design mode you will see that you can click button, but it doesn’t do anything!

Getting the Command Button to do Something

Finally, you need to give the command button something to do when you click it!  For this example, I will get to display a message box saying “Hello World”.  If you have come out of design mode, click the back into it by clicking on the icon with a triangle, ruler, and pencil displayed.  Once you have done this, double click on the command button and this will open a new window, which will be the Visual Basic Environment (VBE), it is from here that code is run from.  In the right hand pane of the VBE there is some text already entered for you.  It should say something like

Private Sub CommandButton1_Click()

End Sub

click between these two lines in the blank space.  And type the following:
MsgBox "Hello World"
This tells the code to show a messge box that will say “Hello World”.  Now you have typed that, click back into the worksheet and then come out of design mode.  You can also tell if you are in design mode by hovering over the command button.  If you are in design mode, the mouse will turn to cross hairs as you hover over the button.

Now that you have come out of design mode click the button, and you should get a message box pop up!  So now, you could put your code where you had written
MsgBox "Hello World"
and this should run whenever you click the button.