VBA Controls – Form Control & ActiveX Control in Excel

What is a VBA form?

A form is a document designed with a format to collect, organize and edit information. There are two types of forms.

• Printed Forms
• Online Forms

Printed Forms:

These are the forms that may use an excel template to organize the questions to be asked via forms. It contains instructions, formatting, labels, and data.

Online Forms:

Online forms are more similar to the printed forms but online forms contain controls. These controls are the objects that display, enter or edit data, perform an action or execute a selection. Controls make the form much more simpler and easier to use.

Some of the common controls are list box, option buttons and the controls can also run the macros and respond to events such as by running the VBA code, clicks on the button created in the Excel spreadsheets.

Types of Excel forms

There are different types of forms such as data forms, VBA userforms and the spreadsheet that contains ActiveX and form controls. The user can create individual forms or combine all types of forms in different ways to achieve the requirement appropriately.

Worksheet with Form and ActiveX controls

A worksheet is where the user enters and views the data and there are also many built-in functions to organize the data and derive some conclusions from it. The user can enter and format the cells in many ways. The user can use the cells as labels and by adjusting the height, width, and merging of cells, the user can make the spreadsheet look like a data entry form.

The other features such as cell comments, hyperlinks data validation, embedded formatting, charts, the auto filter can make the spreadsheet look like an advanced form. The user can add controls and other objects to the worksheet for much more convenience. For example, the user can create a list box to list the options available. The user can set properties such as it can either control floats freely or moves and resizes together with a cell.

For example:

The user might want a checkbox that would move together with the cell when the range is sorted. If the user has a list box then they might prefer to keep it in a specific location at all times instead of moving along with the cell.

There are two types of controls and they are:

• Form controls
• ActiveX controls

Note: The user can add objects from the drawing tools such as shapes, text boxes, word art etc in addition to these controls.

VBA Form controls

The user can use Form controls when the user wants to interact with the cell data without using VBA code. The user can also use form controls when they want to add controls to chart sheets.

For example: After adding a list box control to the spreadsheet and linking it to a cell, the user can return a numeric value for the current position of the selected item in the control. Then, that numeric value is accessed with the INDEX function to select different items from the list.

The user can run macros by using Form controls and the user can also attach an existing macro to control or write or record a new macro. The control runs the macro when the user clicks the control.

VBA ActiveX controls

With or without the use of VBA code, and on VBA UserForms, the ActiveX controls are used on the spreadsheet forms. The ActiveX controls are used when the user needs more flexible design requirements. These controls have properties settings in which the user can access it to customize their appearance, behavior, fonts, and other characteristics.

The user can also write macros that respond to events associated with ActiveX controls. While automating any application with VBA, the ActiveX controls are most frequently used controls. The user uses different ActiveX Controls in Excel spreadsheet or UserForm and performs various operations in Excel applications.

Some of the ActiveX Controls are CheckBox, Label, Image, TextBox, ComboBox, ListBox, CommandButton, OptionButton etc.

For example:

The user can perform different actions, depending on which choice they select from a list box control.

Excel Form Control Vs ActiveX Control

Excel Form Control and Activex Control are almost alike but the applications vary a little between them.

VBA Controls: VBA Form Control & ActiveX Controls in Excel

GUI controls in Excel

GUI stands for Graphical User Interface and it is a part of the program that the user interacts with. A GUI is made up of controls and these controls can be used in a Form by VBA excel. The most frequently used GUI VBA controls are:

To access VBA GUI control, follow the steps:

Step 1: Go to the Developer tab

Step 2: Choose the insert drop-down button.

Step- 3: Choose a form control of your choice and these options are available in the drop down panel.

Whenever the user plays the cursor on the VBA form controls, the name of the control appears.

Adding GUI controls to a spreadsheet

Let’s now add a command button to the worksheet, follow the steps:

Step 1: Go to the developer tab and Click on the Insert dropdown

Step 2: Click on Command Button which is an ActiveX Control

Step 3: Draw the button on the sheet as per the user’s convenience.

Setting GUI control properties

Lets now set the caption of the button by following the steps:

Step 1: Right-click on the button

Step 2: Choose Properties from the options available.

Note: A window appears containing the properties of the form control. The user uses the name property to identify the control in the code window.

Step 3: Let’s now set the name property to btnButton.
Note: Button prefix is btn.

Step 4: Let’s now set the caption property to DataFlair. This is what the users will view on the button.

Step 5: Close the window when you are done.

The output will look like:

How to Use Form Control in Excel?

