How to Create Interactive Forms in Excel VBA with ActiveX Controls

Other Interesting Articles

2. List Box

List Box is a control element that serves to display a list of selections, allowing the user to select one or more items. This is especially useful in data input forms or for navigation between pages in applications that use Excel VBA.

How to Add Data to a List Box in Excel VBA

  1. Open the Developer Tab in Excel.
  2. Select Insert > List Box (ActiveX Controls).
  3. Click and drag to place the List Box on the worksheet.
  4. Right-click on List Box > Properties.

Example of VBA Code to Add Items to List Box

Use the following VBA code to add multiple city names to the List Box:

With Sheet1.ListBox1
     .AddItem    “Jakarta”
     .AddItem “Surabaya”
     .AddItem “Bandung”
End With

This code can be placed in Workbook_Open event so that the data will be loaded automatically when the file is opened.

Connecting List Boxes with Cells in Excel

To display the selected items from the List Box to a specific cell, perform the following steps:

  1. Right-click on List Box, then select Properties.
  2. Find the LinkedCell property and then enter the cell address (example: D3).
  3. Now, whenever the user selects an item in the List Box, the value will appear in the D3 cell.

If you want to retrieve the selected value using VBA, use the following code:

Range(“D3”).Value =  ListBox1.Value

3. Combo Box

Combo Box is an ActiveX control in Excel VBA that serves as a list of options that can be pulled down. Users can select a single item from the list or type their text. A Combo Box is often used in input forms to minimize errors when entering data, as users can only choose from the options that have been provided.

How to Add Dynamic Data to a Combo Box

To add a Combo Box to a worksheet, follow these steps:

  1. Open Excel and make sure that the Developer Tab is on.
  2. Click Developer > Insert > Combo Box (ActiveX Controls).
  3. Click and drag to draw Combo Box in the worksheet.
  4. Right-click on Combo Box, then select Properties.

Example VBA Code to Add Items to a Combo Box Dynamically

Use the following code to add items to the Combo Box automatically when the workbook is opened or the button is clicked:

ComboBox.Add  Item “Option 1”
ComboBox1.Add Item “Option 2”
ComboBox1.Add Item “Option 3”

Connecting Combo Boxes with Cells in Excel

To save the selected value in the Combo Box to a specific cell, perform the following steps:

  1. Right-click on Combo Box, then select Properties.
  2. Find the LinkedCell property and then enter the cell address (for example, D3).
  3. Now, whenever the user selects an item in the Combo Box, that value will appear in the D3 cell.

Alternatively, use the following VBA code to save the value of the option into the cell:

Range(“D3”).Value  ComboBox1.Value

4. Check Box

Check Box is a control that allows the user to select the Ya/Tidak or True/False option. This is useful for binary inputs such as confirmation of consent, feature selection, or activation/deactivation of a function in a form.

Using the Check Box to Save True/False Values

To add a Check Box in Excel VBA, follow these steps:

  1. Open the Developer Tab in Excel.
  2. Click Insert > Check Box (ActiveX Controls).
  3. Click and drag to place the Check Box on the worksheet.
  4. Right-click on Check Box > Properties.

Example of VBA Code to Connect a Check Box with a Cell

For Check Box to store values into cells based on their status, use the following code:

If CheckBox1.Value  = True Then
     Range(“D2”).Value  1
Else
     Range(“D2”).Value = 0
End If

This code will populate D2 cells with 1 if the Check Box is checked and 0 if it is not checked.

To make this function run automatically when the user clicks Check Box, use event Change inside the VBA Editor:

Latest Articles

Related Articles