How to create multiple checkboxes in Excel using VBA

In this article you will learn how to create checkboxes which are automatically linked to particular cells. VBA doesn't allow the creation of multiple checkboxes at once. You can manually link checkboxes to specific cells (Copy/Paste). For each copy of your checkbox you have to select a cell.

Excel/VBA - How to create multiple checkboxes

The Control type

• There are two types of checkboxes in Excel: forms and ActiveX controls. In this article, we will use the form type.

Avoid cluttering your Excel sheet with too many items

• Excel does not always handle a cluttered page very well, especially if you intend to use a workbook on multiple versions of Excel.

Creating the checkboxes

For each of your checkboxes, there will be two important cells:

• The position cell: the one which we will draw our checkbox.
• The linked cell: one in which we will return the result of our checkbox.
• Note that in this example the position cell and the linked cell are the same. Checking or unchecking this box will return TRUE or FALSE in the linked cell. Unfortunately Excel does not give the ability to set up multiple checkboxes. There are two possibile solutions to overcome this limitation:

Solution 1

This simple VBA code when (manually) triggered, will generate the check boxes along with the linked cells.

The code

• From your workbook, press ALT + F11 to access the VBA editor.
• Click on Insert > Module.
• Copy and paste the below code:

Option Explicit

Sub Inserer_Cases_a_cocher_Liees()
Dim rngCel As Range
Dim ChkBx As CheckBox

For Each rngCel In Selection
  With rngCel.MergeArea.Cells
    If .Resize(1, 1).Address = rngCel.Address Then
    'Pour ne pas afficher la valeur de la cellule liée, enlevez l'apostrophe en début de ligne suivante :
      '.NumberFormat = ";;;"
      Set ChkBx = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
      With ChkBx
        'valeur par défaut :
        .Value = xlOff 'pourrait être True ou False
        'cellule liée
        .LinkedCell = rngCel.MergeArea.Cells.Address
        'Texte de remplacement
        '.Characters.Text = "TITI"
        'texte
        '.Text = "Toto" ' ou : .Caption = "Toto"
        'bordure :
        With .Border
          'Style de ligne
          '.LineStyle = xlLineStyleNone 'ou xlContinuous 'ou xlDashDot ou xlDashDotDot ou xlDot
          'couleur
          '.ColorIndex = 3  '3 = rouge
          'épaisseur du trait
          '.Weight = 4
        End With
        'accessibles aussi les propriétés .Locked, .Name, .Enabled etc...
      End With
    End If
  End With
Next rngCel
End Sub

To use this code:

From any sheet of your workbook:

• Select the range of cells where you want to insert the checkboxes,
• Press Alt + F8.
• Select the module and click on Run.

Solution 2

In this example we will use the Worksheet_SelectionChange event as a trigger. This event will automatically run the code each time you select a cell (within a certain range). The proposed code will loop through all the cells from a selected range and if the cell is in "Wingdings" font, it will insert a checkbox.

The code

To insert this code:

• Open the sheet of your choice.
• Right-click on the sheet tab > View Code.
• Copy and paste the below code:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    'Restriction de la plage. Pour une feuille entière mettez une apostrophe en début de la ligne suivante
    If Intersect(Union([A2:A10], [D2:D10]), Target) Is Nothing Then Exit Sub 'plage A2:A10;D2:D10
    If Target.Count = 1 Or Target.MergeCells Then
        If Target.Font.Name = "Wingdings" Then
            With Target    'cellule "liée"
                .Value = Abs(.Range("A1").Value - 1)
                .NumberFormat = """þ"";General;""o"";@"
                Application.EnableEvents = False
                .Range("A1").Offset(, 1).Select
                Application.EnableEvents = True
            End With
        End If
    End If
End Sub

To use this code:

• Select the cell range.
• Apply the the Wingdings font.
• Click anywhere in your worksheet and then on each of the previously selected cells.

Using the code with a protected workbook/sheet

ActiveSheet.Unprotect "admin"</code> If your sheet is protected, you will need to unprotect the code. This can be achieved using the below code: <code basic>ActiveSheet.Protect "admin" 'admin = votre mot de passe

'PUT YOUR CODE HERE