VBA MODULE 13
Form Control & ActiveX Control
These controls are very helpful when we want to make Excel more interactive and user friendly
We can create command buttons, text boxes, list boxes, check boxes etc.
These controls can be seen on Excel by following two steps given below
- Click on the developer tab
- Click on Insert Drop down button
In the given figure you can see the Developer tab, Insert drop down with Form and ActiveX controls.
Difference between Form and ActiveX control
ACTIVEX CONTROL |
FORM CONTROL |
ActiveX controls are better in appearance as they have more formatting options available than form controls |
Form controls don’t have many formatting options hence its appearance is not too appealing |
ActiveX controls trigger events |
Form controls call macros assigned to them |
Sometimes events for ActiveX controls are triggered on its own when some other event is triggered. |
Form controls are simple and does not have such problems |
At times, on opening the workbook ActiveX controls are not recognized and give errors. |
Form controls never give error hence they are more reliable. |
Adding ActiveX control Command button
- Click on Insert drop down
- Click on Command Button (ActiveX Control) as shown in the image below
- As soon as you click on the button Design Mode will be highlighted and you can draw the button on the sheet using your mouse strokes
Formatting ActiveX control Command button
After drawing the button on the sheet we can right click on it and the menu appears which is shown below.
Now select properties option and a new window will appear which is shown below. This window has several options to format your button.
Commonly used properties are Name property and Caption Property, highlighted in red box. But there are other useful properties as well to make your button attractive and appealing like font, foreColor, Picture etc. You can change these properties for better formatting.
You can set the Caption property to any name of your choice and this name will appear on your button
For example I changed caption property to “My Button” and it will appear on the button as shown in picture
Now set the Name property of the above button as My_Button, then double click on the button or right click on the button and select option "View Code", you will see the VBA code window showing the sub procedure named as My_Button_Click. You can put your code in this procedure to program this button and get it working.
Remember whenever you want to change any property of your button you must turn on the design mode first by clicking on the design mode given in developers tab. Turn off the design mode when formatting is over.
Let’s make this button working with the simple code shown below in the picture.
After putting the code above go to your sheet where you made the button, turn off the design mode and then click on the button, you will see the result as shown in the below image
