Let us demonstrate the use of a CommandButton control from the ActiveX Controls group on a worksheet. Suppose that when the CommandButton control, which we place on Sheet1, is clicked, it activates Sheet2.
- Start Microsoft Office Excel 2010 and make sure the cell pointer is on Sheet1.
- Go to the Developer tab, and in the Controls group, click the Insert dropdown button.
- Click the CommandButton control from the ActiveX Controls group and move the pointer directly onto the worksheet; the pointer will change to a thin cross.
- Choose the location on the worksheet, press and hold the left mouse button, draw the button to the desired size, and then release the mouse button.
Note that after the CommandButton control appears on the worksheet, the Design Mode button becomes active (enabled). On the surface of the first CommandButton you create, the label CommandButton1 will appear automatically.
If you now create a second CommandButton, the label CommandButton2 will appear on it, and so on.
NOTE
Like any graphical object, you can draw the button while holding the <Shift> key to make it square, or while holding the <Alt> key to snap it to the worksheet grid. You can set the button’s size using the sizing handles, and move it using the move handle.
Right-click the created button and from the context menu, select Properties to open the Properties window.
The CommandButton control is an object, meaning that, like any object, it has properties, methods, and events. The label displayed on the surface of the CommandButton is set using the Caption property. Additionally, the CommandButton has a Name property, which identifies it as an object in code. In this case, it is also CommandButton1.
Change the Caption property in the Properties window from CommandButton1 to Sheet2 (to indicate that this button will activate Sheet2). If you like, experiment with other CommandButton properties such as BackColor, Font, ForeColor, and Shadow. Close the Properties window.
NOTE
Another way to open the Properties window is to select the desired control with the mouse and then, on the Developer tab, in the Controls group, click the Properties button.
Now, let’s create the procedure that handles the button click event. When this event is triggered, Sheet2 should be activated.
Double-click the button you created (remember that Design Mode is enabled in the Controls group on the Developer tab). The VBA editor opens with the worksheet module active (in this case, Sheet1), and the first and last lines of the Click event procedure are automatically added .
First and last lines of the “button click” event procedure. Worksheet module for Sheet1
Private Sub CommandButton1_Click() End Sub
Open the file in Windows where you previously created the macro Activate_Sheet; make sure the required module is visible in the Project – VBAProject window. Copy the following instruction from the macro via the clipboard :
Sheets(« Sheet2 »).Select
Paste it into the button click event procedure . Of course, you could type this instruction manually, but doing so is slower and prone to typos.
“Button click” event procedure that activates Sheet2. Worksheet module for Sheet1
Private Sub CommandButton1_Click()
Sheets("Sheet2").Select
End Sub
- Return to Sheet1. The created button will only handle the event (being clicked) after exiting Design Mode. To do this, turn off Design Mode by clicking the Design Mode button in the Controls group on the Developer tab.
- Test the button you created: click it, and if everything is done correctly, Sheet2 will be activated.
As an exercise to reinforce the material, we recommend creating a macro that adds a new worksheet to the Excel workbook and sets the cell pointer to Sheet1. Then, link the created procedure to another button, also placed on Sheet1.