What do you mean by a checkbox in ActiveX control in excel?

The checkbox represents whether a choice is selected or not, and it will give the result corresponding to it.

What is the options button?

The option buttons give you only one choice within the many mutually exclusive choices available.

What do you mean by the list box?

List box refers to the list of one or more than one choice given in the form of a drop-down list from which you can select only one.

How is the combo box different from the Listbox?

In the list box, you can only select from the list given, while in the combo box, you can either select from the given list and, if you do not find the required choice, you can type it manually.

Table of contents

Introduction

Different ActiveX Controls

2.1.

Command Buttons

2.2.

Text Box

2.3.

List Box

2.4.

Combo Box

2.5.

Check Box

2.6.

Option Buttons

2.7.

Spin Button

Loan Calculator

FAQs

Key Takeaways

Last Updated: Mar 27, 2024

ActiveX Controls

Author Naman Kukreja

Introduction

Data is an essential key to success in this technologically enhancing world, and if you want to be an active part of it, you should be good at dealing with data. Dealing with data involves monitoring, analyzing, gathering, consolidating, and connecting the data.

Excel is one of the best tools to work with data. If you want to increase efficiency in excel, like managing data, increasing or decreasing data, adding a drop-down list, adding some command buttons, checkboxes, and list boxes. So how do you achieve all these functions in excel?

The answer to the above question is using the ActiveX Control feature in excel. We will learn about all the features while moving further with the blog, So let's get on with our topic without wasting anytime further.

Different ActiveX Controls

There are various ActiveX controls available in excel, like command buttons, list boxes, and text boxes. We will learn all of them with proper code in VBA.

Command Buttons

To create a command button in Activex Control, follow the steps given below:

Open the Developer tab from the home page and click on insert and then click on the command button like this:

Drag the command button to your worksheet.
Select the design mode, right-click on it, and select the option view code.

Change the command button name to Apply Red Color by right-clicking on properties and making sure the design mode is on.
Now Visual Basic Editor appears to write the following line in it.

Select the range of your text and then see the output:

Text Box

As the name suggests text box is used to fill text, it is empty. To create a text box, follow the given steps:

Open the Developer tab from the home page, click on insert, and click on the text box like this:

Drag a command button and a text box on your worksheet. Select a text box, right-click on the command button, and click on view code.

Enter the following code in the Visual Basic Editor.

TextBox1.Text = "Data imported successfully."

Now click the command button on the sheet.

Use the following line to clear the text box.

TextBox1.Value = " "

List Box

It is a list or drop-down list from which the user can select the option of his choice. To create a List box, follow the steps given below.

Open the Developer tab and click on the List box option.

Now drag the list box to the adequate position on the worksheet.

Now open the Visual Basic Editor and double click on this worksheet in the project explorer.
On the coding tab part, from the left drop-down menu, select workbook, and in the right drop-down menu, select open like shown in the image below:

Add the following item to the list box.

With Sheet1.ListBox1
    .AddItem "Paris."AddItem "New York."AddItem "London"
    .AddItem "Delhi"
    .AddItem "Sydney"
    
End With

You can use the sheet number accordingly, and to make sure it is not repeated again and again, write this line.

ListBox1.Clear

We need to link the list box with the sheet, so right-click on the list box and open properties like shown below and write D3 in the linked cell:

Now save all the changes and open the excel file again.

Combo Box

It is pretty similar to a list box but has a significant difference. It is a drop-down list-like list box, but we can either select from the list or type a different choice with our requirement.

To make a combo box in excel, follow the steps given below:

Open the Developer tab from the home page, click on insert, and click on the combo box like this:

Drag the combo box to the required position on your worksheet.

Now open the Visual Basic Editor and double click on this worksheet in the project explorer.
On the coding tab part, from the left drop-down menu, select workbook, and in the right drop-down menu, select open like shown in the image below:

Now add the following items in the code:

With Sheet1.ComboBox1
    .AddItem "Delhi"
    .AddItem "Dubai"
    .AddItem "Paris"
End With

You can use the sheet number accordingly, and to make sure it is not repeated again and again, write these lines.

ComboBox1.Clear
ComboBox1.Value = ""

We need to link the list box with the sheet, so right-click on the list box and open properties like shown below and write D3 in the linked cell:

Now save all the changes and open the excel file again.

Check Box

The check box is a field used to store checked information. To create a check box in excel, follow the following steps.

Open the Developer tab from the home page, click on insert, and click on the check box like this:

Drag the check box to the required position of your worksheet.

Right-click on the checkbox and click on view code.

Add the following lines in the code:

If CheckBox1.Value = True Then Range("D2").Value = 1
If CheckBox1.Value = False Then Range("D2").Value = 0

Check and un-check the checkbox to see if the code is working or not.

Option Buttons

