Formulaire VBA d'insertion et de modification des clients

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'insertion

Pouvoir inscrire de nouveaux clients ou candidats de façon ergonomique, mais aussi pouvoir modifier l'information ou la supprimer en toute simplicité, est l'enjeu de cette formation VBA Excel.



L'opérateur doit remplir les champs du formulaire selon les informations requises. Au clic sur le bouton Ajouter, un nouvel enregistrement correspondant doit être créé et ajouté à la file, dans la base de données, située dans la partie inférieure sur la même feuille.

Source et présentation du concept
Comme nous avons déjà apporté des solutions au niveau du contrôle de la saisie en amont, nous proposons de débuter les travaux à partir d'un classeur Excel relativement abouti. Ainsi, nous concentrerons notre attention sur le développement des instructions VBA Excel nécessaires à la manipulation des données.

• Télécharger le fichier formulaire-insertion-vba.rar en cliquant sur ce lien,
• Le décompresser dans le dossier de votre choix,

Comme vous le remarquez, la décompression fournit le classeur Excel accompagné d'un fichier texte implémenté d'une instruction VBA que nous exploiterons.

• Double cliquer sur le fichier du classeur pour l'ouvrir dans Excel,
• Puis, cliquer sur le bouton Activer la modification du bandeau de sécurité,
• En haut de la fenêtre Excel, cliquer sur l'onglet Révision pour activer son ruban,

En consultant la section Protéger de ce ruban, vous constatez que la protection est active sur cette feuille. La saisie n'est autorisée que dans les champs attendant les informations du nouveau client à créer. Il s'agit des zones de saisie situées entre les lignes 3 et 9 et entre les colonnes B et G.

De fait et afin de sécuriser l'information, les cellules des enregistrements à insérer à partir de la ligne 12 sont protégées. Pour agir, le code VBA devra donc être en mesure d'ôter la protection avant traitement et de la restituer à l'issue.

Selon les travaux réalisés au cours de la formation pour bâtir un formulaire d'inscription, les champs de saisie sont soumis à des règles de validité.

• Dans le ruban Révision, cliquer sur le bouton Ôter la protection de la feuille,
• Sélectionner par exemple le champ du téléphone, soit la cellule G9,
• 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,

Comme vous pouvez le voir, une règle de validité personnalisée contrôle la saisie :

=ET(ESTERREUR(CNUM(G9))=FAUX; NBCAR(G9)=9)

L'inscription doit nécessairement avoir lieu sur 9 caractères (NBCAR(G9)=9) et chacun d'entre eux doit être numérique (ESTERREUR(CNUM(G9))=FAUX). Le format spécial appliqué à cette cellule affiche le zéro en préfixe mais ne le comptabilise pas. C'est la raison pour laquelle la cellule doit comporter neuf chiffres et non dix.

• Cliquer sur le bouton Ok de la boîte de dialogue pour revenir sur la feuille Excel,

Grâce à ces règles, à partir du moment où un champ porte une information, c'est qu'elle est jugée conforme. L'ajout d'un nouveau client ne doit et ne peut se réaliser que lorsque tous les champs sont renseignés.

C'est la raison de la présence de calculs intermédiaires entre les colonnes P et Q, sur la droite du formulaire. Un calcul conditionnel est réalisé en colonne P, pour chaque champ du formulaire. Lorsqu'il restitue l'information Nok, il indique que le champ correspondant est vide. Dans le cas contraire, il garde la cellule vide. De fait, un dénombrement de ces mentions est réalisé en cellule P9, grâce à la fonction Excel Nb.Si. Tant que le résultat ne vaut pas zéro (0), le code VBA ne doit pas enclencher le traitement. Nous y ferons donc référence.



Sur la droite du formulaire, vous notez la présence d'une zone de synthèse. Elle est destinée à réagir au gré des enregistrements ajoutés, modifiés ou supprimés.



