How to Create Interactive Forms in Excel VBA with ActiveX Controls

ActiveX Controls are interactive components that can be used within Microsoft Excel to enhance spreadsheet capabilities. Using ActiveX Controls, users can create more attractive and flexible interfaces, such as Text Boxes, List Boxes, Combo Boxes, Check Boxes, and Option Buttons.

This feature is powered by Visual Basic for Applications (VBA), which allows users to write code to organize interactions between users and Excel worksheets.

The presence of ActiveX in Excel VBA is essential because it offers a wide range of advantages.  ActiveX Controls provide more intuitive interactions, such as choosing from a list or entering text. Additionally, with VBA, users can customize the behavior and appearance of the controls as needed, such as automatically populating Combo Boxes from the database.

ActiveX also helps reduce input errors by providing structured controls, such as a Check Box for Yes/No options. ActiveX Controls can be integrated with VBA Macros, which allows for the automation of various tasks, including data validation and dynamic reporting.

Difference between ActiveX Controls and Form Controls in Excel

In Excel, there are two main types of controls that you can use: ActiveX Controls and Form Controls.

If you need more complex controls and integration with VBA, then ActiveX Controls are the way to go. However, if you just want to use simple buttons or lists without the need for programming, Form Controls will suffice.

Enabling ActiveX Controls in Excel

If ActiveX Controls don’t work in Excel, Excel’s built-in security settings likely limit this feature. Microsoft automatically restricts the use of ActiveX Controls to avoid security risks, such as the execution of malicious code from untrusted sources. Therefore, you need to change the security settings in the Trust Center so that Excel can use ActiveX Controls more freely. You can enable an option that allows ActiveX controls to operate without restrictions or ask for confirmation before activating them.

Steps:

1. Open the Microsoft Excel app.
2. Click on the File menu, then select Options and proceed to Trust Center.
3. In the window that appears, select Trust Center Settings….
4. In the left panel, find and select ActiveX Settings.
5. You will see several options; choose one of the following:
   • Enable all controls without restrictions and without prompting style=”color: initial;”> (Enable all controls without restrictions and confirmation requests).
   • Or, if you want a higher level of security, choose Prompt me before enabling all controls with minimal restrictions (Ask for confirmation before enabling all controls with minimal restrictions).
6. After choosing, click OK and close all the settings windows that open.

Types of ActiveX Controls and How to Use Them

In Excel VBA, ActiveX Controls are used to improve interactivity and automation in worksheets. Here are some of the most commonly used controls and how to use them.

1. Text Box

The Text Box allows users to enter or display text in an Excel UserForm or worksheet. With VBA, we can set the content of the Text Box, either by filling in the data automatically or by receiving input from users.

How to Create a Text Box in Excel VBA

1. Open Excel and enable the Developer tab.
2. Select Developer > Insert > Text Box (ActiveX Controls).
3. Click and drag to create a Text Box on the worksheet.
4. To add a VBA code, right-click on Text Box and then select View Code.

Sample VBA Code to Automatically Fill Text Boxes

The following code will automatically populate the Text Box with text when it runs:

TextBox1.Text “Data imported successfully”