As the name suggests, these buttons come into use when there is more than one option, and you want to select only one and print the result corresponding to that option chosen button itself. To create the options button in excel, follow the steps given below:

Open the Developer tab from the home page, click on insert, and click on the options button like this:

Drag the two options button to the required position of your worksheet.
Right-click on either one of them and click on the view code.

Add the following line in the code:

If OptionButton1.Value = True Then Range("D3").Value = 30

Now repeat the above steps for the other option button and add the following line in its code in the VBA.

If OptionButton1.Value = True Then Range("D3").Value = 40

The result will be this:

Spin Button

A spin button is used to either increment or decrements a number in excel. To execute the spin button in excel VBA, follow the steps given below.

Open the Developer tab from the home page, click on insert, and click on the spin button like this:

Drag the spin button to the required position in the excel worksheet and then right-click on the spin button, and then click on the view code:

To link the spin button with the worksheet, add the following line:

Range("C3").Value = SpinButton1.Value

We can set maximum and minimum on the spin button by adding the following lines:

SpinButton1.Max = 00
SpinButton1.Min = 0

We can set the incremental value in the spin button by the line given below:

SpinButton1.SmallChange = 2

Click the arrows for incrementing and decrementing and observe the change in value.

You can change the properties at run time by right-clicking on the spin button shown below:

Loan Calculator

The loan calculator is a perfect example of combing and implementing all the concepts of ActiveX controls that we have learned so far. We will make a simple loan calculator that contains two option buttons and two scroll bars.

Follow the given instructions to make a loan calculator.

Add the two option buttons. Go to the Developer tab and then click on ActiveX control. Select the two option buttons and place them accordingly in the worksheet.

Add the two Scroll bars. Go to the Developer tab and then click on ActiveX control. Select the two scroll bars and place them accordingly in the worksheet.

Right-click on the first scroll bar, open properties and set min to 0, max to 20, LargeChange to 2, and small change to 0.
Similarly, for the second scroll bar, change the min to 5, max to 30, LargeChange to 5, SmallChange to 1, and link it to cell F8.
Open Visual Basic Editor and open the worksheet and choose the worksheet from the left drop-down list, and change from the right.

We want VBA to work only in case when there is an error or change in D$. Then to do so, we add the following line:

If Target.Address = "$D$4" Then Application.Run "Calculate"

We will get the right percentage in cell F6. Add the following lines in the code of the first scroll bar to do so:

Private Sub ScrollBar1_Change()

 
Range("F6").Value = ScrollBar1.Value / 100
Application.Run "Calculate"

 
End Sub

Add the following lines to the second scroll bar control.

Private Sub ScrollBar2_Change()

 
Application.Run "Calculate"

 
End Sub

Open the view code of the first option button and add the following code:

Private Sub OptionButton1_Click()

 
If OptionButton1.Value = True Then Range("C12").Value = "Monthly Payment"
Application.Run "Calculate"

 
End Sub

Similarly, follow the steps for the second option button.

Private Sub OptionButton2_Click()

 
If OptionButton2.Value = True Then Range("C12").Value = "Yearly Payment"
Application.Run "Calculate"

 
End Sub

Now we will create a function that will solve all the loan calculator calculations and represent the result.
Now place a module and add the following code to it.

Sub Calculate()

 
Dim loan As Long, rate As Double, nper As Integer

 
loan = Range("D4").Value
rate = Range("F6").Value
nper = Range("F8").Value

 
If Sheet1.OptionButton1.Value = True Then
    rate = rate / 12
    nper = nper * 12
End If

 
Range("D12").Value = -1 * WorksheetFunction.Pmt(rate, nper, loan)

 
End Sub

Now your result is ready, and when you open your excel sheet, it will look somewhat like this. You can color the background and style it accordingly.

Source

FAQs

What do you mean by a checkbox in ActiveX control in excel?
The checkbox represents whether a choice is selected or not, and it will give the result corresponding to it.
What is the options button?
The option buttons give you only one choice within the many mutually exclusive choices available.
What do you mean by the list box?
List box refers to the list of one or more than one choice given in the form of a drop-down list from which you can select only one.
How is the combo box different from the Listbox?
In the list box, you can only select from the list given, while in the combo box, you can either select from the given list and, if you do not find the required choice, you can type it manually.

Key Takeaways

In this article, we have extensively discussed Different ActiveX Controls like a list box, combo box, scroll bar, option buttons, command buttons, and many more from scratch and also learned how to control their working with writing code in VBA and to change the properties and to sum up all of your understanding we have also made a simple loan calculator containing scroll bars and option buttons.

We hope that this blog has helped you enhance your knowledge of excel. If you are interested in learning how to work with VBA with an example of date and time, you must refer to this blog. Here you will get and complete idea about the date and time and the different functions we can perform with them using VBA. If you would like to learn more, check out our articles on Code studio. Do upvote our blog to help other ninjas grow.

“Happy Coding!”