Comment créer un formulaire dynamique et sécurisé sur Excel, sans saisir de code VBA !
Dans ce tutoriel, je vais vous montrer comment créer un formulaire dans Excel pour générer facilement des fiches clients. Ce formulaire sera dynamique et sécurisé afin d’être certain que les données enregistrées répondent à un certain nombre de règles prédéfinies.
Les étapes que nous allons découvrir comprennent la conception de la feuille de calcul avec des champs tels que nom, prénom, adresse, code postal, ville et sexe.
Nous verrons également comment rendre ce formulaire dynamique en ajoutant des cases à cocher et en appliquant une mise en forme conditionnelle pour les éléments sélectionnés.
Enfin, nous verrons comment automatiser le processus d'enregistrement en créant un bouton lié à une macro, que nous ne développerons pas, nous laisserons le soin à Excel de créer celle-ci à notre place
Téléchargement
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :
Tutoriel Vidéo
1. Création du formulaire
Pour commencer, nous créer la feuille de calcul dans laquelle nous allons insérer le formulaire.
Pour expliquer ce que nous allons y insérer, nous allons nous placer sur la cellule C2 pour y saisir le nom du formulaire « Création d'une fiche client » :
Ensuite, dans les cellules de la colonne B, nous allons saisir les titres des différents champs du formulaire :
- B4 : « Nom »
- B6 : « Prénom »
- B10 : « Adresse »
- B12 : « Code Postal »
- B14 : « Ville »
Comme vous pouvez le constater, nous avons laissé une place vide sur la ligne 8, dans laquelle nous viendrons ensuite insérer des cases d’option pour sélectionner le sexe des clients.
Pour identifier rapidement les cellules de saisie des données, nous sélectionnons les cellules C4, C6, C8, C10, C12 et C14 en maintenant la touche [Ctrl] du clavier enfoncée pour leur appliquer un fond de couleur grise depuis le menu Accueil du ruban :
Maintenant, nous allons ajuster la taille des cellules :
- Nous agrandissons les lignes dans lesquelles se trouvent des données pour que celles-ci fassent 27 pixels de haut (ce qui inclut également la ligne 8) :
- Nous abaissons la hauteur des lignes intermédiaires à 11 pixels de haut :
- Et nous élargissons les colonnes B à 100 pixels et C à 230 pixels :
Il ne reste plus qu’à ajuster la position des contenus des cellules des colonnes C à D :
- Nous centrons verticalement le contenu des toutes les cellules, en sélectionnant les trois colonnes, puis en cliquant sur le bouton « Aligner au centre » du menu Accueil :
- Nous appliquons également un léger retrait horizontal pour décoller le contenu des bordures :
- En enfin, nous alignons les intitulés de champs (la colonne B) sur la droite des cellules :
Pour compléter la construction du formulaire, il est maintenant nécessaire d'ajouter des champs de formulaire pour permettre la sélection du sexe de la personne. Cependant, avant cela, il faut activer le menu développeur dans l'interface d'Excel. Ce menu, qui est généralement masqué, offre des fonctionnalités avancées permettant d'étendre les possibilités d'Excel.
Si vous n'avez pas encore activé le menu développeur, voici les étapes à suivre :
- Cliquez sur l'onglet "Fichier" > "Options".
- Sélectionnez l'option "Personnaliser le ruban".
- Dans la partie droite, cochez simplement l'option "Développeur".
- Cliquez sur le bouton [OK] pour valider vos modifications.
Une fois cette étape terminée, le menu développeur sera activé et accessible dans tous vos documents Excel.
Le menu développeur offre de nombreuses fonctionnalités intéressantes, notamment :
- L’insertion d’éléments de construction de formule, ce qui va justement nous intéresser ici,
- Un accès rapide à VBE (Visual Basic Editor), l'éditeur intégré de VBA.
- La possibilité d'exécuter des macros VBA pour automatiser des tâches.
- L'utilisation de l'enregistreur de macro pour créer de nouvelles macros sans avoir à écrire de code (nous aborderons cette fonctionnalité plus tard).
- Et bien d'autres possibilités encore.
L'activation de l'onglet développeur est permanente, ce qui signifie que vous n'aurez à le faire qu'une seule fois, et il sera ensuite disponible dans tous vos documents Excel.
Maintenant que le menu développeur est ajouté, nous allons nous rendre dans le groupe « Contrôles », déployer le menu « Insérer » et choisir d’insérer une « Case d’option » :
Nous pouvons maintenant venir l’insérer au niveau de la cellule C8 :
Ensuite, nous cliquons dessus pour modifier le libellé correspondant :
Enfin, pour lier cet élément avec la feuille de calcul, nous effectuons un clic droit sur celui-ci afin de choisir « Format de contrôle ».
Dans la boîte de dialogue qui s’affiche à l’écran, nous choisissons de lier celui-ci avec la cellule C8 :
Ainsi, lorsque nous validons en appuyant sur le bouton [OK], puis que nous cliquons sur le bouton de l’élément, Excel va automatiquement ajouter « 1 » dans la cellule C8 :
Ici, nous ne le voyons pas, car la case est placée juste au-dessus.
Maintenant, nous allons dupliquer cette case, en la sélectionnant, puis en appuyant sur les touches [Ctrl]+[D], ce qui va nous permettre de créer la case « Homme » :
Maintenant, en sélectionnant cette deuxième option, c’est la valeur « 2 » qui sera insérée dans la cellule C8.
2. Rendre le formulaire dynamique
Le formulaire est maintenant créé, nous allons pouvoir le rendre dynamique, en ajoutant des contrôles sur chaque élément.
Pour cela, nous nous plaçons sur la cellule D8, afin d’y insérer la formule suivante :
=SI(C4="";"";"P")
Cette formule utilise la fonction SI pour déterminer si du contenu a bien été saisi dans la cellule C4. Lorsque ce sera le cas, alors la fonction SI retournera une lettre « P » en majuscule, et dans le cas contraire, elle n’affiche rien.
Si vous avez l’habitude de suivre mes tutoriels, l’utilisation de la lettre « P » devrait vous rappeler quelque chose.
En effet, il suffit maintenant d’appliquer la police de caractères « Wingdings 2 » sur la cellule, pour que le « P » devienne une case à cocher :
Nous pouvons maintenant centrer le contenu de la cellule et agrandir la police de caractère et appliquer un format gras pour rendre cette coche plus lisible :
Maintenant, pour améliorer encore le rendu visuel, nous allons appliquer une mise en forme conditionnelle.
Pour cela, nous sélectionnons la cellule, puis nous nous rendons dans le menu « Accueil » > « Mise en forme conditionnelle » > « Règles de mise en surbrillance des cellules » > « Égal à… » :
Dans la boîte qui s’affiche, il ne reste plus qu’à saisir la lettre « P » et sélectionner le remplissage vert :
La case est maintenant affichée en vert :
Maintenant, nous allons également modifier la mise en forme de la cellule de saisie de valeur.
Pour cela, nous nous rendons dans le menu « Accueil » > « Mise en forme conditionnelle », mais cette fois-ci, nous sélectionnons « Gérer les règles ».
Nous y retrouvons alors la règle que nous venons créer, que nous allons dupliquer en cliquant sur le bouton correspondant :
Une fois la deuxième règle ajoutée, nous modifions la cellule sur laquelle elle doit s’appliquer en remplaçant la référence à la cellule D4 par C4, qui correspond à la cellule active :
Puis, nous double cliquons sur celle-ci pour la modifier.
Sur la boîte de dialogue de personnalisation de la règle de mise en forme conditionnelle, nous choisissons la dernière option « Utiliser une formule pour déterminer pour quelles cellules le format sera appliqué », et nous saisissons la formule :
=D4="P"
Ensuite, nous cliquons sur le bouton « Format » afin de sélectionner une couleur de fond plus claire :
Après avoir validé les fenêtres successives, la saisie dans la cellule est maintenant validée :
Nous pouvons sélectionner ces deux cellules, pour les copier en appuyant sur les touches [Ctrl]+[C], et les coller sur les autres cellules de saisie du formulaire, y compris sur la ligne de sélection du sexe :
3. Règles de validation du formulaire
Maintenant que les cellules sont validées, nous allons pouvoir effectuer un test global, pour vérifier que tous les champs sont valides.
Pour cela, nous nous plaçons sur la cellule D16, et nous saisissons la formule suivante :
Cette formule utilise la fonction NB.SI pour compter le nombre de cellule ayant pour la valeur la lettre « P ».
Maintenant, nous allons pouvoir vérifier si ce nombre est effectivement égal à 6 en complétant la formule comme ceci :
=NB.SI(D4:D14;"P")=6
Cela permet donc d’afficher VRAI dans la cellule lorsque tous les champs sont remplis, et FAUX dans le cas contraire.
Pour obtenir rapidement cette information par la suite, nous allons maintenant affecter un nom à la cellule en le saisissant dans la zone des noms en haut à gauche de la feuille de calcul :
Et maintenant, nous allons masquer le contenu de la cellule en appliquant une couleur de police blanche :
Il ne reste maintenant plus qu’à créer le bouton d’enregistrement du formulaire, en saisissant « Enregistrer » dans la cellule C16, puis en la mettant en forme :
Ici, nous l’affichons en gris, comme si celui-ci était désactivé.
Pour modifier son apparence, nous allons créer une nouvelle règle de mise en forme conditionnelle (menu « Accueil » > « Mise en forme conditionnelle » > « Nouvelle règle ») :
Dans le champ de saisie de la formule, il suffit d’y insérer la formule :
=validation
Ensuite, nous cliquons sur le bouton « Format » pour modifier l’apparence à donner au bouton lorsque celui-ci doit être rendu cliquable, en choisissant par exemple un fond bleu et une couleur de texte blanche :
Pour le moment, le bouton n’est pas cliquable, mais nous y reviendrons un peu plus tard.
4. La base d’enregistrement
En effet, il est maintenant temps de créer la base des enregistrements.
Pour cela, nous insérons une nouvelle feuille de calcul, dans laquelle nous saisissons les en-têtes des colonnes de cette base :
Nous commençons par créer une colonne « ID », puis nous reprenons tous les champs du formulaire.
Ensuite, pour simplifier l’utilisation des données de cette base, nous la convertissons en tableau structuré en cliquant sur les touches [Ctrl]+[L].
Nous cochons bien l’option « Mon tableau comporte des en-têtes », puis nous validons la création du tableau :
Ensuite, sur la première ligne nous allons préparer la récupération des données de chaque colonne.
Ainsi, dans la cellule A1, nous insérons la formule suivante :
=SI(validation;MAX(Tableau1[ID])+1;"")
Cette formule commence par regarder si le formulaire est valide.
Si c’est le cas, alors la fonction MAX récupère l’ID le plus élevé de la base (pour le moment « 0 ») et ajoute 1.
Dans le cas contraire, la cellule n’affiche rien.
Suivant la même logique, nous pouvons maintenant récupérer les valeurs de champ du formulaire :
=SI(validation;Feuil1!C4;"")
Seule la détermination du sexe de la personne vari légèrement, afin de transformer les valeurs « 1 » et « 2 » en « Femme » et « Homme ».
Pour cela, nous saisissons la formule suivante dans la cellule D1 :
=SI(validation;SI(Feuil1!C8=1;"Femme";"Homme");"")
5. Enregistrer les données et purger le formulaire
Et voilà, maintenant que tout est prêt, il ne reste plus qu’à enregistrer le formulaire dans la base !
Pour cela, nous allons commencer par rendre le bouton d’enregistrement cliquable.
Nous revenons sur la feuille de calculs du formulaire, dans laquelle nous insérons une zone de texte (menu « Insérer » > « Texte » > « Zone de texte »), que nous dessinons juste au-dessus de la cellule C16, en maintenant la touche [Alt] du clavier enfoncée :
Nous nous rendons ensuite dans le menu « Format de forme » afin de sélectionner « Aucun remplissage » et « Aucune bordure » :
Puis, pour enregistrer les données, nous effectuons un clic droit sur cette zone, et nous choisissons « Affecter une macro » :
À ce moment-là, nous saisissons un nom que nous allons souhaiter donner à la macro, et nous cliquons sur le bouton « Enregistrer » :
Cela va nous permettre de créer un automatisme, sans avoir à saisir de code VBA, grâce à l’une des fonctionnalités les plus épatante d’Excel : L’enregistreur de macro !
Celui permet en effet de transformer des actions en lignes de code VBA, que nous pourrons ensuite lancer automatiquement en appelant cette dernière :
Ensuite, Excel nous présente une nouvelle boîte de dialogue pour paramétrer l’enregistrement de la macro :
Une fois que nous appuyons sur le bouton [OK], toutes nos actions seront converties en lignes d’instruction VBA.
Voici les opérations à effectuer :
- Nous nous plaçons sur la feuille d’enregistrement des données,
- Nous sélectionnons les cellules de la ligne 4
- Nous appuyons sur [Ctrl]+[+] pour ajouter une nouvelle ligne
- Nous sélectionnons les cellules A1 :G1
- Nous les copions en appuyant sur [Ctrl]+[C]
- Nous sélectionnons les cellules A4 :G4
- Nous affectons un collage spécial des valeurs, en effectuant un clic droit
- Nous revenons sur la feuille du formulaire
- Nous sélectionnons les cellules C4:C14
- Nous appuyons sur la touche [Suppr] du clavier pour supprimer les données du formulaire
- Nous sélectionnons la cellule C4 pour préparer la saisie suivante
En enfin, nous pouvons arrêter l’enregistreur de macro en appuyant sur le carré situé en bas à gauche :
Et voilà, l’enregistrement est maintenant terminé, nous pouvons saisir de nouvelles données puis valider celles-ci en appuyant sur le bouton « Enregistrer » :
