ActiveX Controls

ActiveX Controls are one of the most used Excel Controls to automate applications with Excel VBA. It has the same controls, unlike Form Controls (Command Button, combo box, checkbox, etc.), but it supports more features and usability to perform multiple operations in the Excel sheet. For the same macro, ActiveX control and Form control will result in different outputs. In ActiveX control, the flow of the program is managed in the VBA editor window (where you can easily debug and run the code with ALT + F11). The user can add multiple ActiveX controls as per his requirement, whereas in Form Control the usability is limited. These controls show up as objects that the user can use in the VBA code and thus used in complex scripts. You can use these controls for setting the layout and orientation for your excel sheet, fetching out the commonly used data, customizing the data, etc.,

Advantages of ActiveX Control

It is customizable and can be easily created.
ActiveX Control allows flexible design and is used to advanced scripts.
You can easily write up the code in VBA Editor and assign that code to your ActiveX control.
It is loaded into the memory separately. Hence, the program will run smoothly.
The user can access the flow of the control programmatically.
In this, the user can activate multiple controls.
ActiveX controls are used in day to day business activities and save a lot of time.

Disadvantages of ActiveX Control

These controls only work for Windows, and it is not supported in MAC. Thus, many users avoid them to use.
They are not used for basic and simple scripts.
By default, many computers don’t trust ActiveX controls and disable them. In that case, the user needs to add the trust center manually.

ActiveX controls are added in the Excel sheet while enabling the design mode. Once everything is set and, the programming is done, disable the design mode to activate the ActiveX control. The following steps are used to activate ActiveX Controls:

Go to the Developer Tab. Click on insert under the Controls section.

From the form control and ActiveX control, choose the required control. Unlike here, we have chosen the command button.

Make sure the design mode is enabled. Drag the control (command button) to the excel sheet and place is appropriately as per the excel layout.

You can rename the ActiveX control by right-clicking on the control and selecting the properties option. The properties dialog box appears on the left side of the sheet. Under the caption option set the control name. You can even change or enable the other properties unlike, font, BackColor, ForeColor, locked, visible, etc., for the control.

Double click on the control or right-click on the control and select the view code option. View code will direct you to the VBA Editor window where you can write the code to implement an action whenever the user clicks on the control. Write your code in between the sub and end sub procedure.

Below we have written a code to change the font color for the selected data to Red.

Private Sub CommandButton1_Click()

Selection.Font.Color = vbRed

End Sub

Come back again to the excel sheet and disable the design mode such as the control can become clickable.

Select the text and click on the ActiveX Command Button.

You will notice that the font color has changed from black to Red.