Want to become an expert in VBA? So this is the right place for you. This blog mainly focus on teaching how to apply Visual Basic for Microsoft Excel. So improve the functionality of your excel workbooks with the aid of this blog. Also ask any questions you have regarding MS Excel and applying VBA. We are happy to assist you.

Add a Command Button to an Excel Sheet


Today I am going to explain you how to insert a command button to an Excel sheet. First click on the developer tab.


Then click on the insert.


You will notice that there are two types of buttons available in VBA. One is form control buttons and other one is ActiveX control command buttons. There are few differences between these two types of buttons. Form control buttons compatible with any Microsoft Excel version. Also they are compatible with Mac OS too. But ActiveX control command buttons are not compatible with Mac OS. Also it is different how you can write codes for each of these button types. You can easily write codes for ActiveX control command buttons by double clicking on the button. But if you need to write the code for the form control button, first you need to develop the code on a separate module. Then you can right click on the form control button and assign macro.

So if you select the ActiveX control command button your mouse pointer will changed to + symbol. Then click on somewhere in the Excel sheet and drag the mouse to create a rectangle. 


Release the left mouse button. Then command button will be created like this.  


Next I will explain you how to change the name of the command button or any other property. First you need to right click on command button. Then select properties. 


You will see the properties window like this. 


Now you can change properties like name, caption, background colour and font colour etc. from this window. And when you name the controls, it is a good practice to follow the conventional naming system. For an example if you want to name this command button as “Click” then you need to add cmd before the name. So the name will be “cmdClick”.

Next I will explain you how to add VBA code to ActiveX control command button. As an example let's write a code to type from 1 to 100,000 in column A of the Excel sheet. First click on the developer tab and select the Design mode.



Now double click on the command button. Then VBA editor will be opened like this.



So you can add the below code to the command button.



Exit the design mode by clicking on it again. Then click the command button to run the program. You will get following result.



Contact Form

Name

Email *

Message *

Popular Posts