How to Create Interactive Forms in Excel VBA with ActiveX Controls

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

5. Option Buttons

Both Check Box and Option Buttons are used to provide options to users, but there are important differences between the two:

If the user needs to select more than one option, use Check Box. However, if you can only choose one of several options, use Option Buttons.

Steps to Create a Selection Button in Excel VBA

1. Open Excel and enable the Developer tab.
2. Select Developer > Insert > Option Button (ActiveX Controls).
3. Click and drag to place two Option Buttons on the worksheet.
4. Right-click on each Option Button and then select Properties.
   1. Rename (Name) to OptionButton1 and OptionButton2.
   1. Change the Caption to “Male” for OptionButton1 and “Female” for OptionButton2.
5. To group the two buttons, place them in Frame (ActiveX Controls) or on the UserForm.

Example of VBA Code for Connecting Selector Keys with Cells

The following code will store a selection from the Option Button into the D3 cell according to the user’s choice:

If OptionButton1.Value  =  True Then Range(“D3”).Value “Male”
If OptionButton2.Value  =  True Then Range(“D3”).Value “Female”

To ensure this code runs automatically when the user clicks the button, use the following VBA events:

Private Sub OptionButton1_Click()
    Range(“D3”).Value =“Male”
End Sub

Private Sub OptionButton2_Click()
     Range(“D3”).Value =“Female”
End Sub

With this code, D3 cells will be automatically updated according to the choices made by the user.

6. Spin Button

Spin Button is a control used to increase or decrease the numerical value in a cell gradually. This is especially useful for numerical inputs, such as setting the number of goods, interest rates, or other parameters that have value limitations.

How to Set the Maximum and Minimum Values in the Spin Button

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

Example of VBA Code to Set the Spin Button

Use the following code to set the Spin Button value limit:

SpinButton1.Max  = 100
SpinButton1.Min  = 0

To connect Spin Button with cells in Excel, use the following code:

Private Sub SpinButton1_Change()
    Range(“C3”).Value = SpinButton1.Value
End Sub

This code will ensure that every time the user presses the Spin Button key, the values in the C3 cell will change according to the values set in the controls.