Formulaire Excel d'inscription sans code VBA

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Formulaire Excel sans VBA

Dans cette formation Excel, nous proposons de construire un véritable formulaire d'inscription, sans l'appui du code VBA. L'enjeu consiste non seulement à guider la saisie mais aussi à la contrôler. A l'issue, les informations renseignées doivent être consolidées et archivées en base de données, placée sur une autre feuille du même classeur. Mais nous le verrons, pour cette seconde étape, le code sera nécessaire.



Source et présentation du concept
Comme le code VBA est à contourner dans cette première étape, nous ne pouvons pas envisager d'exploiter les UserForm. La simulation doit s'opérer dans les cellules d'une feuille. Et pour débuter, nous proposons de réceptionner un classeur offrant la structure et quelques fonctionnalités précieuses.

• Télécharger le classeur formulaire-inscription-excel.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous réceptionnons ainsi un classeur composé de trois feuilles nommées respectivement : Formulaire, Archives et Villes.



La première d'entre elles héberge donc le formulaire d'inscription. Et comme vous pouvez le voir, il offre 9 champs destinés à accueillir la saisie de l'utilisateur. Chacune de ces informations doit être contrôlée. Il n'est pas acceptable de polluer la base de données avec de faux profils. Et ces contrôles varient selon la nature de la donnée à renseigner. Un code postal par exemple est nécessairement composé de 5 chiffres, ni plus ni moins.

Sur la droite de ce formulaire, vous notez la présence répétée des noms de champs. Nous les exploiterons pour livrer un résultat intermédiaire qui simplifiera l'inscription en base de données. Nous pourrons masquer ces deux colonnes à l'issue.

• En bas de la fenêtre Excel, cliquer sur l'onglet Archives pour activer sa feuille,

Il s'agit de la base de données, destinée à recevoir toutes les informations des nouveaux inscrits. Cela va de soi, ces inscriptions doivent être empilées les unes sous les autres.

• En bas de la fenêtre Excel, cliquer sur l'onglet Villes pour activer sa feuille,

Cette dernière est particulièrement importante. Elle archive toutes les villes et codes postaux de la région PACA. Nous simulerons donc des inscriptions en conséquence. Elle héberge les travaux que nous avons aboutis lors de la formation précédente, pour extraire dynamiquement toutes les villes d'un code postal.



A ce titre, vous pouvez remarquer la présence de formules en colonne E et H. Le calcul en colonne E consiste à repérer par des numéros, toutes les villes correspondant au code postal saisi depuis le formulaire. Le calcul en colonne H exploite ce repérage intermédiaire pour en fournir l'extraction. Sa plage de cellules est nommée et retravaillée par une formule exploitant la fonction Decaler. Elle se nomme Liste. C'est ce nom que nous devrons exploiter dans le formulaire, pour proposer les villes du code postal tapé par l'utilisateur.

Contrôler une civilité
Il est temps de débuter les travaux de conception. Nous devons valider les informations renseignées, au cours de la saisie. Et nous proposons de travailler sur les champs, en respectant l'ordre imposé.

• En bas de la fenêtre Excel, cliquer sur l'onglet Formulaire pour revenir sur sa feuille,

La civilité est l'une des informations les plus simples à valider. L'entrée ne peut prendre que deux valeurs : Madame ou Monsieur. Il paraît naturel de suggérer ces deux options sous forme de liste de choix. Et pour construire une liste déroulante avec Excel, les données doivent être préalablement inscrites dans des cellules. Et c'est le cas, ces informations existent en cellules N14 et N15.

• Cliquer sur le champ de la civilité à renseigner, soit la cellule C6 pour la sélectionner,
• En haut de la fenêtre Excel, cliquer sur l'onglet Révision pour activer son ruban,
• Dans la section Protéger du ruban, cliquer sur le bouton Oter la protection de la feuille,

Cette protection consiste à préserver la mise en page et la structure du formulaire. Mais elle empêche aussi de poser des règles pour contrôler les données. A l'issue des travaux, nous devrons la réactiver.

• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,

Dans la boîte de dialogue qui suit, l'onglet Options doit être activé.

• Dans la zone Autoriser, choisir Liste,
• Cliquer alors dans la zone de saisie Source qui se propose juste en dessous,
• Sur la feuille Excel, sélectionner les deux cellules N14 et N15,