Ce sont les fonctions Excel Nb.Si et Nb.Si.Ens qui sont exploitées pour dénombrer les enregistrements par catégories.

Enfin, en haut à droite de la feuille, vous remarquez la présence de trois boutons forcément indispensables. Ils doivent nous permettre de manipuler les informations d'inscription. Les intitulés qu'ils portent parlent d'eux-mêmes.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur de code VBA Excel,
• Dans l'explorateur de projet sur la gauche, déployer l'arborescence du dossier Modules,
• Puis, double cliquer sur l'élément Module1,

Comme vous pouvez le voir, ce classeur propose définitivement de riches fonctionnalités puisque deux fonctions sont offertes. Elles se nomment respectivement NvLigne et ClExiste. La première doit être appelée pour récupérer l'indice de ligne à partir duquel la prochaine inscription doit procéder. La seconde, typée comme une fonction Booléenne, doit être appelée pour savoir si le client à créer n'existe pas déjà. A ce titre, nous confirmons une fois encore la permissivité du langage VBA. La première fonction n'est pas typée alors qu'elle devrait l'être. Mais comme les opérations sont réalisées sur des nombres entiers exclusivement, la fonction est typée comme telle implicitement.



Initialisation du code VBA
Les boutons Ajouter et Modifier réalisent quasiment les mêmes traitements. Nous envisageons donc de regrouper les instructions dans une même procédure, avec des nuances de traitements engagés par des tests préalables. Nous optimiserons ainsi le code VBA. A toutes fins utiles et avant cela, il est nécessaire de déclarer une variable publique. Sa vocation est de porter la valeur de l'indice de ligne en cours pour l'enregistrement à modifier ou à supprimer.

• Dans l'explorateur de projet, double cliquer sur l'élément Feuil2 (bd_clients),
• Dans sa feuille de code au centre de l'écran, ajouter la déclaration publique suivante :

Dim ligneSel As Integer

C'est au clic sur l'une des lignes de la feuille que nous récupèrerons l'indice de ligne en cours. C'est par cette méthode en effet que nous proposerons à l'utilisateur de désigner le client à modifier ou à supprimer. Nous initialiserons donc cette variable publique en temps voulu.

• En haut de la feuille de code, déployer la liste déroulante de gauche,
• Dans les suggestions, choisir l'objet Ajouter,

Cet objet désigne le premier bouton de la feuille Excel bd_clients. Cette méthode permet de créer instantanément la procédure événementielle Ajouter_Click. Son code se déclenchera donc au clic sur le bouton Ajouter.

• Déployer de nouveau la liste déroulante de gauche, en haut de la feuille de code,
• Dans les propositions, choisir cette fois l'objet Modifier,

Ce dernier désigne le deuxième bouton de la feuille Excel et nous générons la procédure événementielle Modifier_Click. Son code VBA se déclenchera au clic sur le bouton Modifier.

• Dans la procédure Ajouter_Click, ajouter l'appel suivant :

Private Sub Ajouter_Click()

insertion ('Ajout')

End Sub

• Dans la procédure Modifier_Click, ajouter l'appel suivant :

Private Sub Modifier_Click()

insertion ('Modif')

End Sub

Au clic sur l'un et l'autre bouton, nous appelons une même procédure nommée insertion. Mais comme vous le remarquez, un paramètre différent lui est transmis. C'est lui qui permettra au code VBA de reconnaître l'appelant pour adapter son traitement.

Contrôler la validité de l'inscription
Nous devons donc créer cette procédure. Avant d'agir, sa première tâche consiste à vérifier que tous les champs du formulaire sont correctement remplis. En effet, qu'il s'agisse d'une création ou d'une modification, toutes les informations doivent être renseignées. Et comme nous l'avons expliqué précédemment, c'est la cellule de synthèse en P9 qui ouvre la voie.

• Sous la procédure Modifier_Click, créer la procédure insertion, comme suit :

Private Sub insertion(mode As String)
Dim ligne As Integer: Dim test As Boolean

