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 one which we will draw our checkbox. The linked cell : one in which we will return the result of our checkbox.

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

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"

If your sheet is protected, you will need to unprotect the code. This can be achieved using the below code:

ActiveSheet.Protect "admin" 'admin = votre mot de passe

'PUT YOUR CODE HERE

Image © 123rom