- The VBA/Excel command button is an ActiveX control.
- The command button is used to perform a certain task as a user clicks on it.
- You may run a macro or execute the procedure, trigger message box, etc. as the user clicks the button.
- Placing a command button in an Excel sheet is a matter of simple “three-click” work.
If you are working in VBA for Excel then you might already add the Developer tab in the ribbon. If not, you may add this by:
Once the Developer tab is added, you may see the insert group there as shown in the image below:
In the developer tab, click on the “Insert” button group:
Under the ActiveX controls heading, locate the command button as shown below:
This should be the first one towards the left.
Click on the command button and now click the mouse somewhere on the Sheet where you want to add the button.
Drag the + sign and make the button as big as you want.
That’s it!
To make the button functional, you need to add some code to perform a certain task.
To add the code, you may double-click the button that you just added.
It should take you to the VBA code editor.
Let us perform some action as a user clicks on the button.
Add a button to the Excel sheet:
Ensure that “Design Mode” is selected as shown below:
Double-click on the button that should lead you to the VBA editor as shown below:
Write this line of code:
Save this code and come back to the Excel sheet.
Unselect the “Design Mode” as shown below:
Click on the button and it should assign “testing” to the B3 cell as shown below:
An example of triggering a message box
In this example, we attached a message box to the button. As you click on the button, the message box will appear with a simple message and an OK button.
The following line of code is written in the button click event:
In the VBA code editor:
As you click the button, the following message box appears:
In the first example, you saw as we added the button, it has the following default caption (display text): CommandButton1
You may change the button caption easily.
Right-click on the button and you will see:
“CommandButton Object” option
Click on Edit option as shown below
The prompt will be at the button label – write the text that you want to appear for the button.
Right-click on the button and you will see the “Properties” option there.
Clicking it will open the Properties window. There locate “caption” and change the text as you want. See the image below for the steps:
