[VBA] How to Manipulate Form Controls on a Sheet (ControlFormat Object)

Introduction

Excel worksheets allow you to place two types of controls: “Form Controls” and “ActiveX Controls.” Among these, Form Controls are generally easier to use.

Usually, you link these controls to cells using the LinkedCell property to manage their values. However, by using VBA, you can directly read and write the state of these controls without going through cells.

This article explains the ControlFormat object, which is the key to manipulating Form Controls (Checkboxes, List Boxes, Spin Buttons, etc.) on a worksheet using VBA, along with its main properties.

How to Access the ControlFormat Object

Form Controls on a sheet are recognized as Shape objects in VBA. You access control-specific functions through the .ControlFormat property of that Shape object.

Basic Syntax: ActiveSheet.Shapes("ControlName").ControlFormat

Main Properties by Control Type and Sample Code

1. Check Boxes / Option Buttons

You can get or set the state of check boxes or option buttons.

Main Properties:

• .Value: Gets or sets the control state.
  • Check Box: On = 1 (xlOn), Off = -4146 (xlOff)
  • Option Button: Selected = 1 (xlOn), Not Selected = -4146 (xlOff)
• .LinkedCell: Sets the address of the linked cell as a string (e.g., “A1”).

Sample Code:

' Manipulate the state of a Check Box
Sub ManipulateCheckBox()
    Dim chkBoxShape As Shape
    Set chkBoxShape = ActiveSheet.Shapes("ApprovalCheckBox")
    
    ' Turn the checkbox on
    chkBoxShape.ControlFormat.Value = xlOn
    
    ' Display the current state in a message box
    If chkBoxShape.ControlFormat.Value = xlOn Then
        MsgBox chkBoxShape.Name & " is ON."
    Else
        MsgBox chkBoxShape.Name & " is OFF."
    End If
End Sub

2. List Boxes / Combo Boxes

You can manipulate list items or determine which item is selected.

Main Properties:

• .ListFillRange: Sets the source cell range for list items as a string (e.g., “Sheet2!A1:A10”).
• .ListIndex: Gets or sets the index number of the selected item (starts at 1). Returns 0 if nothing is selected.
• .Value: Returns the index number of the selected item, same as .ListIndex.
• .LinkedCell: Sets the cell address to output the selection result (index number).

Sample Code:

' Manipulate a List Box
Sub ManipulateListBox()
    Dim listBoxShape As Shape
    Set listBoxShape = ActiveSheet.Shapes("ProductListBox")
    
    With listBoxShape.ControlFormat
        ' Set the range for list items
        .ListFillRange = "ItemList!A2:A6"
        
        ' Select the 3rd item in the list
        .ListIndex = 3
        
        ' Display the index of the selected item
        MsgBox "Item number " & .ListIndex & " is selected."
    End With
End Sub

3. Spin Buttons / Scroll Bars

You can manipulate numeric ranges or the current value.

Main Properties:

• .Value: Gets or sets the current value.
• .Min: Minimum allowable value.
• .Max: Maximum allowable value.
• .SmallChange: The amount the value changes when the button arrow is clicked once.
• .LinkedCell: Sets the cell address to output the current value.

Sample Code:

' Manipulate a Spin Button
Sub ManipulateSpinner()
    Dim spinnerShape As Shape
    Set spinnerShape = ActiveSheet.Shapes("QuantitySpinner")
    
    With spinnerShape.ControlFormat
        ' Set the range
        .Min = 1
        .Max = 100
        
        ' Set the current value
        .Value = 10
        
        ' Set the linked cell
        .LinkedCell = "C5"
        
        MsgBox "Current Value: " & .Value
    End With
End Sub

Summary

In this article, I explained how to directly manipulate Form Controls on a sheet using the VBA ControlFormat object.

• Access controls on a sheet using the Shapes collection.
• Access control-specific features via the .ControlFormat property.
• Read and write states using properties like .Value, .ListFillRange, and .LinkedCell.

While linking controls to cells using LinkedCell is the basic usage of Form Controls, knowing how to manipulate values directly with VBA is very useful when creating more dynamic and advanced sheet applications.