Les contrôles de feuille de calcul vous aident à créer une interface de projet individuelle intégrée directement dans la feuille de calcul, c’est-à-dire une interface aussi proche que possible de l’utilisateur. Ils automatisent le travail de l’utilisateur, le simplifiant ainsi et augmentant l’efficacité du travail. Des contrôles de ce type créent également la protection nécessaire pour vos données.

1 Présentation des commandes de la barre d’outils
Les contrôles font partie intégrante de l’interface graphique de Windows. Des exemples de contrôles sont les boutons de commande, les zones de texte, les listes, les barres de défilement et d’autres éléments d’interface que vous pouvez utiliser pour entrer un nombre, sélectionner une valeur ou effectuer une autre action. Divers contrôles peuvent être placés sur une feuille de calcul Excel. On accède aux contrôles en cliquant sur le bouton Insérer, qui se trouve sous l’onglet Développeur de ruban, dans le groupe Contrôles. Il convient de noter que lorsque vous cliquez sur le bouton Insérer, deux groupes de contrôles sont disponibles : les Contrôles de formulaire et les Contrôles ActiveX, comme le montre la figure suivante.

Le groupe Contrôles de formulaire est principalement destiné à garantir la compatibilité avec les documents des versions antérieures d’Excel (jusqu’à Excel 97) qui utilisent les contrôles correspondants. Ils sont beaucoup moins puissants que les contrôles trouvés dans le panneau de Contrôle ActiveX. Certains de ces éléments ne peuvent pas du tout être utilisés dans les documents Excel récents comme les zones de texte et les zones de liste déroulante. Cependant, ils ont un certain nombre de fonctionnalités que les contrôles ActiveX n’ont pas, par exemple, ils peuvent être placés sur des feuilles de graphique.
Les contrôles ActiveX sont des composants indépendants de diverses applications et peuvent également être utilisés dans Excel.  Le tableau suivante représente une liste des commandes principales et des boutons correspondants de la barre d’outils.

Bouton	Préfixe	Icône	Description
Etiquette ou Label	Lbl		Permet d’ajouter un texte que l’utilisateur ne pourra pas modifier, comme une légende sous un graphique.
Zone de texte ou Textbox	Txt		Contient le texte que l’utilisateur entre ou peut modifier.
Zone de liste déroulante ou Combobox	Cbo		Permet de tracer une zone de liste combinée à une zone de texte.
Zone de liste ou Listbox	Lst		Affiche une liste des éléments que l’utilisateur peut choisir.
Case à cocher ou Checkbox	Chk		Crée une case que l’utilisateur peut facilement cocher pour indiquer si un élément est vrai ou faux.
Case d’option ou OptionButton	Opt		Permet d’afficher plusieurs options parmi lesquelles l’utilisateur ne peut en choisir qu’une seule.
Bouton bascule ou ToggleButton	Tgl		Crée un bouton qui peut être activé et désactivé.
Cadre ou Frame			Permet de créer un groupement graphique ou fonctionnel de contrôles.
Bouton de commande ou CommandButton	Cmd		Crée un bouton que l’utilisateur peut choisir pour exécuter une commande.
Contrôle d’onglet  ou TabStrip			Permet de définir plusieurs pages pour la même zone d’une fenêtre ou d’une boîte de dialogue dans votre application.
MultiPage			Présente plusieurs écrans d’informations sous la forme d’un ensemble.
Barre de défilement ou ScrollBar	Scr		Outil graphique qui permet d’accéder rapidement aux nombreux éléments d’une liste ou à une quantité importante d’informations, d’indiquer la position actuelle sur une échelle.
Toupie SpinButton	Spn		Contrôle de compteur que vous pouvez utiliser avec un autre contrôle pour incrémenter et décrémenter des nombres. Vous pouvez également vous en servir pour faire défiler les valeurs.
Image	Img		Affiche une image graphique d’une bitmap, d’une icône ou d’un métafichier sur votre formulaire. Les images affichées dans un contrôle Image sont uniquement décoratives et utilisent moins de ressources qu’un PictureBox.
Autres contrôles			Affiche une liste de contrôles ActiveX supplémentaires disponibles sur votre ordinateur que vous pouvez ajouter sur un formulaire personnalisé.

