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 :


Formulaire d'inscription VBA Excel

Nous poursuivons ici la conception de l'application VBA Excel pour l'évaluation des candidats par QCM. Ces questionnaires sont archivés dans une base de données externe. Dans les deux précédentes formations, nous avons tout d'abord programmé des effets d'animation sur le formulaire d'identification. Leur but est de sensibiliser le candidat sur les zones à renseigner avant de débuter. Et puis, nous avons géré les évènements associés aux zones de texte sur le formulaire d'inscription. Leur objectif est de guider l'utilisateur dans l'inscription des informations requises.



Nous devons désormais ajouter ces informations en base de données. Ainsi à l'avenir, le candidat pourra s'identifier. De fait, après vérification des données, il obtiendra l'autorisation de participer à une évaluation.

Sources et présentation de la problématique
Nous devons débuter les travaux depuis un classeur source restituant les développements précédents.

• Télécharger le fichier formulaire-inscription-vba-excel.rar en cliquant sur son lien,
• Décompresser l'archive dans le dossier de votre choix,

Après décompression, vous notez la présence de deux fichiers. Il s'agit tout d'abord du classeur Excel nommé : formulaire-inscription-vba-excel.xlsm. Et puis, il s'agit de la base de données Access nommée : questionnaires-evaluations.accdb. Cette dernière stocke tous les questionnaires dans des tables indépendantes. Nous devrons aussi y archiver les informations et résultats des candidats.

• Ouvrir le fichier formulaire-inscription-vba-excel-dev.xlsm dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Comme vous le constatez, le formulaire d'identification est programmé pour se charger à l'ouverture du classeur. Il n'est pas encore fonctionnel. Nous le développerons dans la prochaine formation. Il est accompagné d'effets d'animations gérés par des incréments de temps dans une boucle VBA.

• Cliquer sur le lien Jamais inscrit ? situé en dessous de la première zone de texte,

Vous affichez ainsi le formulaire d'inscription. La première zone de saisie est active par défaut. De fait, l'indication consistant à guider l'utilisateur disparaît. C'est ainsi que nous avions géré les évènements dans la formation précédente. Mais il s'agit aussi d'un défaut à corriger. Si vous quittez une zone de texte sans l'avoir renseignée, sa couleur de fond change. Il en va demême lorsque l'adresse mail est considérée comme non conforme, ou que l'identifiant est trop court. Nous avions programmé ces alertes pour inciter le candidat à corriger les défauts et à renseigner tous les champs.



Une fois toutes les informations acceptées, l'utilisateur n'aura plus qu'à cliquer sur le bouton Valider pour s'inscrire. C'est précisément le défi de cette formation.

Inscription en base de données externe
Avant d'entrer dans le vif du sujet, nous devons commencer par corriger le petit défaut que nous avons évoqué précédemment. Pour que l'indication du premier champ réapparaisse, son contrôle ne doit pas être activé par défaut. Nous devons donc donner le focus à un autre élément. Le bouton Annuler ou Valider ferait l'affaire. Cette action doit intervenir au chargement du formulaire ou plus précisément sur activation. Cet évènement se nomme Activate.

• Fermer le formulaire d'inscription en cliquant sur la croix de sa fenêtre,
• Basculer dans l'éditeur de code VBA Excel avec le raccourci clavier ALT + F11,
• Dans l'explorateur de projet sur la gauche de la fenêtre, déployer le dossier Feuilles,
• Puis, double cliquer sur l'élément Inscription pour afficher son formulaire à l'écran,
• Double cliquer alors sur un emplacement vide du formulaire,

Nous basculons ainsi dans l'éditeur de code VBA associé à ce formulaire, entre les bornes de la procédure évènementielle UserForm_Click ainsi générée. Cet évènement n'est pas celui que nous cherchons. Le bouton doit être activé au chargement du formulaire et non au clic.

• En haut de l'éditeur, dérouler la seconde liste déroulante et choisir l'évènement Activate,

Nous créons ainsi la procédure évènementielle UserForm_Activate. Elle va permettre de déclencher un code VBA dès que le formulaire est chargé.

• Supprimer la procédure UserForm_Click du Private Sub au End Sub,
• Entre les bornes de la procédure UserForm_Activate, ajouter l'instruction VBA suivante :

Annuler.SetFocus

La méthode SetFocus associée à l'objet bouton Annuler permet de le rendre actif. Ainsi écrite, c'est lui et non le premier champ du formulaire qui sera sélectionné à l'ouverture.

• Enregistrer les modifications et afficher le formulaire Connexion,
• Enfoncer la touche F5 du clavier pour l'exécuter,
• Une fois les animations terminées, cliquer sur le lien Jamais inscrit ?,