To use the form control in excel, go to the Developer tab and click the insert button. The list of form control appears. The first half of the list is the form controls.

The user can create different types of controls by just clicking on the item and dragging them to the sheet. After this, the user needs to connect the inserted form controls with the data and VBA code, the user may get some output.

How to create ActiveX control in VBA

Let’s see how to incorporate the ‘command click’ button in VBA using ActiveX control Excel. To execute a program, follow the steps:

Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose a button from the drop-down list and make sure the button you choose is under the activex controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step 4: Now, right-click on the command button and choose the view code option from it.

Step 5: Enter your code once the code editor opens up.
Note: Before writing the code, check you are in the right sheet.

Step 6: Copy-paste the following code in your editor.

Code to create an ActiveX Control Form:

Private Sub CommandButton1_Click()
    Range("A1").Value = "DataFlair Tutorial"
    Range("A2").Value = "VBA Controls - DataFlair"
End Sub

Code Explanation:

Here, we define two strings to each cell in the worksheet. So, when the user clicks on the button, A1 will display DataFlair Tutorial and A2 will display VBA Controls – DataFlair

Step 7: Save the code and then return to the excel file.

Step 8: In the Excel sheet, check whether the design mode is on.
Note: If it appears as shown in the image, then turn off the design mode.

Step 9: Click on the Design mode to turn it off and then click on the button.

The following output appears:

VBA Text Box

An empty field where the user can fill it with text or values is known as a text box. VBA TextBox is also a form control in Excel. This comes in handy when the user has to get the input from the other user such as name, date of birth etc.

The textbox control helps the user to input the information and this saves more time and the errors while typing. It consists of static or dynamic data. Text box leads to a way to get data from the user and this paves a way to process the data and find out some useful insights out of it.

To create a textbox in Excel, follow the steps:

Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose a textbox icon from the drop-down list and make sure the button you choose is under the activex controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step – 4: Right-click on the command button and select view code.

Note: The user can change the caption and name of a control by right-clicking on the control and then selecting the Properties option. Here, TextBox1 is the name of the text box.

Step -5: Copy paste the following code in your editor.

Code to create a TextBox:

Private Sub TextBox1_Change()
TextBox1.Text = "DataFlair Website!"
End Sub

Code Explanation:
Here, the user is adding the content to the text box. The content that will appear in the text box is “DataFlair Website!”

Step – 6: Save and run the code. The following output appear in the sheet:

Step – 7: To clear the text in the text box, change the code in the editor as follows

Code to clear the text in the TextBox:

TextBox1.Value = ""

Note: The textbox is very much helpful in a userform and sometimes, the user can use it to directly place a text box on the worksheet.

VBA List Box

List box is also a tool under the user forms in VBA. The list box comes with a user form in VBA. The list box can hold a list of values in it and the user can select an item from the Listbox. An empty field where the user can fill it with a list of items is known as a list box. To create a Listbox in Excel, follow the steps:

Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose a Listbox icon from the drop-down list and make sure the button you choose is under the activex controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step – 4: Right-click on the command button and select view code.

Note: The user can change the caption and name of a control by right-clicking on the control and then selecting the Properties option. Here, the ListBox1 is the name of the list box.

Step -5: Copy paste the following code in your editor.

Code to create a ListBox:

Private Sub ListBox1_Click()
With Sheet3.ListBox1
    .AddItem "Excel"
    .AddItem "VBA"
    .AddItem "Python"
End With
End Sub

Code Explanation:
Here, the user is adding a list of items to the list box. The content that will appear in the listbox are “Excel”, “VBA”, “Python”.

Step – 6: Save and run the code. The following output appears in the sheet:

Step – 7: To clear the list items in the list box, change the code in the editor as follows

Code to clear the list items:

ListBox1.Clear

Note: The listbox is very much helpful in a userform and sometimes, the user can use it to directly place a list item on the worksheet.

VBA Combo Box

A drop-down list from which a user can select an item to fill their choice is known as combo box. To create a combo box, follow the steps:

Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose a combo box icon from the drop-down list and make sure the button you choose is under the ActiveX controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step 4: Right-click on the command button and select view code.

Note: The user can change the caption and name of a control by right clicking on the control and then selecting the Properties option. Here, the ComboBox1 is the name of the combo box.

Step 5: Copy-paste the following code in your editor.

Code to add a ComBox in the Excel Sheet:

Private Sub ComboBox1_Change()
With Sheet4.ComboBox1
    .AddItem "Excel"
    .AddItem "Python"
    .AddItem "Java"
End With
End Sub