1.1 Positionner un contrôle sur une feuille de calcul
La création d’un contrôle sur une feuille de calcul implique deux étapes : placer le contrôle sur la feuille de calcul et le personnaliser.
La personnalisation implique de définir les propriétés du contrôle, en particulier, vous pouvez lier le contrôle et certaines cellules de la feuille de calcul, personnaliser l’apparence du contrôle et ses autres paramètres.
Pour placer un contrôle sur une feuille de calcul, procédez comme suit:
1. Cliquez sur le bouton Insérer de l’onglet Développeur du ruban dans le groupe Contrôles et sélectionnez le contrôle approprié dans le panneau déroulant de l’un des groupes: Contrôles de formulaire ou Contrôles ActiveX. Le pointeur de la souris se transforme en croix.
2. Placez le pointeur de la souris à la position de la feuille de calcul où vous souhaitez placer le contrôle et cliquez sur le bouton gauche de la souris. Le contrôle apparaîtra sur la feuille de calcul.
3. Déplacer les carrés blancs pour contrôler les marqueurs de bordure et changer sa taille à votre guise.
Si vous devez sélectionner des contrôles supplémentaires dans la boîte de dialogue:
1. Cliquez sur le bouton « Autres contrôles »  dans la barre d’outils Contrôles ActiveX comme le montre la figure suivante.

2. Sélectionnez le contrôle requis dans la liste qui apparaît. Le pointeur ressemblera à une croix.
   3. Déplacez le pointeur vers la position sur la feuille de calcul où vous souhaitez positionner le contrôle et cliquez. L’élément apparaîtra sur la feuille de calcul.
   4. Si nécessaire, redimensionnez le contrôle.
   Le contrôle n’est lié à aucun emplacement de la feuille de calcul et peut être déplacé librement vers n’importe quel autre emplacement. Une souris ou un clavier est utilisé pour déplacer l’objet. La souris est plus pratique à utiliser pour les déplacements sur de longues distances.
   Pour déplacer le contrôle avec la souris :
   1. Mettez en surbrillance la commande souhaitée. Pour sélectionner un contrôle ActiveX, cliquez sur le bouton « Mode Création » dans le groupe Contrôles sous l’onglet Développeur, puis sélectionnez le contrôle. L’élément sélectionné a une bordure sur laquelle se trouvent les marqueurs.
3. Faites glisser le contrôle avec la souris vers un nouvel emplacement. Faire glisser l’élément suit soit la bordure de l’élément, soit derrière son image graphique. Si vous essayez de saisir le nom d’un élément, vous pouvez passer en mode d’édition du nom et vous ne pourrez pas faire glisser l’élément.
   Pour sélectionner plusieurs contrôles, sélectionnez chacun d’eux tout en maintenant les touches <Ctrl> + <Shift> enfoncées.
   Pour déplacer le contrôle verticalement ou horizontalement, maintenez la touche <Maj> enfoncée tout en faisant glisser. Pour aligner le contrôle sur les lignes de la grille tout en faisant glisser, maintenez la touche Alt enfoncée. Les effets peuvent être combinés en maintenant enfoncées ces deux touches tout en faisant glisser.
   Le déplacement d’un contrôle à l’aide du clavier est plus pratique si vous avez besoin de positionner précisément l’élément sur la feuille. Pour déplacer un contrôle à l’aide du clavier:
   1. Sélectionnez le contrôle que vous souhaitez déplacer.
   2. Déplacez-le à l’aide des touches <←>, <↑>, <→> et <↓> du clavier.
   Parfois, vous devez faire plusieurs copies du même élément. Pour copier un contrôle:
   1. Mettez en surbrillance la commande souhaitée.
   2. Tout en maintenant la touche <Ctrl> enfoncée, faites glisser l’objet vers l’endroit où vous souhaitez placer la copie. Lorsque vous relâchez le bouton de la souris, une copie de l’objet apparaît à l’emplacement spécifié.