Nous définissons ainsi de quelle matière doit se nourrir la liste déroulante. Mais ce n'est pas fini. Dans un formulaire digne de ce nom, nous devons guider et contrôler l'utilisateur. C'est exactement la vocation des deux autres onglets : Message de saisie et alerte d'erreur.

• Cliquer sur l'onglet Message de saisie de la boîte de dialogue,
• Dans la zone Message de saisie, saisir le texte suivant : Madame ou Monsieur (Obligatoire),

Grâce à ce réglage, cette indication apparaîtra en amont sous forme d'info-bulle, au clic sur la cellule, pour mieux aiguiller encore l'utilisateur.

• Désormais, activer le troisième onglet de la boîte de dialogue : Alerte d'erreur,
• Dans la zone Titre, saisir le texte Attention par exemple,
• Dans la zone Message d'erreur, inscrire l'indication suivante :

L'information sur la civilité est requise. Vous devez la choisir dans la liste déroulante.

• Dans la zone Style sur la gauche, conserver l'option Stop,

Cette alerte est en effet destinée à se déclencher à validation de la saisie, en cas d'inscription erronée. Il s'agit cette fois d'un contrôle en aval. Avec le style réglé sur Stop, tant que l'utilisateur ne respecte pas les consignes, son information n'est pas prise en compte. Avec les autres options, un message est tout de même adressé, mais l'utilisateur peut forcer l'inscription.

• Cliquer sur le bouton Ok pour valider ce premier contrôle de données,

De retour sur la feuille Excel, la case C6 étant active, vous notez l'apparition du message de saisie, sous forme d'info-bulle en effet. L'utilisateur est donc guidé et pris en main.

• Taper l'information Messieurs par exemple,
• Puis, valider cette saisie à l'aide de la touche Tab du clavier,

Comme vous le constatez, le message d'alerte que nous avons configuré se déclenche instantanément. La donnée n'est pas prévue dans la liste déroulante. Elle contredit donc la règle de validité, engendrant l'apparition du message personnalisé.

• Enfoncer la touche Echap du clavier pour abandonner la saisie,

Rien n'empêche en revanche de valider une cellule vierge. Et cela ne doit pas être autorisé. D'autres contrôles de validité seront donc nécessaires sur certains champs. Nous en profiterons d'ailleurs pour régler des mises en forme conditionnelles. Nous alerterons ainsi visuellement l'utilisateur pour créer un formulaire professionnel et ergonomique.

Contrôler un code postal
Bien qu'un code postal ne soit composé que de chiffres, la donnée n'est en rien un nombre.

• Cliquer sur le champ du CP, soit la cellule E6 pour la sélectionner,

En consultant la section Nombre du ruban Accueil, vous constatez que ce champ est effectivement formaté comme un texte. Si tel n'était pas le cas, le zéro en préfixe de certains codes postaux disparaîtrait. Dans le cas des valeurs numériques, il est considéré comme superflu (6000 au lieu de 06000).

Le contrôle de validité est plus complexe sur une telle information. Nous devons nous assurer que la donnée est nécessairement codée sur 5 caractères. Et dans le même temps, nous devons vérifier que chacun de ces caractères est bien un chiffre, malgré le format en texte.

• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue, choisir Personnalisé,
• Dans la zone Formule, inscrire la syntaxe suivante :

=ET(ESTERREUR(CNUM(E6))=FAUX;NBCAR(E6)=5)



Le contrôle de cette information nécessite en effet une règle particulière. Grâce à la fonction Excel ET, nous recoupons deux conditions à inspecter ensemble. La première consiste à vérifier que le code postal peut bien être converti en un nombre, grâce à la fonction Excel Cnum. Cette dernière retourne une erreur le cas échéant. C'est pourquoi, nous testons son renvoi en l'imbriquant dans la fonction logique EstErreur. La seconde condition consiste à vérifier que la saisie est bien codée sur 5 caractères. C'est la fonction Excel NbCar appliquée sur une cellule, qui retourne le nombre de ses caractères.

• Activer l'onglet Message de saisie de la boîte de dialogue,
• Dans la zone Message de saisie, saisir le texte : Code postal, 5 chiffres obligatoires,
• Activer alors l'onglet Alerte d'erreur de la boîte de dialogue,
• Saisir le titre : Attention,
• Puis, ajouter le message : Un code postal composé de 5 chiffres est nécessaire,
• Valider cette nouvelle règle de validité en cliquant sur le bouton Ok,

