Excel - Création automatique de cases à cocher liées aux cellules en-dessous

Décembre 2016



Introduction

Vendreur Jack s'est penché sur la question suivante : comment créer, sous Excel, des cases à cocher liées automatiquement à la cellule sur laquelle on a placé la dite case à cocher.
Sources : cette discussion.

En effet, il n'est pas possible sans VBA (langage de programmation sur Excel), de créer plusieurs cases à cocher en même temps. Vous pouvez faire des copié-collés mais si vous voulez lier votre case à une cellule, vois devrez procéder manuellement. Pour chacune de vos copies, il vous faudra sélectionner la cellule à lui lier. Ceci peut en effet représenter un travail fastidieux selon le nombre de cases à cocher.

Rappels

Type de contrôle

Il existe deux types de cases à cocher sous Excel : les cases à cocher de type formulaire et les contrôles ActiveX. Dans cette fiche pratique, nous utiliserons les cases à cocher de type Formulaire.

Ne pas placer trop d'objets dans une feuille Excel

Même s'il s'agit d'une des possibilités d'Excel, il convient toutefois de se méfier des feuilles avec trop d'objets dessus. Excel ne gère pas toujours très bien et, régulièrement, on se retrouve avec un affichage tout pourri, ou, en tout cas, pas toujours comme on l'avait souhaité. D'autant plus si votre classeur est amené à se promener entre plusieurs versions d'Excel.
Considérons donc qu'au-delà de quelques petites dizaines, la méfiance est de mise et la recherche d'une autre solution doit être envisagée pour ne pas risquer d'instabilités.

Qu'est-ce qu'une CheckBox ?

Nous n'allons pas refaire ce que le site de Microsoft décrit déjà en long, large et travers (exemple ICI, mais juste expliquer la possibilité de lier une Case à cocher avec une cellule.
Pour les propriétés de nos checkbox, il y a deux cellules importantes :
  • La cellule de position : celle au-dessus de laquelle nous allons dessiner notre case à cocher,
  • La cellule liée : celle dans laquelle nous allons renvoyer le résultat, la valeur de notre checkbox.

Nota : Dans cette fiche pratique, la cellule liée sera la cellule de position.

Le fait de cocher (ou de décocher) notre case va renvoyer VRAI ou FAUX dans la cellule liée. Malheureusement, Excel n'a pas prévu le cas où nous voudrions mettre en place plusieurs checkbox avec différentes cellules liées (une par checkbox).
Voici donc deux solutions différentes passant toutes les deux par du code VBA (Visual Basic for Application).

Solution n°1 - Avec déclenchement manuel

Ce simple code VBA générera, lors de son déclenchement, l'ajout de cases à cocher liées avec les cellules situées dessous, cellules que vous aurez préalablement sélectionnées.

Le code

Pour l'installer, depuis votre classeur tapez ALT+F11, puis Insertion/Module, dans la fenêtre de code de ce module, copiez-collez ce 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

Nota : Les lignes en vert sont des commentaires. Vous y verrez notamment quelques-unes des propriétés des checkbox que vous pourrez ajouter. Pour cela, il suffit d'enlever l'apostrophe au début de la ligne de code concernée.

Utilisation

Depuis une des feuilles de votre classeur :
  • sélectionnez la plage de cellules dans laquelle vous souhaitez insérer des cases à cocher,
  • Tapez Alt + F8, Choix : Inserer_Cases_a_cocher_Liees, Cliquez sur Exécuter.

Et voilà, vos checkbox ont été insérées aux endroits souhaités et liées aux cellules sur lesquelles elles sont placées.

Solution n°2 - Avec déclenchement automatique

Ici, nous allons travailler avec un des événements déclencheurs de notre feuille Excel, l'événement Worksheet_SelectionChange. Cet événement lance le code qu'il contient, automatiquement, à chaque fois que vous sélectionnez une cellule différente dans la feuille concernée.
Le code proposé va, à chaque fois que vous changez de sélection, boucler sur toutes les cellules d'une plage définie (ou de toute la feuille si vous le désirez) et, si cette cellule est en police "Wingdings", y insèrera une fausse case à cocher. Nous utilisons ici les caractères "þ" et "o" qui, dans cette police, nous renvoie les symboles correspondant à une checkbox cochée ou décochée.

Le code

Pour insérer ce code :
  • placez-vous sur la feuille concernée,
  • clic droit sur l'onglet de cette feuille / Visualiser le code
  • copiez-collez ce 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

Utilisation

  • Sélectionnez les cellules concernées,
  • appliquez-leur la police Wingdings,
  • cliquez n'importe où dans votre feuille, puis sur chacune des cellules choisies précédemment.


Nota : Pour obtenir le nombre de cases cochées, dans une autre cellule, il conviendra d'utiliser la formule : =NB.SI(C3:C14;1), la fonction =SOMME ne vous sera ici d'aucune utilité.

Note - Protection de feuille

Si votre feuille est protégée, il conviendra de la déprotéger par le code. En effet, en cas de protection de feuille, on a le message d'alerte, la coche est basculée mais pas la valeur liée.
Pour déprotéger puis protéger à nouveau une feuille Excel, le code VBA est :
ActiveSheet.Protect "admin" 'admin = votre mot de passe

'ICI VOTRE CODE

ActiveSheet.Unprotect "admin"

Conclusion

Un grand merci à Vendreur Jack pour l'idée et le développement de cette astuce. Un grand merci également à Le Pingou pour sa contre-proposition productive, ainsi qu'à Eriiic pour sa participation active.
Vous trouverez, ci-joint, un classeur exemple, au format Excel 97-2003...

A voir également :

Ce document intitulé «  Excel - Création automatique de cases à cocher liées aux cellules en-dessous  » issu de CommentCaMarche (www.commentcamarche.net) est mis à disposition sous les termes de la licence Creative Commons. Vous pouvez copier, modifier des copies de cette page, dans les conditions fixées par la licence, tant que cette note apparaît clairement.