Vous pouvez effectuer diverses opérations avec les contrôles : ils peuvent être regroupés, placés en arrière-plan ou au premier plan, accrochés aux objets, alignés, etc.

Les contrôles sont des objets. Comme tout autre objet, ils ont des propriétés, des méthodes et des événements. Les valeurs des propriétés de contrôle sont définies à la fois dans le code et pendant sa création. Afin de définir les valeurs de propriété au stade de la conception, sélectionnez le contrôle et cliquez sur le bouton Propriétés, qui se trouve sous l’onglet Développeur dans le groupe Contrôles. La fenêtre Propriétés apparaîtra à l’écran comme le montre la figure suivante.

Dans la moitié gauche de cette fenêtre, les propriétés de l’élément sont répertoriées, et dans la moitié droite, il y a des champs de saisie ou des listes déroulantes pour définir les valeurs de ces propriétés.
Comme mentionné précédemment, vous pouvez associer une macro ou une procédure VBA à un contrôle lorsqu’un événement se produit. Le code de procédure qui gère les événements associés au contrôle est tapé dans le module de feuille de calcul sur lequel se trouve le contrôle. Pour accéder à ce module, sélectionnez un contrôle et cliquez sur le bouton Affichage du code, situé sous l’onglet Développeur, dans le groupe Contrôles.
Lorsque vous avez terminé la création du contrôle, n’oubliez pas de quitter le mode Création en cliquant à nouveau sur le bouton Mode Création.

1.2 Votre premier projet avec un contrôle
Nous allons à présent créer notre premier projet avec un contrôle. Sur la feuille de calcul nous placerons un bouton, et en appuyant sur ce  bouton une boîte de dialogue apparait avec le message “Bonjour à tous!”
Alors, suivez ces étapes :
1. Cliquez sur le bouton Bouton de commande (CommandButton) de la barre d’outils Contrôles ActiveX, qui est appelé en cliquant sur le bouton Insérer dans le groupe Contrôles sous l’onglet Développeur du ruban.
2. Dessinez le bouton de commande avec une taille requise sur la feuille de calcul.
3. Sélectionnez le bouton créé. Cliquez sur le bouton Propriétés, situé sous l’onglet Développeur, dans le groupe Contrôles. La fenêtre Propriétés s’affiche à l’écran. Définissez la propriété Name dans cette fenêtre sur cmdBonjour au lieu du CommandButton1. Définissez la propriété Caption sur « Bonjour à tous ».

4. Sélectionnez à nouveau le bouton créé. Cliquez sur le bouton Afficher le code, situé sous l’onglet Développeur, dans le groupe Contrôles. Cela ouvrira Visual Basic Editor et l’éditeur de code crée automatiquement les première et

dernière instructions pour gérer l’événement Click du bouton qui est généré lorsque le bouton est cliqué.

Private Sub cmdBonjour_Click()
End Sub

5. Dans l’evenement cmdBonjour_Click, ajoutez une instruction qui affiche une boîte de dialogue à l’écran.

Private Sub cmdBonjour_Click()
   MsgBox "Bonjour à tous!", vbExclamation
End Sub

Commentaires

■ La notion de MsgBox  et ses propriétés ont été clairement expliqués au chapitre 5.

6. Cliquez sur le bouton « Mode Création », situé sous l’onglet Développeur dans le groupe Contrôles, pour quitter le mode Création.
   Le projet est prêt. Vous pouvez maintenant tester le bouton Bonjour créé. Cliquez dessus, et une boîte de dialogue avec un message d’accueil apparaît à l’écran, comme le montre la figure suivante.

2 Propriétés, méthodes et évènements des contrôles

2.1 Propriétés générales des contrôles                                
Les contrôles ont une grande collection de propriétés qui vous permettent de définir divers paramètres d’un objet depuis son emplacement et sa taille jusqu’au texte et aux graphiques qui y sont affichés. Le tableau suivante répertorie les propriétés générales des contrôles.

2.2 Méthodes de contrôle courantes
Les commandes ont plusieurs méthodes courantes pour déplacer, positionner et mettre au point. Le tableau suivant énumère ces méthodes.