De retour sur la feuille, comme précédemment, vous voyez le guide apparaître sous forme d'info-bulle. Si vous tentez d'entrer une information incomplète ou un texte, le contrôle se déclenche aussitôt. Là encore, grâce au style Stop, il n'est pas possible de forcer l'inscription.



L'utilisateur n'a d'autre choix que de corriger la saisie ou de l'abandonner avec la touche Echap. Le même symptôme que précédemment persiste néanmoins. Rien n'empêche l'utilisateur de laisser le champ vierge.

Contrôler la ville associée
Grâce aux travaux de la précédente formation, une fois un code postal validé, nous disposons d'une extraction des villes correspondantes.

• En E6, taper par exemple le code postal : 05500,
• Cliquer ensuite sur l'onglet Villes en bas de la fenêtre Excel,

Nous l'avons évoqué en début de formation, la zone d'extraction s'est construite automatiquement en colonne H. Elle propose toutes les villes du code postal inscrit sur le formulaire. Et nous avions pris soin de transformer cette zone en plage dynamique grâce à un nom retravaillé par une formule. Cette plage est nommée Liste. C'est ce nom que nous devons utiliser pour opérer le contrôle de validité de la ville sur le formulaire.

• En bas de la fenêtre Excel, cliquer sur l'onglet Formulaire pour revenir sur sa feuille,
• Puis, sélectionner le champ de la ville, soit la cellule G6,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue, choisir Liste,
• Dans la zone Source, taper l'expression suivante : =Liste,

Nous faisons ainsi la liaison avec la plage dynamique, reconnue par ce nom.

• Activer l'onglet Message de saisie de la boîte de dialogue,
• Taper le message suivant : Seules les villes proposées dans la liste sont autorisées,
• Activer l'onglet Alerte d'erreur de la boîte de dialogue,
• Taper le titre : Attention,
• Puis, saisir le message : Vous devez choisir l'une des villes proposées,
• Valider cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

L'info-bulle du guide se propose automatiquement comme toujours. Si vous déployez la liste déroulante, vous avez l'heureuse surprise de constater qu'elle s'est chargée dynamiquement. Si vous tentez d'entrer une ville non prévue par la liste, la demande est naturellement rejetée.

Contrôler les saisies standards
Contrôler les informations des champs suivants s'avère plus difficile. Qu'il s'agisse du nom, du prénom ou de l'adresse, il n'y a pas de véritables règles. Tout texte doit être autorisé sur des longueurs variables. Même les chiffres ne doivent pas être interdits en ce qui concerne l'adresse. Nous proposons simplement de nous assurer que la longueur dépasse les 3 caractères. Pour l'adresse, nous pourrions rehausser cette barrière. Mais l'idée est de regrouper ces trois champs dans une même manipulation. Nous adapterons ensuite les messages.

• Cliquer sur le champ du nom de famille pour sélectionner la cellule C9,
• Tout en maintenant la touche CTRL enfoncée, cliquer sur les cellules H9 et C12,

Elles sont désormais réunies dans une même sélection pour grouper les règles.

• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue, choisir Longueur du texte,
• Dans la zone Données, choisir l'opérateur Supérieur à,
• Dans la zone Minimum, taper le chiffre 3,

Il est temps de vérifier que ce réglage est bien commun aux trois zones de texte.

• Cliquer sur le bouton Ok de la boîte de dialogue pour valider cette règle,

Après essai, quelle que soit la zone, si vous tentez d'inscrire une information composée de moins de quatre caractères, la saisie est refusée. Dès lors que la donnée propose plus de trois caractères, elle est acceptée.

Il convient désormais d'adapter les messages de saisie et alertes d'erreur pour chacun. Pour le champ du nom de famille :

• Message de saisie : Votre nom de famille,
• Message d'alerte : Un nom doit proposer plus de 3 caractères,

Pour le champ du prénom :

• Message de saisie : Votre prénom,
• Message d'alerte : Un prénom doit proposer plus de 3 caractères,

Pour le champ de l'adresse :

• Message de saisie : Votre adresse postale,
• Message d'alerte : Le nombre de caractères est insuffisant pour une adresse,

