Créer un bouton modifier

Question

Je suis débutante en VBA Excel et je me forme grâce aux différents tutoriels disponibles et grâce à certaines aides venant de forum. Je me permet de vous demander une petite aide. J'ai des données dans un tableau excel. A partir de mon fichier, j'ai créé un formulaire qui me permet d'ajouter d'autres données dans mon tableau. J'aimerais maintenant pouvoir créer un autre bouton. Cela serait le bouton modifié mais cette foi-ci sur ma feuille excel qui après sélection de la ligne à modifier me donnera la main pour le faire. Mais je sais pas trop comment m'y prendre...

MFerrand · Accepted Answer

L'ajout ou la modification d'une base de données constituent 2 opérations quasi identiques qui ne se différencient que par la ligne d'insertion (ligne suivante en cas d'ajout, ligne choisie à modifier sinon), et éventuellement la recherche pour modif. Si tu sélectionnes directement la ligne à modifier, il est facile à partir de là, d'ouvrir ton userform, l'initialiser avec les valeurs de la ligne et mémoriser la ligne pour l'utiliser lors de la validation. Quelques petites remarques cependant sur ton code actuel : - tu as fait beaucoup d'effort sur ta date en TextBox 4, une petite erreur cependant : " jj/mm/aaaa" et "jj/mm/aaaa" sont deux valeurs différentes (les espaces sont des caractères... ) - le soin apporté à la mise en forme de la date ne te dispense pas de convertir la valeur de la TextBox en Date lors de l'affectation, si tu veux éviter des surprises lorsque le jour sera inférieur à 13, il est aussi utile de te signaler que l'instruction With permet d'éviter de nombreuses répétitions dans l'écriture du code, et qu'en outre c'est l'un des principaux moyens d'optimisation du code, ta base étant constituée par un tableau Excel, pourquoi ne pas l'utiliser ! cela permet de définir la ligne d'insertion sans recherche, d'atteindre directement le tableau à partir du nom inséré automatiquement par Excel, et de se passer ainsi de toute référence à la feuille... (si tu utilises ce type de tableaux autant en exploiter les avantages, même si ta ligne d'enregistrement est courte, il demeure plus agréable de l'affecter en bloc que cellule par cellule (en passant par l'intermédiaire d'un tableau). Cordialement...

Juice · Answer

Coucou Ysama et bienvenue sur le Forum !

Coucou le Fofo ! Coucou MFerrand !

Voilà ton fichier modifié

Bon alors je suis parti sur quelque chose de complètement différent de ce que tu avais fait :p

Ici tu joue entre deux UserForm :

L'un est la pour que tu sélectionne la ligne qui t’intéresse en cas de modification ou de suppression de ligne
L'autre est la pour que tu modifie ou que tu insère une nouvelle ligne à ton tableau