2.3 Événements de contrôle communs
Les contrôles ont une grande collection de procédures capables d’intercepter et de gérer divers événements survenant dans le système et les actions effectuées par l’utilisateur, des clics de souris à la détection d’une erreur. Le tableau suivante suivant répertorie les événements communs aux contrôles.

 3 Le contrôle Bouton de commande (CommandButton)
Le contrôle Bouton de commande   ou CommandButton est principalement utilisé pour lancer l’exécution de certaines des actions déclenchées par un clic sur un bouton, comme le démarrage ou l’arrêt d’un programme, l’impression des résultats, etc. Ainsi, l’événement principal associé à un bouton de commande est l’événement Click. La propriété principale de CommandButton est la propriété Caption, qui renvoie et définit le texte affiché sur la surface du bouton.

3.1 Menu de bouton de commande
Nous allons créer un classeur qui contient trois feuilles de calcul Feuil1, Feuil2 et Feuil3. Le premier contient deux boutons avec les noms des deux autres feuilles comme le montre la figure suivante.

L’appui sur le bouton conduit à l’activation de la feuille du même nom, et lorsque le deuxième bouton est enfoncé, non seulement l’activation est effectuée, mais également le défilement de la feuille de sorte que la cellule spécifiée s’affiche dans le coin supérieur gauche de la fenêtre de la feuille.
À l’aide de la fenêtre Propriétés, définissez leurs valeurs de propriété, comme indiqué dans le tableau suivante.

Dans le module de feuille de calcul Feuil1, entrez le code suivant.

Private Sub cmdFeuil2_Click()
    Worksheets("Feuil2").Activate
End Sub
Private Sub cmdFeuil3_Click()
    Worksheets("Feuil3").Activate
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollRow = 30
End Sub

Commentaires

■ Lorsqu’on appuie sur le bouton « Feuil 2 », la feuille Feuil2 est activée par la méthode Activate de l’objet Sheet.

■ Le défilement de la feuille pour que la cellule spécifiée s’affiche dans le coin supérieur gauche de la fenêtre de feuille est effectué par les propriétés ScrollColumn et ScrollRow de l’objet Window.

■ La propriété ScrollColumn renvoie ou définit le numéro de la colonne de gauche du volet ou de la fenêtre. Sa syntaxe est expression.ScrollColumn et expression représente un objet Window.

 ■ La propriété ScrollRow renvoie ou définit le numéro de la ligne qui apparaît en haut du volet ou de la fenêtre. Sa syntaxe est expression.ScrollRow et expression représente un objet Window.

3.2 Afficher une valeur avec CommandButton

CommandButton vous permet non seulement de naviguer, mais également de saisir des données dans des cellules. Par exemple, vous pouvez utiliser CommandButton pour afficher le montant total, les coûts ou les bénéfices.

Figure: Afficher une valeur avec CommandButton

À titre d’exemple, considérons le problème de trouver la somme totale a + b + c de trois variables avec deux options possibles pour leurs valeurs, données dans le tableau suivante.

Suivons ces étapes pour mettre en œuvre l’exemple décrit :
1. Sur la feuille de calcul, placez les cellules B2, B3 et B4 sous les variables a, b, c.
2. Dans la cellule B5, entrez la formule = SOMME (B2: B4) pour trouver la somme de a + b + c.
3. Dans la plage D3: D5, entrez la première option pour les valeurs des variables a, b, c.
4. Dans la plage E3: E5, entrez la deuxième option pour les valeurs des variables a, b, c.
5. Créez deux boutons et, à l’aide de la fenêtre Propriétés, définissez leurs valeurs de propriété comme indiqué dans le tableau suivante.

6. Dans le module de feuille de calcul Feuil1, tapez le code suivant :

Private Sub cmdVar1_Click()
   Range("B2").Value = Range("D3").Value
   Range("B3").Value = Range("D4").Value
   Range("B4").Value = Range("D5").Value
End Sub
Private Sub cmdVar2_Click()
   Range("B2").Value = Range("E3").Value
   Range("B3").Value = Range("E4").Value
   Range("B4").Value = Range("E5").Value
