Form Controls: How To Use Them In Excel And VBA

One of the coolest things about Excel is the ability to add Form Controls. Form Controls are interactive UI elements you have probably seen many times. They can be:

• Command Buttons
• Combo boxes
• Checkboxes

Form Controls are great because they are intuitive ways to interact with a piece of software that everyone can understand. Implementing them in an Excel sheet can really open up the complexity options in your project.

But, how do you use them? Form Controls can bring a lot to your application, but they are a little more complicated to configure and use in VBA. In this blog post, I am going to go through each Form Control and explain how to configure it, interact with it, and use it in a piece of code.

Let’s get started!

Adding Form Controls To A Spreadsheet

To add Form Controls to a spreadsheet, you will need to enable the Developer section of the toolbar:

If you do not see this tab on your toolbar, navigate to File –> Options:

And go to the ‘Customize Ribbon’ section:

Add the Developer option to your Main Tabs and click ‘OK.’ You should then be set to add Form Controls.

Command Button Form Controls

The first option you have for Form Controls is the command button. This is one of the most basic controls found in any application.

It is simply a button that you click on to trigger some other action. You can also format the label on the button and give it a few different colors and sizes. To add a command button to a sheet, go to your ‘Developer’ tab and under ‘Insert,’ select the first option:

Once selected, your cursor will change into a crosshair. You can now click and drag a rectangle onto your sheet, and the rectangle will be the size of your button:

When you let go of your mouse, Excel will automatically open a window that will ask you if you want to link a macro to this button:

In this case, thus button was the 8th Form Control I added to this sheet, so it shows up automatically as ‘Button8.’ When you go further into the macro by clicking ‘New,’ you will see the following:

Sub Button8_Click()

End Sub

With this, you can now create an event for your button to trigger when its clicked. Let’s create a button that puts a hard-coded value into a cell:

Sub Button8_Click()
    ActiveSheet.Cells(1, 1).Value = "Button was clicked!"
End Sub

Pretty easy right!? You can change the macro that gets fired off of this button by right-clicking on it and selecting ‘Assign Macro,’ and then choose the macro you want.

Command Button in VBA

Having a button and a macro are great, but what if you wanted to access your button in a macro? Well, paste the following code below and you can paste the button text in a cell:

Sub Button8_Click()
    ActiveSheet.Cells(1, 1).Value = ActiveSheet.Shapes("Button 8").OLEFormat.Object.Caption
End Sub

It’s important to note that there is a space between ‘Button’ and ‘8’ in the above macro. To access Form Controls on your sheet in VBA, you need to use the syntax:

Activesheet.Shapes([Optional Object Name]).OLEFormat.Object

The above syntax will then let you access a Form Controls properties and methods. For the command button, its properties and methods are listed here.

In this example, I am using the ‘Caption’ property to get the text on the button and paste it in a cell:

Ta-da! You can build some really advanced logic on your spreadsheets with controls once you can access them in VBA.

Combo Box Form Controls

The combo box, also known as a drop-down list, is a box with an arrow that shows you options when clicked. Clicking one of the options select it as the controls value:

To add a combo box to your spreadsheet, click on the ‘Insert’ option under the ‘Developer’ ribbon and select the second option:

Now, when you select this option, your cursor will, again, turn into a crosshair. Draw the size of the rectangle you want the combo box to be and it will render on the page.

You’ll notice you are not prompted to assign the combo box to a macro once it’s rendered. Normally, the combo box is not designed to trigger a macro, but it can be. Before we get to that, let’s configure our combo box. Right-click on it and select ‘Format Control’ and navigate to the ‘Control’ tab:

The tabs before control allow allow you to configure the visuals of the combo box, but the Control tab is where you can configure its values and what cell you can see the selected value index in.

In some other cells on the sheet, type in the values you want in the combo box, each in a separate cell, and link them to your input:

Select a single cell for the ‘Cell link’ option, and that cell will get the index of the combo box value selected, and it will update every time to combo box is updated:

Now, try out your combo box!

Pretty cool! You can also assign a macro to a combo box in the same way you can to a Command Button, and it will fire every time the value of the combo box is updated.

Combo Box In VBA

Having a combo box is great and being able to assign a macro to it is also helpful. But what if you wanted to put some more complex logic in the macro? Say you only wanted the macro to do something when a certain combo box value was selected. To get the value of a combo box in VBA:

Sub DropDown11_Change()
    Dim index As Long
    
    index = ActiveSheet.Shapes("Drop Down 11").OLEFormat.Object.ListIndex
    If index = 2 Then
        ActiveSheet.Cells(1, 4).Value = ActiveSheet.Shapes("Drop Down 11").OLEFormat.Object.List(index)
    Else
        ActiveSheet.Cells(1, 4).Value = ""
    End If
End Sub

The above code will get the index of the combo box value when it changes. If that index is 2, we write the value of the list item in our cell. If not, it clears the cell out:

When we select the second option:

And picking something else again:

As you can see, you can do a lot with VBA and Form Controls once you get the hang of it. The other methods and properties available for a combo box can be seen here.

Checkbox Form Controls

Everyone has used a checkbox when interacting with a web app. A checkbox give users a simple way to specify a ‘True’ or ‘False’ value:

To insert a check box on an Excel sheet, select the ‘Insert’ option under the ‘Developer’ tab and select the third option:

When you select it, your cursor will turn into a crosshair. Click on the sheet where you want your checkbox to render and it will appear:

For a checkbox, the most important configuration option is the ‘Cell link.’ You can specify a cell to hold the value of the checkbox as it changes:

Specify the cell you want to hold the checkbox value and:

Pretty cool! You can also assign a macro to checkbox and it will fire upon change.

Checkbox In VBA

A checkbox is very handy, but in a macro, the most important part of it is getting the checkbox value. Say you wanted to fire a macro and check that checkbox value to build some interesting logic:

Sub CheckBox12_Click()
    Dim checkboxvalue As Integer
    
    checkboxvalue = ActiveSheet.Shapes("Check Box 12").OLEFormat.Object.Value
    Debug.Print (checkboxvalue)
End Sub

If you run this macro, you can see in the VBA window that a value of True = 1 and a value of False = -4146. Why? I’m not sure, but you can use it for whatever logic you need!

You can see other properties and methods of the checkbox Form Control here.

Iterating Over Multiple Form Controls

Once you start using Form Controls in VBA, you can use the Shapes() method to get all the controls on a page and start iterating over them to see what;s on your sheet and how you can get their properties:

Sub ShapeNames()
For Each obj In ActiveSheet.Shapes()
    Debug.Print (obj.OLEFormat.Object.Name)
Next
End Sub

Running the above code for my sheet, I get:

The above code can be very helpful in figuring out what’s on your sheet and how you can reference it. Try combining From Controls with some of the other VBA code to make a dynamic, living application. Happy macro’ing!