test = False
If (Range('P9').Value = 0) Then

Else

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

End If

End Sub

Nous la déclarons avec un paramètre typé comme un String. Sa vocation est de récupérer l'information transmise au clic sur les deux boutons précédents. La variable ligne doit être utilisée pour indiquer l'emplacement à partir duquel l'inscription ou la modification doit être effectuée.

La variable test est typée comme un booléen et initialisée à False dans l'enchaînement. Dans le cas d'une inscription, nous l'utiliserons comme indicateur pour confirmer que le client n'existe pas déjà.

Ensuite, nous engageons une instruction conditionnelle. Son test porte sur la fameuse cellule P9. Tant que celle-ci n'est pas nulle, soit tant que les champs ne sont pas conformes, aucun traitement n'est enclenché.

Dans le cas contraire, nous en informons l'utilisateur grâce à la fonction VBA MsgBox. Puis, le traitement est interrompu.

Tester la source à traiter
Désormais, une nuance doit être observée avant d'entreprendre la suite du traitement. S'il s'agit d'une nouvelle inscription, le curseur doit se positionner à la suite, soit sur le nouvel indice de ligne disponible. De plus, une vérification sur l'existence du client doit être opérée. S'il s'agit d'une modification, l'indice de ligne doit être calé sur celui cliqué par l'utilisateur pour désigner le client à modifier.

• Dans l'instruction conditionnelle, avant le else, ajouter les lignes VBA suivantes :

...
If (mode = 'Ajout') Then
ligne = NvLigne
If (ClExiste = True) Then test = True
Else
ligne = ligneSel
End If
...

Nous testons donc tout d'abord le paramètre passé à la procédure. C'est ainsi que nous savons s'il s'agit d'une inscription ou d'une modification. Dans le cas d'une inscription, nous appelons la fonction NvLigne située dans le Module1. Elle retourne le prochain indice de ligne disponible que nous stockons dans la variable ligne. Ensuite, nous appelons la fonction ClExiste. Si elle retourne True, nous savons que le client figure déjà dans la base de données. Nous basculons donc l'état de la variable booléenne test. C'est elle qui décidera de la suite à entreprendre. Dans le cas d'une modification, nous affectons l'indice de ligne à celui stocké en variable publique ligneSel. Ce dernier sera renseigné lorsque nous traiterons la sélection de l'utilisateur sur la feuille.

Maintenant que la ligne est connue, il suffit simplement de prélever les informations des champs du formulaire Excel pour les inscrire dans leurs colonnes respectives.

• A la suite du code, toujours avant le else, ajouter les instructions VBA suivantes :

...
ActiveSheet.Unprotect
If test = False Then
Range('B' & ligne).Value = Range('B3').Value
Range('C' & ligne).Value = Range('D3').Value
Range('D' & ligne).Value = Range('G3').Value
Range('E' & ligne).Value = Range('B6').Value
Range('F' & ligne).Value = Range('D6').Value
Range('G' & ligne).Value = Range('B9').Value
Range('H' & ligne).Value = Range('G9').Value
Range('I' & ligne).Value = Range('D9').Value
Else
MsgBox 'Le client existe déjà'
End If

vider_form

ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
...

Tout d'abord, grâce à la méthode Unprotect de l'objet ActiveSheet, nous ôtons la protection de la feuille. Le code peut désormais entreprendre des modifications sur cette dernière.

Ensuite, nous vérifions l'état de la variable test. Si elle est toujours réglée à false, cela signifie qu'il s'agit d'une modification ou effectivement de l'inscription d'un client n'existant pas encore. Dans ce cas, nous opérons la correspondance entre les champs du formulaire et les cellules de la base de données, pour l'indice de ligne en cours. Dans le cas contraire, nous en informons l'utilisateur, toujours par le biais de la fonction VBA MsgBox.