End Sub

Commentaires

■ Appuyez sur le bouton Option 1 pour lire les valeurs à l’aide de la propriété Value des cellules de la plage D3: D5 et les saisir à l’aide de la propriété Value dans les cellules de la plage B2: B4.

■ L’appui sur le bouton Option 2 provoquera la lecture des valeurs des cellules de la plage E3: E5 et leur saisie dans les cellules de la plage B2: B4.

■ Les notions de Range et Value ont déjà été expliquées dans le chapitre 9.           

3.3 Décorer un bouton de commande avec les images et personnaliser le pointeur de souris
Avec les images, les boutons peuvent prendre un aspect plus présentable. En outre, changer la forme du pointeur de la souris peut également ajouter un attrait visuel à l’interface.
L’image est chargée sur la surface du bouton à l’aide de la propriété Picture. La propriété PicturePosition vous permet de définir la position relative du texte et de l’image affichés sur le bouton. La propriété MousePointer définit le pointeur de la souris. Les valeurs valides pour cette propriété sont répertoriées dans le tableau suivante. Dans le code, les valeurs des propriétés Picture et MouseIcon sont définies par la fonction LoadPicture, dont la valeur de paramètre doit être le nom du fichier graphique correspondant.

Constante	Valeur	La description
FmMousePointerDefault	00	Défaut
FmMousePointerArrow	01
FmMousePointerCross	02
FmMousePointerIBeam	03
FmMousePointerSizeNESW	06
FmMousePointerSizeNS	07
FmMousePointerSizeNWSE	08
FmMousePointerSizeWE	09
FmMousePointerUpArrow	10
FmMousePointerHourglass	11
FmMousePointerNoDrop	12
FmMousePointerAppStarting	13
FmMousePointerHelp	14
FmMousePointerSizeAll	15
FmMousePointerCustom	99	Défini par l’utilisateur

Comme exemple d’utilisation de boutons avec des images, nous allons réaliser un autre exemple, mais cette fois, nous n’entrerons pas des nombres dans les cellules de la feuille de calcul, mais des formules par lesquelles les calculs sont effectués. En résumé, considérons le problème de trouver la valeur des expressions x + y et x – y.

Suivons ces étapes pour mettre en œuvre l’exemple décrite:
1. Sur la feuille de calcul, placez les cellules B1 et B2 sous les variables Valeur1 et Valeur2. Dans la cellule B3, la formule = B1 + B2 ou = B1-B2 sera saisie à partir du code.
2. Créez deux boutons et, à l’aide de la fenêtre Propriétés, définissez leurs valeurs de propriété comme indiqué dans le tableau suivante.

3. Dans le module de feuille de calcul, tapez le code suivant :
   Appuyez sur le bouton Somme pour entrer la formule = B1 + B2 dans la cellule B3 et la ligne Montant dans la cellule A3. Appuyer sur le bouton Différence entraînera l’entrée de la formule = B1-B2 dans la cellule B3 et la ligne Différence dans la cellule A3.

Private Sub cmdSomme_Click()
    Range("B3").Formula = "=B1+B2"
    Range("A3").Value = "Somme"
End Sub
Private Sub cmdDifference_Click()
    Range("B3").Formula = "=B1-B2"
    Range("A3").Value = "Difference"
End Sub

Commentaires

■ Un clic sur le bouton de commande Somme entraine l’entrée de la formule = B1 + B2 dans la cellule B3 et ainsi le calcul de la somme de Valeur 1 et Valeur 2.

■ Un clic sur le bouton de commande Différence Somme entraine l’entrée de la formule = B1 – B2 dans la cellule B3 et ainsi le calcul de la différence entre Valeur 1 et Valeur 2.

■ La propriété Formula renvoie ou définit une valeur de type Variant qui représente une formule. Sa syntaxe est expression. Formule et expression représente un objet Range.