Contrôler la saisie d'une date
La date de naissance est l'information demandée par le champ suivant. Cette donnée doit nécessairement être inscrite dans un format date (jj/mm/aaaa). Excel propose un contrôle de validité dédié.

• Sélectionner le champ naissance, soit la cellule C15,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue, choisir Date,
• Juste en dessous, conserver l'opérateur proposé par défaut : Comprise entre ,
• Dans la zone Date de début, taper : 01/01/1940,
• Dans la zone Date de fin, saisir : 01/01/2050,

Ainsi, en plus du format imposé et contrôlé, nous bornons la date pour exclure les propositions farfelues. Nous prévoyons quelques années dans le futur, pour permettre la portabilité de l'application.

• Activer l'onglet Message de saisie de la boîte de dialogue,
• Entrer l'indication suivante : Votre date de naissance (jj/mm/aaaa), ex : 15/07/1991,
• Activer l'onglet Alerte d'erreur de la boîte de dialogue,
• Entrer un titre et saisir le message : La date proposée n'est pas correcte,
• Puis, valider cette nouvelle règle en cliquant sur le bouton Ok,

Après essai, comme vous le constatez, seule la saisie d'une date dans le format imposé, est autorisée.

Contrôler la saisie d'un mail
L'adresse mail est un champ particulier à valider. Quelques règles s'imposent. Il doit nécessairement proposer une arobase (@) et un point (.). Il apparaît de même opportun d'imposer une certaine longueur. Trois conditions doivent être réunies. Comme nous l'avons fait pour le champ du code postal, nous allons personnaliser cette validation grâce à une formule.

• Sélectionner le champ du Mail, soit la cellule E15,
• Cliquer sur le bouton Validation des données dans le ruban Données,
• Dans la zone Autoriser de l'onglet Options de la boîte de dialogue, choisir Personnalisé,
• Dans la zone Formule, taper la syntaxe suivante :

=ET(CHERCHE('@';E15)>0; CHERCHE('.';E15)>0; NBCAR(E15)>6)

• Activer l'onglet Message de saisie,

Une alerte apparaît indiquant que la formule semble erronée.

• Cliquer sur le bouton Oui pour l'ignorer et poursuivre,
• Taper l'indication : Votre adresse électronique,
• Activer l'onglet Alerte d'erreur et ignorer de nouveau l'alerte,
• Taper un titre et le message suivant : Votre Mail n'est pas conforme,
• Enfin cliquer sur le bouton Ok pour valider la règle et ignorer l'alerte,

Comme vous le remarquez, après essais, et malgré l'alerte d'Excel, seules les adresses dotées d'un point, d'une arobase et de plus de 6 caractères, sont validées. Toutes les autres saisies sont refusées.

La fonction ET est donc utilisée pour recouper les trois conditions énumérées dans ses paramètres. La fonction Cherche renvoie la position d'une occurrence cherchée (@ ou .), dans une chaîne de texte. Il s'agit du champ du mail ici (E15). Si elle retourne une valeur supérieure à 0, nous en concluons que l'information a été trouvée. Enfin et comme précédemment, la fonction NbCar est utilisée pour imposer un plancher de saisie.

Contrôler un numéro de téléphone
Le contrôle d'un numéro de téléphone est semblable à celui d'un code postal. La longueur est fixée à 10 caractères. Chacun d'entre eux est nécessairement un chiffre. Par contre, en l'état, si vous tapez un numéro débutant par 0 dans le champ dédié, il est automatiquement éliminé. Le format de la cellule doit être adapté.

• Sélectionner le champ du téléphone, soit la cellule H15,
• Dans la section Nombre du ruban Accueil, choisir Texte avec la liste déroulante,

Désormais, le zéro en préfixe est conservé.

• Sélectionner le champ du numéro de téléphone, soit la cellule H15,
• Dans le ruban Données, cliquer sur le bouton Validation des données,
• Dans la zone Autoriser de l'onglet Option de la boîte de dialogue, choisir Personnalisé,
• Dans la zone Formule, taper la syntaxe suivante :

=ET(ESTERREUR(CNUM(H15))=FAUX; NBCAR(H15)=10)

Il s'agit donc d'une règle de validité tout à fait semblable à celle du code postal. Nous nous assurons tout d'abord que l'information inscrite peut bien être interprétée comme un nombre. Ensuite, nous vérifions qu'elle est nécessairement codée sur 10 caractères.