Comme vous le constatez, l'information du premier champ apparaît désormais explicitement. Nous avions en effet programmé des événements indiquant à la zone de se vider sur activation. Dès lors, c'est le bouton Annuler qui porte le focus à l'ouverture du formulaire.

• Fermer le formulaire Inscription en cliquant sur la croix de sa fenêtre,

Nous devons maintenant développer le code permettant de valider l'inscription. Ce code doit être déclenché au clic sur le bouton Valider. Il doit attaquer la base de données Access téléchargée en même temps que le classeur Excel. Une référence à une librairie ActiveX est indispensable.

• En haut de l'éditeur de code, cliquer sur le menu Outils,
• Dans la liste, choisir Références,

Comme l'illustre la capture ci-dessous, vous notez la référence à la librairie Microsoft Office Access database engine. Nous l'avons ajoutée en amont. Il s'agit d'une classe externe. En l'instanciant, nous hériterons des propriétés et méthodes permettant de manipuler les enregistrements de bases de données Access.

• Fermer la boîte de dialogue des références,

Tous les tests consistant à contrôler la saisie doivent être refaits. Les couleurs ne sont que des indicateurs visuels. Rien n'empêche l'utilisateur de cliquer pour valider. Si toutes les informations sont correctement remplies, une requête action (Insert Into) doit être exécutée sur la base de données externe. Sa vocation est d'y insérer les nouvelles données pour inscrire le candidat.

• Dans l'éditeur de code VBA Excel, afficher le formulaire Inscription en conception,
• Puis, double cliquer sur le bouton Valider,

Nous générons ainsi la procédure événementielle Valider_Click. C'est en effet au clic sur ce bouton que le code VBA d'insertion doit être déclenché. Nous devons commencer par déclarer les variables nécessaires au traitement.

• Entre les bornes de la procédure événementielle, ajouter les déclarations suivantes :

Dim chemin_bd As String: Dim requete As String
Dim enr As Recordset: Dim base As Database
Dim test As Boolean

La variable chemin_bd, déclarée comme une chaîne de caractères (String), doit mémoriser le chemin d'accès à la base de données. La variable requete doit stocker la syntaxe de la requête SQL pour pouvoir l'exécuter. La variable enr, déclarée comme un objet de type Recordset, doit permettre de manipuler les enregistrements de la base de données. C'est pourquoi nous déclarons l'objet base, comme un objet de type Database, permettant de pointer sur une base de données. Ces deux dernières déclarations sont rendues possibles par l'ajout de la référence citée plus haut. Enfin la variable test est déclarée somme un booléen. Elle doit servir d'indicateur pour confirmer si l'insertion a abouti.

Nous devons désormais refaire tous les tests permettant de contrôler la saisie. Si un champ est mal renseigné, la requête ne doit pas être exécutée. Il s'agit de vérifier qu'aucun champ n'est vide. De plus, aucune zone ne doit proposer l'indication par défaut. Enfin, le mail doit être conforme et l'identifiant suffisamment long.

Nous proposons donc d'imbriquer trois instructions conditionnelles If.

• A la suite du code VBA, ajouter les imbrications conditionnelles suivantes :

If (mel.Text <> "" And Nom.Text <> "" And Prenom.Text <> "" And vid.Text <> "") Then

If (mel.Text <> "Votre adresse Mail" And Nom.Text <> "Votre nom" And Prenom.Text <> "Votre prénom" And vid.Text <> "Votre identifiant de connexion") Then

If (InStr(1, mel.Text, ".") > 0 And InStr(1, mel.Text,"@") > 0 And Len(vid.Text) > 3) Then

End If
End If
End If

Comme nous l'avions fait dans la formation précédente, nous exploitons la fonction VBA Instr. Elle permet de tester la présence du point et de l'arobase passés en troisième paramètre de la fonction. Cette analyse s'effectue dans le champ du mail : mel.Text. Et cette recherche débute à partir du premier caractère (1). Si la position retournée est supérieure à 0, la fonction InStr indique que les occurrences ont été trouvées. De fait, nous considérons que l'adresse saisie est conforme. Nous exploitons de même la fonction VBA Len. Elle retourne la longueur de la chaîne qui lui est passée en paramètre. Si l'identifiant propose plus de 3 caractères, nous l'acceptons.

Lorsque tous ces tests sont passés avec succès, nous pouvons entreprendre la connexion à la base de données.

• A la racine du dossier de décompression, double cliquer sur le fichier questionnaires-evaluations.accdb pour l'ouvrir dans Access,
• Cliquer sur le bouton Activer le contenu du bandeau de sécurité,
• Dans le volet des objets Access sur la gauche de l'écran, double cliquer sur la table msy_inscrits pour l'afficher en mode feuille de données,