Enfin et dans tous les cas, nous appelons la procédure vider_form. Celle-ci n'existe pas encore. Nous allons devoir la créer. Sa mission est de vider les champs du formulaire après traitement. Ainsi, une nouvelle modification, suppression ou insertion peut être entreprise. Puis, nous réactivons la protection de la feuille grâce à la méthode Protect de l'objet ActiveSheet. La ligne de cette dernière instruction est donnée dans le fichier texte, à la racine du dossier de décompression. Elle est en effet issue d'une macro automatique enregistrée.

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

Private Sub insertion(mode As String)
Dim ligne As Integer: Dim test As Boolean

test = False
If (Range('P9').Value = 0) Then

If (mode = 'Ajout') Then
ligne = NvLigne
If (ClExiste = True) Then test = True
Else
ligne = ligneSel
End If

ActiveSheet.Unprotect
If test = False Then
Range('B' & ligne).Value = Range('B3').Value
Range('C' & ligne).Value = Range('D3').Value
Range('D' & ligne).Value = Range('G3').Value
Range('E' & ligne).Value = Range('B6').Value
Range('F' & ligne).Value = Range('D6').Value
Range('G' & ligne).Value = Range('B9').Value
Range('H' & ligne).Value = Range('G9').Value
Range('I' & ligne).Value = Range('D9').Value
Else
MsgBox 'Le client existe déjà'
End If

vider_form
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True

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

End If
End Sub

Vider le formulaire
La procédure destinée à vider les champs du formulaire est triviale. Elle consiste à pointer sur chacun des champs grâce à l'objet Range pour réinitialiser les valeurs de chacun.

• Sous la procédure insertion, créer la procédure vider_form, comme suit :

Private Sub vider_form()

Range('B3').Value = '': Range('D3').Value ='': Range('G3').Value = ''
Range('B6').Value = '': Range('D6').Value ='': Range('B9').Value = ''
Range('G9').Value = '': Range('D9').Value =''

End Sub

Nous pouvons déjà réaliser quelques essais à ce stade. La modification d'un client n'est pas encore fonctionnelle. Nous n'avons pas codé la procédure permettant de récupérer l'indice de ligne cliqué. En revanche, l'inscription d'un nouveau client peut être testée.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille bd_clients,
• Remplir tous les champs du formulaire,
• Pour le champ CP, saisir un code postal de la région PACA (04, 05, 06, 13, 83, 84),

La liste des villes est effectivement liée à la saisie du code postal, sur une base de données volontairement restreinte.

• A l'issue, cliquer sur le bouton Ajouter,

Comme vous pouvez le voir, l'inscription est en effet parfaitement fonctionnelle. Toutes les informations s'archivent dans la base de données, à partir de la première ligne ici, puisqu'il s'agit du premier client. A l'issue du traitement, les champs du formulaire sont parfaitement réinitialisés, grâce à la procédure vider_form.

Récolter les données du client cliqué
Pour permettre la modification et la suppression d'un client, il est tout d'abord nécessaire de récolter les informations de ce dernier, en fonction de la ligne cliquée par l'utilisateur dans la base de données. C'est l'évènement SelectionChange associé à la feuille, objet Worksheet, qui permet d'intercepter les sélections de cellules sur une feuille Excel.

• Revenir dans l'éditeur de code VBA Excel sur l'élément Feuil2 (bd_clients),
• Déployer la liste déroulante de gauche, en haut de la feuille de code,
• Dans les propositions, choisir l'objet Worksheet,

Cette action a pour effet de créer la procédure événementielle Worksheet_SelectionChange.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

End Sub

L'objet Target transmis en paramètre est particulièrement précieux. Il s'agit d'un objet Range. Il représente donc une cellule ou un groupe de cellules. En conséquence, il porte avec lui les propriétés permettant de prélever les indices de ligne et de colonne.

Avant de procéder à la restitution des données de l'enregistrement cliqué, dans les cases du formulaire, nous devons prélever l'information de ligne et de colonne de la cellule désignée. Ensuite, pour éviter tout traitement inutile, nous devons nous assurer qu'il s'agit bien d'une cellule placée dans la grille de la base de données.