• Cliquer sur l'onglet Message de saisie de la boîte de dialogue,
• Ajouter le message suivant : Numéro de téléphone à 10 chiffres accolés,
• Activer l'onglet Alerte d'erreur de la boîte de dialogue,
• Ajouter un titre et l'alerte suivante : Le numéro n'est pas conforme,
• Cliquer enfin sur le bouton Ok de la boîte de dialogue pour valider cette règle,

Après quelques essais d'usage, vous constatez que seules les inscriptions composées de 10 chiffres sont acceptées.

Contrôles et alertes visuelles
Tous les champs du formulaire sont désormais guidés et contrôlés. En revanche, comme nous l'avons déjà évoqué, les champs vides ne sont pas interdits. Et pour cause, avant de débuter l'inscription, le formulaire proposé est naturellement vierge. De telles inscriptions ne doivent pas être autorisées. Tout d'abord, nous proposons de faire réagir visuellement les cellules non conformes, comme le ferait n'importe quel formulaire Web. Nous devons donc exploiter la mise en forme conditionnelle d'Excel. Selon une règle bien définie, elle consiste à adapter dynamiquement la couleur des cellules. Désormais, si une inscription existe, c'est qu'elle a été contrôlée et validée. Le seul défaut concerne un champ vide.

• Cliquer sur le premier champ pour sélectionner sa cellule C6,
• Dans la section Styles du ruban Accueil, cliquer sur le bouton Mise en forme conditionnelle,
• Tout en bas de la liste, choisir Nouvelle règle,
• Dans la boîte de dialogue qui suit, choisir le type : Utiliser une formule pour...,
• Puis, cliquer dans la zone de saisie qui se propose juste en-dessous pour l'activer,
• Saisir la syntaxe suivante : =$C$6='', pour définir le critère de la règle,
• En bas de la boîte de dialogue, cliquer sur le bouton Format,
• Dans la boîte de dialogue qui suit, activer l'onglet Remplissage,
• Dans la palette de couleurs, choisir un rouge pâle et valider par Ok,

De retour sur la première boîte de dialogue, cette dernière offre un résumé de la situation. Elle indique que la cellule doit être repérée sur fond rouge pâle lorsque le contenu de cette dernière est vide.

• Valider cette règle en cliquant sur le bouton Ok de la boîte de dialogue,

Après essais, si vous supprimez la valeur présente en lieu et place, vous constatez que la cellule est instantanément repérée pour en alerter l'utilisateur. En revanche, si vous choisissez une civilité dans la liste, la mise en valeur dynamique disparaît automatiquement. Nous pourrions être tenté de reproduire ces attributs conditionnels de mise en forme, grâce au pinceau du ruban Accueil. Mais en raison de la présence de certaines cellules fusionnées, nous détériorerions la structure du formulaire. C'est pourquoi, il convient de reconstruire cette règle pour chaque champ, en adaptant bien sûr la cellule à vérifier.



A l'issue, comme vous le constatez après quelques tests, tout champ non renseigné est automatiquement repéré. Mais dès lors que la saisie est validée, l'alerte disparaît. Notre formulaire d'inscription prend forme au fur et à mesure. Il devient fonctionnel et ergonomique.

Pour les besoins de l'insertion des données par une action de macro, nous avons préalablement besoin de repérer ces potentielles anomalies, de façon textuelle, dans le tableau situé entre les colonnes M et N. Le critère est identique. Si une cellule est vide, une mention Nok doit être affichée. C'est elle qui doit interdire l'ajout de données.

• Sélectionner la première cellule vide de ce tableau, soit M4,
• Saisir la formule suivante : =SI(C6='';'nok';''),

Lorsque le champ n'est pas renseigné, la mention doit apparaître. Dans le cas contraire, la cellule est conservée vierge. Il s'agit alors de répliquer cette formule en adaptant la cellule du critère pour chaque ligne : E6, G6, C9, G9, C12, C15, E15 et H15.



Désormais, chaque anomalie est explicitement et spécifiquement identifiée. Dans le prochain volet, nous exploiterons tous ces travaux pour archiver les nouveaux inscrits en base de données. Grâce à ces règles de validité exécutées en amont, le rôle du code VBA sera minimisé.