Comme vous le constatez, cette table recense les candidats. Elle est composée de quatre champs : inscrit_identifiant, inscrit_nom, inscrit_prenom et inscrit_mail. Il s'agit précisément des informations que nous demandons à l'utilisateur de remplir sur le formulaire Excel. C'est dans cette table donc qu'il va s'agir d'exécuter la requête SQL par le code VBA. Dans le volet des objets Access, vous notez la présence d'autres tables. Pour la plupart, il s'agit des questionnaires. Ainsi le candidat pourra choisir le thème sur lequel il souhaite s'évaluer.

• Revenir dans l'éditeur de code VBA Excel,

Les tests étant passés avec succès, nous devons désormais affecter les variables.

• Dans les bornes de la troisième instruction conditionnelle, ajouter les affectations suivantes :

test = False
chemin_bd = ThisWorkbook.Path & "\questionnaires-evaluations.accdb"

Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT inscrit_identifiant FROM msy_inscrits WHERE inscrit_identifiant='" & vid.Text & "'",dbOpenDynaset)

Nous initialisons la variable booléenne à False. En l'état, l'ajout des données n'a effectivement pas encore eu lieu. Grâce à la propriété Path de l'objet VBA Excel ThisWorkbook, nous récupérons le chemin d'accès au classeur actif. ThisWorkbook est en effet un objet désignant le classeur en cours. Comme la base de données est placée dans le même dossier, il ne reste plus qu'à concaténer ce résultat avec son nom (& "\questionnaires-evaluations.accdb"). Nous obtenons ainsi le chemin d'accès complet à la base de données.

Puis il s'agit d'instancier la classe permettant à nos objets de bases de données d'hériter des propriétés et méthodes nécessaires. La méthode OpenDatabase de l'objet DBEngine, issu de la référence déclarée, permet de pointer sur la base de données qui lui est passée en paramètre (chemin_bd). De fait, l'objet base propose les méthodes pour accéder aux enregistrements. C'est ainsi que nous initialisons l'objet enr. La désormais méthode OpenRecordset de l'objet base permet d'accéder aux enregistrements d'une table, selon une requête Sql qui lui est passée en premier paramètre. En second paramètre, l'attribut dbOpenDynaset indique le mode d'accès dynamique pour manipuler les données.

