Valider un formulaire d'inscription en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Valider l'inscription

Dans la formation Excel précédente, nous avons construit un formulaire d'inscription, directement dans les cellules de la feuille. Nous n'avons donc pas exploité les UserForm. Nous avons mis en place des règles de validité sur chaque champ, afin de guider et contrôler la saisie. Grâce à elles, les renseignements fournis sont forcément conformes. Il ne reste plus qu'à valider l'inscription en archivant les informations en base de données. Des actions de macro auraient pu être envisagées. Mais nous allons le voir, comme tous les contrôles sont parfaitement opérés en amont, il est encore plus simple d'exploiter le code Visual Basic.

Source et problématique
Pour débuter, il convient donc de réceptionner tout d'abord le classeur hébergeant les travaux sur ce formulaire.

• Télécharger le classeur formulaire-inscription-excel.xlsm 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 débouchons directement sur la feuille du formulaire. Les champs sont parés d'une couleur d'arrière-plan rouge-saumon. Il s'agit d'une mise en forme conditionnelle qui persiste, tant que les renseignements conformes ne sont pas fournis.

• Cliquer sur le champ Civilité pour sélectionner sa cellule C6,

Vous notez l'apparition d'un guide sous forme d'info-bulle et d'une flèche proposant de déployer une liste de choix.

• Cliquer sur cette flèche et choisir une civilité dans la liste.

Aussitôt, la couleur d'arrière-plan disparaît. Chaque donnée insérée est contrôlée par une règle de validité. Si elle est autorisée, le champ est considéré comme renseigné. Dans le cas de la civilité, la liste déroulante impose les deux valeurs. Il n'est pas possible d'inscrire un autre choix.

Ensuite, si vous inscrivez un code postal de la région Paca, la liste déroulante des villes se charge automatiquement des communes associées. Le code postal n'est accepté que s'il est nécessairement composé de 5 chiffres.

Bref, tous les contrôles sont en place. Un clic sur le bouton Soumettre, placé en bas à droite du formulaire, droit prélever les informations renseignées, pour les archiver en base de données. La structure de ce tableau est proposée dans la feuille Archives de ce classeur.

Mais, la soumission ne doit intervenir que lorsque toutes les informations ont correctement été remplies. C'est la raison de la présence d'un petit tableau entre les colonnes M et N de la feuille Formulaire. Tant qu'une mention Nok est stipulée en regard de l'un des champs, cela signifie qu'une donnée au moins, n'est pas conforme.

Autoriser la soumission
Nous le disions, l'un des intérêts de cette application est de minimiser le rôle du code VBA. Plutôt que de scruter le petit tableau à la recherche d'une mention Nok, nous proposons de bâtir un calcul de synthèse en amont. Il s'agit de compter ces mentions en cellule M13. Si le résultat retourné vaut 0, VBA saura qu'il peut procéder à l'inscription. Dans le cas contraire, il devra en informer l'utilisateur.

• Sélectionner la cellule M13 de la feuille Formulaire,
• Y inscrire et valider la formule suivante : =NB.SI(M4:M12;'nok'),

Comme vous le savez, Nb.Si est une fonction de dénombrement conditionnel. Elle permet de compter l'information qui lui est passée en second paramètre, sur une plage de cellules, renseignée en premier paramètre.



En l'état, la fonction retourne le chiffre 8, sur les 9 champs à renseigner. Dans ces conditions, le code VBA ne doit pas valider l'inscription. Sa première mission est donc de se référer à cette cellule pour prendre la bonne décision.

Tester la valeur d'une cellule
Nous devons donc exploiter ce résultat dynamique par le code. Une instruction conditionnelle fera l'affaire. Pour l'accueillir, nous devons commencer par créer une procédure. Ensuite, il s'agit de la lier au bouton. Dès lors, un clic sur ce dernier enclenchera son code.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,

Il est aussi possible de cliquer sur le bouton Visual Basic dans le ruban Développeur.

• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans la liste, choisir Module,

Nous créons ainsi une nouvelle page de code vierge qui apparaît au centre de l'écran.

• Y créer la procédure Valider :

Sub Valider()

End Sub

• Entre les bornes de cette dernière, ajouter le code VBA suivant :

Dim ligne As Integer
ligne = 2

If (Range('M13').Value = 0) Then


Else

MsgBox 'Tous les champs ne sont pas correctement renseignés'

End If

Nous déclarons tout d'abord la variable ligne comme un entier. C'est elle qui servira à pointer sur la première cellule vide du tableau de la feuille Archives, pour procéder à l'insertion des données, à la suite des autres. C'est pourquoi nous l'initialisons à l'indice 2, soit l'indice de la première ligne de ce tableau.

Puis, nous réalisons un test sur la cellule M13 de la feuille en cours. Comme vous le savez, l'objet Range permet de désigner une cellule dont les références lui sont passées en paramètre. Sa propriété Value permet d'accéder à son contenu. Ainsi, grâce au test opéré dans l'instruction conditionnelle, nous vérifions si l'inscription est ouverte. Et pour l'instant, nous ne traitons pas ce contexte, mais le cas contraire (else). C'est la fonction VBA MsgBox qui informe l'utilisateur que les données demandées ne sont pas conformes.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + F11),
• Cliquer droit sur le bouton Soumettre,
• Dans le menu contextuel, choisir Affecter une macro,

