Logo
×

ActiveX Controls

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:

  1. Go to the Developer Tab. Click on insert under the Controls section.
ActiveX Controls
  • From the form control and ActiveX control, choose the required control. Unlike here, we have chosen the command button.
ActiveX Controls
  • 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.
ActiveX Controls
  • 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.
ActiveX Controls
ActiveX Controls
  • 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.
ActiveX Controls
  • 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

ActiveX Controls
  • Come back again to the excel sheet and disable the design mode such as the control can become clickable.
ActiveX Controls
  • Select the text and click on the ActiveX Command Button.
ActiveX Controls
  • You will notice that the font color has changed from black to Red.
ActiveX Controls

Related Topics

VBA Updating Pivot Table

VBA- Updating Pivot Table The pivot table is an important feature to explore, summarize, and interpret the bulk amount of data. It helps data evaluating, reviewing, as well as making useful...

3 minutes read.

Excel VBA MessageBox

Message Box The MsgBox in Excel VBA is a dialog box used to inform the users of your program by showing a custom message or get some necessary inputs such as Yes/No or...

4 minutes read.

Excel VBA CDec Function

VBA CDec Function: The CDec function in VBA converts an expression into a Decimal data type. Syntax CDec (Expression) Parameter Expression (required) – This parameter represents the expression that you want to convert to a...

1 minute read.

VBA Regex

What is a Regex? Regex stands for Regular Expression is basically a pattern matching strings within another string. They are supported in many languages, including .net, C++, Python, etc. They are...

5 minutes read.

Excel VBA IsArray Function

VBA IsArray Function: The IsArray function in VBA returns a Boolean, showing whether the given variable is an Array or not. Syntax IsArray (VarName) Parameter VarName (required)- This parameter represents the variable that you want...

1 minute read.

VBA runtime error 1004

What is 1004 error?  VBA 1004 Error, also known as object-defined or application-defined, is a runtime error in VBA, usually, if the specified range does not exist in the worksheet or if the Application...

6 minutes read.

Excel VBA Cos Function

VBA Cos Function: The Cos function in VBA returns the cosine value for a supplied angle. Syntax Cos (Number) Parameter Number (required) –This parameter represents the number that you want the absolute value of. Return This function...

1 minute read.

Excel VBA Right Function

VBA Right Function: The Right function in VBA returns a substring from the end of the given string. Syntax Right (Str, Length) Parameter Str (required) – This parameter represents the string from which you want to...

1 minute read.

Excel VBA FormatNumber Function

VBA FormatNumber Function: The FormatNumber function in VBA is used to apply a number format to a numeric expression, and it returns the result as a string. Syntax FormatNumber (Expression, [NumDigitsAfterDecimal], [IncludeLeadingDigit], [UseParensForNegativeNumbers], [GroupDigits]) Parameter Expression (required)...

2 minutes read.

Introduction to Visual Basic Editor Window

How to enable the Developer Ribbon Tab? In order to work with VBA, users need to make a small change in Excel to display a new tab (Developer) at the top of the...

5 minutes read.

VBA UBound

VBA UBound  The UBound or Upper Bound function in VBA is used to specify the length of an array and returns the highest subscript for a dimension for the specified array. It is...

3 minutes read.

Excel VBA IsError Function

VBA IsError Function: The IsError function in VBA returns a Boolean value showing whether the specified Expression represents an error or not. Syntax IsError (Expression) Parameter Expression (required)- This parameter represents the variant that you...

1 minute read.

Excel VBA StrComp Function

VBA StrComp Function: The StrComp function in VBA compares two strings and returns an integer value displaying the result of the comparison. Syntax StrComp (String1, String2, [Compare]) Parameter String1 (required)- This parameter represents the first string to...

2 minutes read.

Excel VBA : With End-with Constructs

With End-with Constructs The With-End With construct enables the user to perform multiple operations on a single object. If you are going to perform several different actions on the same object and typing the same...

4 minutes read.

Excel VBA IsNumeric Function

VBA IsNumeric Function: The IsNumeric function in VBA returns a Boolean value showing whether the specified Expression contains a numeric value or not. Syntax IsNumeric (Expression) Parameter Expression (required)- This parameter represents the variant that...

1 minute read.

Excel VBA Tan Function

VBA Tan Function: The Tan function in VBA returns the tangent for the specified angle in radians. Syntax Tan (Number) Parameter Number (required) – This parameter represents the angle supplied in radiant that you want...

1 minute read.

Scope in Visual Basics

Definition of Scope The scope of any programming language implies the area of code where the variables will be identified, accessed, and used. Every variable has a scope associated with it. The scope of...

4 minutes read.

Excel VBA Join Function

Excel VBA Join Function: The Join function in VBA is used to join an array of substrings and return them all as a single string. Syntax Join (SourceArray, [Delimiter]) Parameter SourceArray (required) – This parameter...

1 minute read.

Excel VBA LTrim Function

VBA LTrim Function: The LTrim function in VBA removes the leading spaces from a supplied text string. Syntax LTrim (String) Parameter String (required) - This parameter represents he text string that you want to remove...

1 minute read.

Excel VBA Trim Function

The Trim function in VBA removes the leading and trailing spaces from the specified string. Syntax Trim (String) Parameter String (required) – This parameter represents the string from which you want to remove the leading and...

1 minute read.