3.4 Bouton interactif avec CommandButton
En contrôlant les événements MouseDown et MouseUp, vous pouvez rendre le bouton plus interactif. Montrons comment cela se fait en utilisant l’exemple d’une application simple. Prenons quelques données sur les ventes de la société “Bristols House”, qui exporte divers produits. Pour une représentation plus visuelle des données dans le tableau des ventes, il est nécessaire de mettre en évidence la ligne avec les ventes maximales en rouge et les lignes dans lesquelles le volume des ventes est supérieur à la moyenne en jaune. Les données de vente changent constamment, vous devez donc créer une application qui automatiserait le processus de mise en évidence des lignes requises avec de la couleur.

Suivez ces étapes pour mettre en œuvre l’exemple décrit.
1. Dans la feuille de calcul, sélectionnez la plage B2: B10 pour les ventes.
2. Entrez la formule pour déterminer les ventes totales = SOMME (B2: B10) dans la cellule B11.
3. Créez un bouton CommandButton et, à l’aide de la fenêtre Propriétés, définissez ses valeurs de propriété comme indiqué dans le tableau suivante.

4. Dans le module feuille de calcul Feuil1, tapez le code suivant :

Private Sub cmdActualiser_MouseDown(ByVal Button As Integer, _
      ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   With cmdActualiser
      .Font.Bold = True
      .Font.Size = 16
      .ForeColor = vbRed
      .Shadow = True
      .BackColor = vbBlue
   End With
End Sub
Private Sub cmdActualiser_MouseUp(ByVal Button As Integer, _
      ByVal Shift As Integer, ByVal X As Single, ByVal Y As Single)
   With cmdActualiser
      .Font.Bold = False
      .Font.Size = 12
      .ForeColor = vbBlack
      .Shadow = False
      .BackColor = vbGreen
   End With
   FautActualiser
End Sub
Sub FautActualiser()
   Dim m, a As Double
   Dim i As Integer
   With WorksheetFunction
      m = .Max(Range("B2:B10"))
      a = .Average(Range("B2:B10"))
   End With
   For i = 2 To 10
      With Range(Cells(i, 1), Cells(i, 2)).Interior
         If Cells(i, 2).Value = m Then
            .Color = RGB(255, 0, 0)
         ElseIf Cells(i, 2).Value >= a Then
            .Color = RGB(255, 255, 0)
         Else
            .ColorIndex = xlColorIndexNone
         End If
      End With
   Next
End Sub

Commentaires

■ Le fait d’appuyer sur le bouton Actualiser entraînera le recalcul et la l’actualisation du tableau.

■ Les procédures de gestion des événements MouseDown et MouseUp programment une apparence différente des boutons, à savoir le style, la taille et la couleur de la police (les propriétés Bold et Size de l’objet Font et la propriété ForeColor du bouton de commande), la couleur d’arrière-plan et la sortie de l’ombre (les propriétés BackColor et Shadow du bouton de commande). La modification de ces propriétés du bouton entraîne une interactivité supplémentaire.

■ La propriété ForeColor permet spécifier la couleur du texte dans un contrôle ActiveX. La syntaxe la propriété ForeColor est expression.ForeColor, où  expression qui représente généralement un contrôle ActiveX comme CommandButton.

■ La propriété BlackColor définit la couleur de remplissage d’un contrôle ActiveX.

■ La propriété Shadow définit l’effet d’ombre appliqué à un contrôle ActiveX

■ La feuille de calcul est recalculée dans la procédure FautActualiser.

■ L’objet WorksheetFunction est une propriété de l’objet Application. C’est un conteneur des fonctions de feuille de calcul.

■ Les valeurs maximale et moyenne sont trouvées à l’aide des fonctions de feuille de calcul intégrées dans un objet WorksheetFunction.

■ La couleur des cellules est définie par la propriété Color de l’objet Interior.

■ La suppression du remplissage d’une cellule est effectuée en définissant la valeur de la propriété ColorIndex sur xlColorIndexNone.

■ La propriété ColorIndex renvoie et définit la couleur de la bordure, de la police ou de l’intérieur. La couleur est spécifiée sous la forme d’une valeur d’index dans la palette de couleurs en cours, ou comme une des constantes XlColorIndex  : xlColorIndexAutomatic ou xlColorIndexNone.