• Dans les bornes de la procédure événementielle, ajouter les instructions VBA suivantes :

Dim ligne As Integer: Dim colonne As Byte

ligne = Target.Row: colonne = Target.Column

If (ligne >= 12 And colonne >= 2 And colonne <= 9) Then

End If

Nous déclarons tout d'abord les variables nécessaires pour recevoir ces informations d'indices. Grâce aux propriétés Row et Column de l'objet Target transmis en paramètre, nous les stockons. Puis, nous nous assurons que la ligne cliquée est bien supérieure à 12 et que dans le même temps la colonne est comprise entre l'indice 2 et l'indice 9, correspondant à la largeur de la base de données.

Si tel est le cas, nous devons stocker l'information de ligne dans la variable publique. Souvenez-vous, cette donnée est exploitée pour la modification d'un client. Et elle sera aussi utile pour la suppression. Ensuite, il ne reste plus qu'à établir la correspondance entre les cellules de la base de données et celles du formulaire pour y charger les informations correspondantes.

• Dans les bornes de l'instruction conditionnelle, ajouter les traitements VBA suivants :

...
ligneSel = ligne
Range('B' & ligne & ':I' & ligne).Select

Range('B3').Value = Range('B' & ligne).Value
Range('D3').Value = Range('C' & ligne).Value
Range('G3').Value = Range('D' & ligne).Value

If (Len(Range('E' & ligne).Value) = 4) Then
Range('B6').Value = '0' & Range('E' & ligne).Value
Else
Range('B6').Value = Range('E' & ligne).Value
End If

Range('D6').Value = Range('F' & ligne).Value
Range('B9').Value = Range('G' & ligne).Value
Range('G9').Value = Range('H' & ligne).Value
Range('D9').Value = Range('I' & ligne).Value
...

Nous stockons donc tout d'abord l'information de ligne en variable publique (ligne). Ensuite, à des fins visuelles et ergonomiques, nous sélectionnons l'intégralité de la ligne grâce à la méthode Select de l'objet Range. A ce dernier, nous lui passons la plage de cellules sur la largeur de la base de données, pour la ligne en cours. Elle est ainsi explicitement repérée durant les modifications.

Dès lors, nous n'avons plus qu'à faire la correspondance entre les champs du formulaire (Range('B3').Value) et les cellules de la base de données (Range('B' & ligne).Value). Notez néanmoins qu'une vérification est entreprise pour l'information du code postal. En raison du format dans la base de données, le zéro en préfixe peut manquer. Donc si l'information est codée sur quatre chiffres, nous le rajoutons.

Nous pouvons désormais procéder à quelques essais.

• Enregistrer les modifications et basculer sur la feuille bd_clients,
• Renseigner intégralement le formulaire puis cliquer sur le bouton Ajouter,

Ainsi, nous ajoutons un nouveau client à la base de données.

• Cliquer sur une cellule de la ligne du premier client,

Comme vous pouvez le voir, toutes ses informations sont aussitôt diffusées dans les champs du formulaire. De plus, sa ligne reste en évidence, intégralement sélectionnée.

• Modifier l'une de ses données, comme son numéro de téléphone par exemple,
• Puis, cliquer sur le bouton Modifier pour exécuter le traitement VBA,

Remarque : Vous avez sans doute constaté une alternance de couleur opérer au fur et à mesure de l'ajout de clients. Il s'agit d'une règle de mise en forme conditionnelle en vigueur sur les cellules de la base de données. Nous l'avions d'ailleurs démontrée à l'occasion d'une formation Excel. De même, vous notez la parfaite actualisation des résultats de synthèse sur la droite de la feuille.

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

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim ligne As Integer: Dim colonne As Byte

ligne = Target.Row: colonne = Target.Column

If (ligne >= 12 And colonne >= 2 And colonne <= 9) Then