• Dans la boîte de dialogue qui suit, sélectionner la procédure Valider,
• Puis, cliquer sur le bouton Ok pour créer l'association,

Désormais, un clic sur le bouton Soumettre doit déclencher l'exécution du code VBA de la procédure Valider.

• Cliquer alors sur une cellule vide de la feuille pour désactiver la sélection du bouton,
• Puis, cliquer sur le bouton Soumettre,

Comme vous le constatez, grâce à ce premier bout de développement, la route est barrée et la soumission est sécurisée. Le test sur la cellule M13 de synthèse n'étant pas concluant, VBA déclenche la branche Sinon (Else) de l'instruction conditionnelle. De fait, après la boîte de message, le traitement est interrompu. Aucune inscription n'a lieu.

Détecter la première cellule vide
Maintenant que la sécurité est posée, nous devons nous soucier de l'autre branche de l'instruction conditionnelle. Elle consiste à valider l'inscription. Les données à insérer sont connues. Elles sont toutes placées dans des cellules respectives du formulaire. L'emplacement en ligne pour l'inscription des données sur la feuille Archives, est variable quant à lui. Il dépend des précédentes insertions validées et cumulées. C'est pourquoi, nous avons déclaré la variable ligne. Nous devons l'affecter sur l'indice de la première ligne vide dans ce tableau. Et pour cela, l'astuce consiste à enclencher une boule While permettant de parcourir toutes les cellules, tant qu'elles ne sont pas vides.

• Revenir dans l'éditeur de code VBA Excel (ALT + F11),
• Dans l'instruction conditionnelle, avant le else, ajouter les lignes VBA suivantes :

...
While Sheets('Archives').Cells(ligne,3).Value <> ''
ligne = ligne + 1
Wend

MsgBox ligne
...

Le critère de la boucle est initié sur la cellule de la colonne 3 et de la ligne 2, telle que nous avons initialisé la variable ligne. Il s'agit donc de la cellule C2. Cette cellule est bien pointée sur la feuille Archives, grâce à l'objet Sheets utilisé en préfixe. Tant que la valeur de la cellule n'est pas vide, la variable ligne est incrémentée pour poursuivre l'analyse sur la ligne du dessous. Au sortir de la boucle, nous affichons sa valeur à titre de test. Elle doit normalement porter l'indice de la première ligne vide.

• Enregistrer les modifications et basculer sur la feuille du formulaire,
• Renseigner tous les champs du formulaire,
• Puis, cliquer sur le bouton Soumettre,

La boîte de dialogue apparaît en effet avec le numéro de la première ligne vide, automatiquement détectée. Si vous affichez la feuille Archives, en l'absence d'inscription pour l'instant, vous constatez que ce résultat est cohérent. Nous devons l'exploiter pour inscrire sur cette ligne, toutes les informations de champs, issues du formulaire. En VBA, c'est l'objet Cells qui permet de pointer sur une cellule, en fonction d'indices variables de ligne et de colonne.

• Revenir dans l'éditeur de code Visual Basic Excel,
• Passer la ligne du MsgBox en commentaire en la préfixant d'une apostrophe,
• A la suite du code précédent, toujours avant le else, ajouter les instructions suivantes :

...
Sheets('Archives').Cells(ligne,3).Value = Range('C6')
Sheets('Archives').Cells(ligne, 4).Value = Range('E6')
Sheets('Archives').Cells(ligne, 5).Value = Range('G6')
Sheets('Archives').Cells(ligne, 6).Value = Range('C9')
Sheets('Archives').Cells(ligne, 7).Value = Range('G9')
Sheets('Archives').Cells(ligne, 8).Value = Range('C12')
Sheets('Archives').Cells(ligne, 9).Value = Range('C15')
Sheets('Archives').Cells(ligne, 10).Value = Range('E15')
Sheets('Archives').Cells(ligne, 11).Value = Range('H15')

Range('C6') = '': Range('E6') = '': Range('G6') = ''
Range('C9') = '': Range('G9') = '': Range('C12') = ''
Range('C15') = '': Range('E15') = '': Range('H15') = ''
...

Très simplement, nous réalisons la correspondance entre les cellules de la feuille Archives, repérées par cet indice de ligne prélevé, et les champs de la feuille Formulaire. Pour ces derniers, l'objet Sheets en préfixe n'est pas nécessaire, puisque le code appartient à la feuille qu'il désigne. Une fois l'inscription achevée, nous prenons soin d'effacer complètement le formulaire, en vidant les cellules. Ainsi, une nouvelle inscription peut être enregistrée dans la foulée.

• Enregistrer les modifications et basculer sur la feuille Formulaire,
• Cliquer sur le bouton Soumettre pour valider les précédents renseignements,

Comme vous le remarquez, à l'issue du traitement, les champs sont effectivement vidés. De fait, les couleurs d'alerte se déclenchent de nouveau. Si vous affichez la feuille Archives, vous constatez la présence du nouvel enregistrement, sur la ligne désignée par le code VBA. Si vous réalisez une nouvelle inscription, vous notez qu'elle vient bien s'empiler à la suite dans la base de données.



Enfin, il convient de masquer les deux colonnes M et N de la feuille Formulaire. Notre application est terminée. Nous avons réussi à réaliser tous les contrôles de saisie, sans l'appui du code VBA. De fait, ce dernier est intervenu sobrement pour finaliser le processus.