Code Explanation:
Here, the user is adding a drop-down list of items to the combo box. The content that will appear in the combo box is “Excel”, “Python”, “Java”.

Step – 6: Save and run the code. The following output appears in the sheet:

Step – 7: To clear the combo list items in the list box, change the code in the editor as follows

Code to clear the combo list items:

ComboBox1.Clear
ComboBox1.Value = ""

Note: The combo box is very much helpful for a userform and sometimes, the user can use it to directly place a drop-down combo list item on the worksheet.

VBA Check Box

A field where the information is stored to check it is known as a checkbox. To create a checkbox, follow the steps:
Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose the checkbox icon from the drop-down list and make sure the button you choose is under the ActiveX controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step – 4: Right-click on the command button and select view code.

Note: The user can change the caption and name of a control by right-clicking on the control and then selecting the Properties option. Here, CheckBox1 is the name of the check box.

Step -5: Copy paste the following code in your editor.

CheckBox Code:

Private Sub CheckBox1_Click()
If CheckBox1.Value = True Then Range("A1").Value = "DataFlair"
If CheckBox1.Value = False Then Range("A1").Value = "No Article Available"
End Sub

Code Explanation:
Here, the user is adding a checkbox and if the checkbox is marked, “DataFlair” appears in the A1 cell. If the checkbox is not marked, then “No Article Available” appears in the A2 cell.

Step – 6: Save and run the code. The following output appears in the sheet:

Note: The checkbox is very much helpful in a userform and sometimes, the user can use it to directly place a drop down combo list item on the worksheet.

VBA Option Buttons

The user uses the option button when there are multiple options but only one has to be selected. To create an option button, follow the steps:

Step 1: Go to the Developer tab and click on the insert button.

Step 2: Choose the option button icon from the drop-down list and make sure the button you choose is under the ActiveX controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step – 4: Right-click on the command button and select view code.
Note: Add as many as button required. Here, we have two options buttons in the sheet.

Note: The user can change the caption and name of a control by right-clicking on the control and then selecting the Properties option. Here, OptionButton1 is the name of the option button1 and OptionButton2 is the name of the option button2.

Step -5: Copy paste the following code in your editor.

Code to add Option Button:

Private Sub OptionButton1_Click()
If OptionButton1.Value = True Then Range("A1").Value = "DataFlair"
End Sub
 
Private Sub OptionButton2_Click()
If OptionButton2.Value = True Then Range("A2").Value = "DataFlair Excel"
End Sub

Code Explanation:
Here, if the user clicks on the first option button then “DataFlair” appears in the A1 cell or else if the user clicks on the second option button then “DataFlair Excel” appears in the A2 cell.

Step – 6: Save and run the code. The following output appears in the sheet:

Note: The option box is very helpful in a userform and sometimes, the user can use it to directly place a drop down combo list item on the worksheet.

VBA Spin Button

The user uses the spin button to increase a number in the cell. To create a spin button, follow the steps:
Step 1: Go to the developer tab and click on the insert button.

Step 2: Choose the spin button icon from the drop down list and make sure the button you choose is under the activex controls.

Step 3: Draw the button on the sheet as per the user’s convenience.

Step – 4: Right-click on the command button and select view code.

Note: The user can change the caption and name of a control by right clicking on the control and then selecting the Properties option. Here, the SpinButton1 is the name of the spin button.

Step -5: Copy paste the following code in your editor.

Code to add Spin Button in Excel:

Private Sub SpinButton1_Change()
Range("A1").Value = SpinButton1.Value
SpinButton1.Max = 50
SpinButton1.Min = 0
SpinButton1.SmallChange = 5
End Sub

Code Explanation:
Here, when the user presses on the right side arrow, the value increases by 5. If the user presses the left side arrow, the value decreases by 5. Initially, the A1 value is set upto 0.

Step – 6: Save and run the code. The following output appears in the sheet:

Prerequisite

• Make sure you have a Microsoft Excel and save the workbook as an Excel Macro-Enabled Workbook file extension.
• Make sure the ribbon contains the developer tab.

Pros of Form Controls

• The controls in the forms are easy to implement.
• Selecting the range and cell link in the control option is almost the same and it’s easy to implement by the user.

Things to Remember

• ActiveX controls are utilized by the macros.
• Both macros and manual data can use the form controls.
• The form controls are also customizable.
• The form controls are also used with the charts in excel.

Summary

Graphical User Interface plays a huge part of the program to interact with the user and they are made up of controls.
The checkbox, Listbox, command, textbox are some of the few controls which are commonly used in the VBA forms.