Dans la syntaxe de la requête, nous prélevons uniquement l'information du champ de l'identifiant (SELECT inscrit_identifiant), dans la table msy_inscrits, pour lequel la valeur correspondrait à celle tapée par le candidat (WHERE inscrit_identifiant='" & vid.Text & "'"). En effet, avant de produire toute insertion, nous devons nous assurer que cet identifiant n'est pas déjà emprunté.

Et c'est ce que nous proposons de faire dans la foulée, en exploitant le résultat retourné par la requête sélection.

• A la suite du code, ajouter les instructions suivantes, toujours dans les imbrications des If :

If (enr.RecordCount = 0) Then

requete = "INSERT INTO msy_inscrits (inscrit_identifiant, inscrit_nom, inscrit_prenom, inscrit_mail) VALUES ('" & vid.Text & "','" & Nom.Text & "','" & Prenom.Text & "','" & mel.Text & "')"
base.Execute requete
test = True

Else

MsgBox "Cet identifiant ne peut pas être utilisé"

End If

Nous l'avons déjà exploitée, c'est la propriété RecordCount d'un objet Recordset qui retourne le nombre d'enregistrements sélectionnés par la requête. Si sa valeur est nulle, elle confirme que l'identifiant n'existe pas. Donc nous pouvons l'insérer. C'est pourquoi, nous produisons la syntaxe de la requête insertion (Insert Into) sur la table msy_inscrits. Nous énumérons d'abord tous les champs à renseigner. Et pour chacun d'entre eux, nous inscrivons les valeurs (VALUES) respectives. Il s'agit de concaténer les différentes informations dynamiques. Chaque valeur de champ doit être encadrée de simples côtes. C'est ensuite la méthode Execute de l'objet Database qui lance l'exécution de cette requête action passée en paramètre. De fait, nous basculons le booléen à True pour indiquer que l'ajout du candidat a eu lieu.

Dans le cas contraire, soit lorsque RecordCount retourne une valeur supérieure à zéro, nous en déduisons que l'identifiant est déjà emprunté. Donc, nous n'exécutons pas la requête. Puis nous en informons le candidat à l'aide d'une boîte de dialogue (MsgBox). Il est ainsi invité à modifier son identifiant de connexion.

Comme vous le savez, toute connexion ouverte doit être fermée.

• Après le End If de la précédente instruction conditionnelle, ajouter les lignes suivantes :

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

La méthode Close des objets Recordset et Database permet de fermer les connexions. Ensuite nous vidons ces objets gourmands de la mémoire en les détruisant (Set enr = Nothing).

Nous devons traiter le cas échéant des trois premières instructions conditionnelles.

• Entre les trois derniers End If, ajouter les instructions suivantes :

Else
MsgBox "Votre adresse mail n'est pas conforme"

End If

Else
MsgBox "Toutes les informations sont requises"

End If

Else
MsgBox "Toutes les informations sont requises"
End If

Enfin, lorsque le candidat a été inscrit avec succès (test = true), nous devons retourner sur le formulaire d'identification en proposant l'identifiant pré-inscrit. L'utilisateur n'aura donc plus qu'à valider pour participer.

• A la toute fin du code, avant le End Sub, ajouter les instructions suivantes :

If (test = True) Then
Connexion.identifiant.Value = vid.Text
Inscription.Hide
Connexion.Show
End If

Il est intéressant de constater à quel point la communication entre formulaires est simple en VBA Excel. Il suffit de pointer sur l'un des contrôles préfixés du nom du formulaire parent (Connexion.identifiant) pour en affecter sa valeur (Value) de l'identifiant validé (vid.Text). Ensuite nous masquons le formulaire d'inscription au profit du formulaire d'identification que nous affichons.

Il ne nous reste plus qu'à tester :

• Enregistrer les modifications et afficher le formulaire Connexion,
• Enfoncer la touche F5 du clavier pour l'exécuter,
• Après les animations, cliquer sur le lien Jamais inscrit ? pour afficher le formulaire Inscription,
• Dans le champ du nom, taper : Aubin,
• Dans le champ du prénom, taper : Marie,
• Dans le champ de l'identifiant, saisir : Marie133,
• Pour l'adresse mail, saisir : AubinMarie@orange.fr,
• Puis, cliquer sur le bouton Valider pour procéder à l'inscription,

Comme vous le constatez, nous sommes instantanément redirigés sur le formulaire d'identification, attestant que l'inscription s'est correctement déroulée. L'identifiant est effectivement pré-inscrit par le code VBA. Le candidat n'a plus qu'à cliquer pour ouvrir sa session et participer. Mais il s'agit de l'enjeu de la prochaine formation. Nous aboutirons le formulaire d'authentification.

Il est intéressant de tester une inscription avec le même identifiant que celui que nous venons d'utiliser. Vous constateriez que VBA nous refoulerait.

• Revenir sur la base de données Access précédemment ouverte,
• Puis, afficher la table msy_inscrits en mode feuille de données,

Comme vous le constatez, le nouveau candidat est bien présent. Toutes les informations ont correctement été ajoutées dans les champs respectifs.

Dans la prochaine formation, afin d'aboutir l'identification, il s'agira de nouveau d'accéder à cette table. L'objectif sera de trouver l'identifiant proposé par le candidat souhaitant s'évaluer. Si la requête retourne une valeur non nulle (RecordCount), nous pourrons lui ouvrir l'accès.



Le code complet de la procédure Valider_Click est le suivant :

Private Sub Valider_Click()
Dim chemin_bd As String: Dim requete As String
Dim enr As Recordset: Dim base As Database
Dim test As Boolean

If (mel.Text <> "" And Nom.Text <> "" And Prenom.Text <> "" And vid.Text <> "") Then

If (mel.Text <> "Votre adresse Mail" And Nom.Text <> "Votre nom" And Prenom.Text <> "Votre prénom" And vid.Text <> "Votre identifiant de connexion") Then

If (InStr(1, mel.Text, ".") > 0 And InStr(1, mel.Text,"@") > 0 And Len(vid.Text) > 3) Then

test = False
chemin_bd = ThisWorkbook.Path & "\questionnaires-evaluations.accdb"

Set base = DBEngine.OpenDatabase(chemin_bd)
Set enr = base.OpenRecordset("SELECT inscrit_identifiant FROM msy_inscrits WHERE inscrit_identifiant='" & vid.Text & "'",dbOpenDynaset)

If (enr.RecordCount = 0) Then

requete = "INSERT INTO msy_inscrits (inscrit_identifiant, inscrit_nom, inscrit_prenom, inscrit_mail) VALUES ('"& vid.Text & "','" & Nom.Text & "','" & Prenom.Text & "','" & mel.Text & "')"
base.Execute requete
test = True

Else

MsgBox "Cet identifiant ne peut pas être utilisé"

End If

enr.Close
base.Close

Set enr = Nothing
Set base = Nothing

Else
MsgBox "Votre adresse mail n'est pas conforme"

End If

Else
MsgBox "Toutes les informations sont requises"

End If

Else
MsgBox "Toutes les informations sont requises"
End If

If (test = True) Then
Connexion.identifiant.Value = vid.Text
Inscription.Hide
Connexion.Show
End If

End Sub