formateur informatique

Formulaire Excel d'inscription sans code VBA

Accueil  >  Bureautique  >  Excel  >  Excel Avancé  >  Formulaire Excel d'inscription sans code VBA
Livres à télécharger


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

Pour l'intégrer sur votre site internet ou blog, vous pouvez l'embarquer :

Sujets et formations similaires :


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.

Formulaire inscription Excel guidé et contrôlé sans code VBA



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. Nous réceptionnons ainsi un classeur composé de trois feuilles nommées respectivement : Formulaire, Archives et Villes.

Modèle formulaire inscription Excel pour guider et contrôler la saisie sans code VBA

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.

Formule Excel pour extraire de la base de données toutes les villes associées au code postal saisi

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,
Sélectionner les données de cellules Excel pour remplir une liste déroulante de choix

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),
Message Excel pour guider la saisie utilisateur dans cellule grâce aux outils de validation des données

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,
Options et message Excel pour refuser les saisies de données non conformes

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,
Guider saisie dans formulaire inscription Excel avec validation des 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,
Inscription refusée dans cellule Excel car saisie non conforme avec règle de validité

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)

Contrôler validité saisie pour un code postal dans cellule Excel

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.

Code postal refusé dans cellule Excel car saisie incomplète

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,
Liste des villes extraites en fonction du code postal saisi dans cellule 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,
Liste déroulante Excel des villes associées au code postal saisi dans cellule

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,
Contrôler la longueur du texte saisi dans cellules Excel pour refuser informations trop courtes

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,
Règle Excel pour contrôler conformité saisie de date

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,
Formule Excel pour contrôler et valider saisie adresse mail dans cellule

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,
Formater la cellule du numéro de téléphone en texte pour conserver le zéro en préfixe

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,
Contrôler saisie vide par mise en forme conditionnelle Excel

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,
Alerte visuelle de couleur sur cellules restées vides dans formulaire inscription Excel

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.

Champs de formulaire Excel obligatoires repérés par des couleurs de mise en forme conditionnelle

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.

Formules Excel pour identifier les cellules non renseignées dans formulaire inscription

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

 
Sur Facebook
Sur G+
Sur Youtube
Les livres
Contact
Mentions légales



Partager la formation
Partager sur Facebook
Partager sur Google+
Partager sur Twitter
Partager sur LinkedIn