ligneSel = ligne
Range('B' & ligne & ':I' & ligne).Select

Range('B3').Value = Range('B' & ligne).Value
Range('D3').Value = Range('C' & ligne).Value
Range('G3').Value = Range('D' & ligne).Value

If (Len(Range('E' & ligne).Value) = 4) Then
Range('B6').Value = '0' & Range('E' & ligne).Value
Else
Range('B6').Value = Range('E' & ligne).Value
End If

Range('D6').Value = Range('F' & ligne).Value
Range('B9').Value = Range('G' & ligne).Value
Range('G9').Value = Range('H' & ligne).Value
Range('D9').Value = Range('I' & ligne).Value

End If
End Sub

Supprimer un client de la base de données
Pour la suppression d'un client, le procédé est trivial. Il consiste tout d'abord à prélever l'information publique sur la ligne, du au clic utilisateur sur l'enregistrement à effacer de la base de données. Ensuite et afin de ne pas avoir à supprimer la ligne, au risque de casser la structure, il s'agit d'effacer les informations en les écrasant par les cellules du dessous.

• Sous la procédure Worksheet_SelectionChange, créer la procédure Supprimer_Click :

Private Sub Supprimer_Click()
Dim ligne As Integer

ligne = ligneSel
If (ligne > 0) Then
ActiveSheet.Unprotect

vider_form
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End If
End Sub

La procédure peut directement être écrite si le nom de l'objet et son événement sont correctement orthographiés. Mais vous pouvez aussi sélectionner le bouton Supprimer dans la liste de gauche, en haut de la feuille de code. Nous déclarons une variable dans laquelle nous stockons l'information publique sur l'indice de ligne. Avant de procéder, nous nous assurons que ce dernier a bien été initialisé (If (ligne > 0) Then). Avant d'enclencher le traitement VBA de suppression sur cet indice, nous ôtons bien sûr temporairement la protection de la feuille. Et nous prévoyons à l'issue du traitement de la réactiver après l'appel de la procédure vider_form pour réinitialiser les champs du formulaire Excel.

Désormais, avant l'appel de cette procédure vider_form, nous devons enclencher un traitement récursif. Celui-ci doit partir de la ligne sélectionnée. Son objectif est de parcourir tous les enregistrements du dessous, tant qu'ils existent, afin de les remonter d'un cran. De fait, les informations du client seront bien supprimées.

• Avant l'appel de la procédure vider_form, ajouter le traitement VBA suivant :

...
Do While Range('B' & ligne).Value <> ''

Range('B' & ligne & ':I' & ligne).Value = Range('B' & ligne + 1 & ':I' & ligne + 1).Value
ligne = ligne + 1

If (ligne > 10000) Then Exit Do

Loop
ligneSel = 0
...

A partir de la colonne B et pour la ligne désignée (Range('B' & ligne).Value), nous engageons une boucle destinée à vérifier la présence d'une information. En conséquence, tant qu'une donnée client est trouvée, le processus se poursuit sur les lignes du dessous (ligne = ligne + 1). Et ce processus consiste à remonter l'enregistrement suivant (Range('B' & ligne + 1 & ':I' & ligne + 1).Value), sur la ligne du dessus (Range('B' & ligne & ':I' & ligne).Value). A l'issue, donc après la boucle, nous prenons soin de neutraliser la variable publique (ligneSel = 0), pour ne pas risquer une double suppression inopinée.

Il est temps de tester le bon déroulement de cette fonctionnalité.

• Enregistrer les modifications et basculer sur la feuille bd_clients,
• Remplir intégralement le formulaire Excel,
• Puis, cliquer sur le bouton Ajouter pour créer un nouveau client,
• Cliquer alors sur une cellule de l'un des clients du dessus,
• Puis, cliquer sur le bouton Supprimer,

La base de données se restructure et le client désigné est effectivement supprimé. Nous avons donc abouti notre formulaire de création, suppression et modification des clients en VBA Excel.