Si tu a la moindre question sur le fonctionnement de ces UserForm (aussi bien sur la codification que sur l'utilisation), si tu a des modifications à apporter ou toute autres choses, nous restons à dispo !

Juice ~

136pour-ysama.xlsm (33.94 Ko)

MFerrand · Answer

Dans ton code de validation, par exemple, si tu mets With Worksheets("Feuil1") cela t'évite de le répéter tant que tu n'as pas mis End With : entre ces deux composantes de l'instruction, toutes les expressions précédées d'un point seront référées à la feuille mise sous With.

Mais la rectification indispensable concerne les dates, tu dois les affecter en utilisant une fonction de conversion en donnée de type Date : ... = CDate(TextBox4.Value), pour éviter des surprises...

Pour la suite, si tu souhaites opérer avec un bouton Insérer et un bouton Modifier (la modification prenant en compte la ligne de la cellule active, si celle-ci est à l'intérieur de ton tableau), je t'écrirai le code utilisant le même Userform pour les 2 opérations et avec la même procédure de validation dans le code du Userform (et avec les améliorations que j'ai indiquées...), si tu en es d'accord.

Cordialement.

MFerrand · Answer

Voilà un dispositif élémentaire dans le fichier joint : - 3 boutons sur la feuille : Insérer, Modifier, Supprimer - tu utilises un tableau Excel, un tel tableau dispose de divers propriétés qui peuvent être avantageusement utilisées Il est autoextensible, toute ligne servie sous le tableau y est automatiquement incorporée. Excel introduit aussi automatiquement un nom dans le Gestionnaire de noms, qui correspond à la partie Données du tableau, excluant l'en-tête (DataBodyRange de l'objet ListObject). Ce nom est utilisable en VBA à l'instar d'un nom de plage, il permet de l'atteindre directement sans transiter par la feuille car son nom est unique dans le classeur. - les boutons Modifier et Supprimer utilisent l'emplacement de la cellule active (soit la cellule que tu sélectionnes avant d'actionner le bouton) pour opérer, on s'assure que cette cellule active se trouve bien dans le Tableau et on en définit alors le rang de la ligne dans la zone de Données, ce qui permet de demander à l'utilisateur (Supprimer) confirmation de la suppression en lui précisant le nom et le badge, ou recueillir les données (Modifier) en vue de les modifier, - si la suppression est confirmée, la ligne de données sera supprimée (on évite de supprimer la ligne entière car cela pourrait affecter tes boutons se trouvant sur la feuille, à côté du tableau, et on vérifie que ce n'est pas la dernière ligne de données du tableau car elle ne pourrait être supprimée sans détruire le tableau, donc si le cas survient, on l'efface), - le bouton Modifier, comme Insérer, va charger l'Userform, en l'initialisant en conséquence : Insérer se contente de modifier l'intitulé du formulaire, le bouton Modifier va en outre affecter les données de la ligne choisie aux contrôles du formulaire, et mémoriser le numéro de ligne dans la zone de Données du Tableau dans une variable publique déclarée dans le Module du Userform, - au niveau du Userform, s'il s'agit d'Insérer, on va servir les contrôles, s'il s'agit de Modifer on va modifier les données que l'on souhaite, Je n'ai pas modifié ton système assez sophistiqué de traitement de la TextBox4 concernant la date. J'ai juste substitué une procédure BeforeUpdate à ta procédure AfterUpdate, elle joue sensiblement le même rôle mais permet de renvoyer l'utilisateur sur la TextBox en passant l'argument Cancel à True. - une fois les saisies ou modifications réalisées, la procédure de validation teste qu'aucun contrôle n'est vide, si c'est le cas elle définit la ligne d'insertion en utilisant pour cela la variable publique lni ; si lni est à 0, c'est qu'on est en mode insérer, elle lui affecte le rang de la première ligne qui suit le tableau (défini par .Rows.Count + 1 appliqué à la plage de données ; si lni est &gt;0, c'est qu'on est en mode modifier et l'insertion se fera sur la ligne déterminée lors de l'appui sur le bouton Modifier, - les données sont regroupées dans un tableau (Lng) qui est affecté globalement à la ligne d'insertion dans le Tableau de données. Lors du recueil des données, la date fait l'objet d'un traitement particulier, en raison de la propension de VBA à convertir en Date tout texte pouvant être identifié comme Date, ce qu'Excel fait également, sauf que la reconnaissance de VBA se fait sur le mode américain et risque donc toujours de produire une inversion mois/jour lorsque le jour est inférieur à 13. Pour pallier cet écueil, on opère une conversion explicite (les fonctions CDate ou DateValue forcent la reconnaissance des dates à partir des paramètres régionaux du système, selon des paramètres FR si l'on est en Excel FR. Ceci suffit si la conversion se fait lors de l'affectation à la cellule. Mais en utilisant un tableau on laisse un nouvelle possibilité de conversion sauvage à VBA lors de l'affectation finale. Pour garantir qu'il n'en sera rien, on convertit la date en entier Long, qui est la forme sous laquelle Excel stocke les dates. On complète l'affectation en dotant la cellule d'un format de date car Excel ne pourra plus alors le faire automatiquement. Si tu as d'autres questions sur le code, n'hésite pas à les poser. Cordialement...

MFerrand · Answer

Si tu veux introduire des modifications, essaie d'abord de bien comprendre comment fonctionne ce que tu as déjà en place.

lni est une variable déclarée au niveau module du Userform, elle est déclarée Public pour pouvoir être initialisée de l'extérieur du Userform. Et la procédure qui appelle le Userform pour modifier un enregistrement initialise la variable avec le numéro de ligne de l'enregistrement à modifier.

Son premier rôle est donc de conserver ce numéro pour l'utiliser lors de la validation de la modification.

Mais lorsqu'on insère un nouvel enregistrement, on procèdera également à une validation et on aura besoin d'un numéro de ligne. Plutôt que de l'affecter à une nouvelle variable, celle-ci existant déjà et étant inutilisée lors d'une insertion, peut donc l'être pour jouer le même rôle : se voir affecter le numéro de ligne d'insertion d'un nouvel enregistrement. Ce que fera la procédure de validation.

Mais, 3e rôle, la validation en cas d'insertion ou de modif. se déroule de la même façon, la même procédure peut l'assurer. Il suffit simplement qu'elle sache si elle dispose déjà d'un numéro de ligne (s'il s'agit d'une modif.) ou si elle n'en dispose pas (s'il s'agit d'un ajout), auquel cas elle doit le définir...

Et comment savoir, tout simplement en testant la variable : elle n'a pas été initialisée (valeur 0), on est en ajout et on l'initialise avec le numéro de ligne qui suit le tableau, elle l'a été (valeur >0), on est en modif. donc on a déjà la ligne. On n'a pas besoin d'autre signalisation et la procédure se poursuit identiquement pour les deux cas, en affectant les données à la ligne désignée par lni.

Voilà pour ce qui de la variable et de son rôle dans la validation. Ce qui ne changera pas si tu modifies la taille de ton tableau.

MFerrand · Answer

Deuxième aspect important : tu travailles avec un tableau Excel. Ce tableau a un nom, inséré d'office par Excel, nom qui fait référence à la zone de données du tableau.

[On peut faire de même sans tableau Excel, mais il faut alors nomme soi-même son tableau, ou du moins sa première colonne, ce qui permet le même usage.]

Tu peux voir dans le Gestionnaire de noms, pour Tableau1 (nom donné par Excel, que tu peux éventuellement modifié, mais il faut aussi le modifier dans le code, mieux vaut donc le faire au départ, avant de coder...)

Si tu ajoutes une colonne à ton tableau, tu pourras vérifier que la référence de Tableau1 s'est étendu d'autant, et inclut ta nouvelle colonne.

Donc, de ce côté là, tu n'as rien à faire, ta colonne étant ajoutée, elle est dans le tableau, qui garde le même nom. Lorsqu'on fera référence à une ligne du tableau, la cellule de la nouvelle colonne sera incluse dans cette ligne, sans qu'on ait à modifier quoi que ce soit.

L'affectation des données est réalisée par cette ligne de code :

        .Rows(lni).Value = Lng

.Rows(lni) est une ligne entière de données du tableau, quelle que soit son nombre de cellules, donc on n'y touche pas. Par contre, Lng est un tableau de 4 éléments, si on en a un cinquième, il faudra le dimensionner en conséquence, on y reviendra.

Tu l'as noté, on ne fait plus référence à la feuille dans le code, tu peux changer son nom, déplacer le tableau sur la feuille, aucune incidence sur le code. On s'adresse directement au tableau en utilisant Tableau1, qui nous conduira toujours au tableau où qu'il se trouve.

Actuellement, la zone de données du tableau occupe la plage A2:D8 de la feuille, la ligne 2, de A à D est la ligne 1 de Tableau1. Tu ajoutes une colonne, la plage occupée devient A2:E8, la ligne 2 est toujours la ligne 1 de Tableau1, mais de A à E

Supposons que tu déplaces ton tableau vers le bas et vers la droite, et qu'il occupe alors la plage C10:G17, c'est la ligne 10 de C à G qui sera ligne 1 du tableau.

Mais que cette ligne se trouve ligne 2 ou ligne 10 de la feuille ne nous intéresse plus, si on doit la modifier, ce sera toujours ligne 1 (du tableau). A partir du nom du tableau, on sait toujours quelle est la dernière ligne, et donc quelle est la suivante où l'on fera un ajout.

Lors d'une modification, il faut calculer quelle est la ligne du tableau qu'on modifie pour l'affecter à lni, on la calcule à partir de l'objet tableau complet (ListObject) pour renvoyer sa ligne d'en-tête (HeaderRowRange) en tant que plage (soit un objet Range) :

        ln = ActiveCell.Row - .HeaderRowRange.Row

La ligne à modifier étant choisie par la cellule que tu sélectionnes, le numéro de ligne de la plage d'en-tête dans la feuille soustrait du numéro de ligne de la cellule active (que tu as sélectionnée) nous fournit facilement le numéro de ligne de la sélection dans le tableau Tableau1, dont la ligne 1 se trouve sous la ligne d'en-tête.

En étant toujours en adressage relatif par rapport au tableau, tu peux ignorer la feuille et conserver une grande stabilité de cet adressage lors de modification de position ou taille du tableau car Excel saura toujours où le trouver.

Mais si tu ajouters une colonne et si tu peux compter que cela n'affectera pas certains éléments relatifs au tableau, il y aura tout de même quelques trucs à modifier dans ton Userform d'ajout-modification !

MFerrand · Answer

Sur ton Userform, tu vas devoir ajouter une TextBox correspondant à ta nouvelle colonne. Et le prendre en considération dans le code (je passe sur les aspects dessin).

Le fait qu'il y ait une TextBox de plus : TextBox5, intéresse la procédure de lancement d'une modification.

Elle affecte la valeurs de la ligne à modifier aux TextBox, au moyen d'une boucle de 1 à 4. Ici il suffira donc de passer la boucle de 1 à 5 pour que le reste suive.

Ce qui se répercute de façon analogue sur la procédure de validation.

La variable Lng dont on a déjà parlé doit être dimensionnée Lng(1 To 5) pour inclure le nouvel élément.

S'agissant comme tu l'as dit d'une date de sortie, sa saisie est par définition optionnelle, elle n'intervient normalement pas lorsqu'on insère un nouvell enregistrement, ne sachant pas à l'entrée si et quand il sortira.

Donc cela ne devrait pas entraîner de modif. de la vérification de saisie...

Il faudra l'inclure lors de l'affectation des valeurs des TextBox au tableau Lng, mais en tenant compte qu'il s'agit d'une date et aussi de son caractère optionnel (il peut être manquant).

La boucle d'affectation à Lng doit passer de 1 à 4, à 1 à 5. La condition pour traiter différemment TextBox4 parce qu'il s'agit d'une date ne change pas, le nouveau est une date, mais on doit prendre en compte le caractère optionnel :

        Else
            If Controls("TextBox" & i).Value <> "jj/mm/aaaa" Then _
             Lng(i) = CLng(CDate(Controls("TextBox" & i).Value))
        End If

de façon que l'élément reste vide si pas de date saisie (et convertir en date une chaîne ne représentant pas une date valide provoquerait une erreur).

Comme on l'a déjà dit, le reste de la validation peut se dérouler sans autre modification.

Mais en amont, tu es obligé d'aligner (au moins partiellement) cette Textbox date sur ta date précédente.

Y placer la mention "jj/mm/aaaa" (ta proc. Initialize), la traiter de la même façon (Enter et Exit), pour KeyPress rien d'indispensable, BeforeUpdate vérifiera que c'est une date et permettra la sortie sans saisie.

Cela implique la duplication de procédure quasi-identiques, et à ce stade tu dois te poser la question soit de dupliquer les procédures soit d'en faire des procédures communes appelées par les procédures d'évènements des contrôles.

Pour 2 contrôles, ça se discute, mais s'il n'est pas définitivement assuré que ce sera limité à 2, pour 3 je conseillerais déjà de mettre en place des procédures communes...

Mon conseil principal est donc de ne pas te mettre à introduire des modifications au coup par coup successivement, cela deviendra vite ingérable, mais de concevoir toutes les modifications que tu souhaiteras apporter de façon que le code puisse être ajusté et fonctionnel, et éventuellement prévu pour accepter des modifications mineures sans bouleversement.

Cordialement.

MFerrand · Answer

On code en fonction d'une configuration déterminée, si la configuration est différente on modifie les moyens utilisés, on réadapte les conditions ou on les diversifie, selon le cas on aura intérêt un Select Case plutôt qu'une instruction If, et le cas échéant on ajuste la configuration de façon à se simplifier... Faisant une boucle sur des TextBox, elle n'incluera pas les ComboBox, selon la nature des informations à récupérer dans les Combo, on les traite à part ou on les inclue dans la boucle, soit par renommage des contrôles (qu'il est toujours recommandé de faire, et judicieux de faire selon l'utilisation ultérieure qu'on en aura), soit en utilisant leur propriété Tag, soit par la médiation d'un tableau les listant, etc. Bref, sachant ce qu'on a, et ce qu'on a à obtenir à chaque stade, on combine de façon à atteindre chaque objectif intermédiaire par la voie la plus facile ! Je ne peux pas en dire plus, concrètement, n'ayant pas la configuration sous les yeux... Cordialement...