Form Controls: How To Use Them In Excel And VBA

Excel Form Controls In VBA Banner
I wouldn’t touch those controls….

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:

Excel Developer Tab

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

Excel Options

And go to the ‘Customize Ribbon’ section:

Excel Customize Ribbon

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.

Excel Form Control Command Button

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:

Insert Excel Form Control Command Button

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:

Insert Excel Form Control Command Button 1

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:

Excel Form Control Command Button Macro

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
Excel Form Control Command Button On Sheet
After the macro ran

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:

Excel Form Control Command Button In VBA
After the macro ran

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:

Excel Form Control Combo Box

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

Insert Excel Form Control Combo Box

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:

Excel Form Control Combo Box Configuration

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:

Excel Form Control Combo Box Options
Options for the combo box

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:

Excel Form Control Combo Box Cell Link
Cell to see the index of the value

Now, try out your combo box!

Excel Form Control Combo Box Selecting An Option
Selecting an option
Excel Form Control Combo Box Option Selected
Option selected

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)
        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:

Excel Form Control Combo Box In VBA
Not the option we want

When we select the second option:

Excel Form Control Combo Box In VBA Right Selection
The option we want!

And picking something else again:

Excel Form Control Combo Box In VBA Wrong Option
Not the right option 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:

Excel Form Control Checkbox

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

Insert Excel Form Control Checkbox

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:

Excel Form Control Checkbox On Sheet

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:

Excel Form Control Checkbox Configuration
Selecting our cell link to show the value

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

Excel Form Control Checkbox Cell Link
The cell value!

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!

Excel Form Control Checkbox In VBA
The true and false values as I was checking and unchecking the box

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)
End Sub

Running the above code for my sheet, I get:

Excel Form Controls Iteration
All my form controls

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!

One thought on “Form Controls: How To Use Them In Excel And VBA

Leave a Reply

%d bloggers like this: