Journal des modifications
Bonsoir, bonjour,
je dois régulièrement transmettre mon classeur excel à une autre personne qui doit ensuite mettre à jour sa base de données. Ce que je souhaiterais c'est créer une feuille de garde avec un petit journal des modifications, qui indiquerait à la personne l'endroit où les modifications ont été faites, la date, qui (utilisateur windows), et quel type de modification a été apportée (suppression, ajout, modification)
Je ne sais pas comment indiquer à VBA les actions suivantes :
- lorsqu'une ligne a été supprimé dans les autres feuilles, indiquer "Elément supprimé" (dans la colonne de la feuille de garde "type de modification")
- lorsqu'un ajout est fait (n'importe lequel, même insertion de ligne), notamment une ligne entière, indiquer "Elément ajouté" (idem)
- lorsque le contenu d'une cellule a été modifié, indiquer "Elément modifié" (idem)
- lorsque je double click dans une cellule sans rien y ajouter, mon code m'indique quand même : " ", alors que je souhaiterais qu'il ignore cette action.
voici le code que j'ai placé dans ThisWorkbook :
Private Sub Workbook_Sheetchange(ByVal vFeuille As Object, ByVal vCible As Range)
If vFeuille.Name <> "Journal" Then
Application.EnableEvents = False
ValSaisie = vCible
vLigne = Application.CountA(Sheets("Journal").Range("b:b")) + 1
Sheets("Journal").Cells(vLigne, 2) = Now
Sheets("Journal").Cells(vLigne, 3) = vFeuille.Name
Sheets("Journal").Cells(vLigne, 4) = vCible.Address
If ValSaisie = "" Then
Sheets("Journal").Cells(vLigne, 5) = "Élément supprimé"
Else: Sheets("Journal").Cells(vLigne, 5) = "Élément modifié"
End If
Sheets("Journal").Cells(vLigne, 6) = Environ("username")
Application.EnableEvents = True
End If
End Sub
je suis un peu au bout du rouleau, je sais pas si quelqu'un a une solution sous la main, ou une idée, cela me redonnerait courage
Bonjour Magnanaforever le forum
dans ton fichier tu as combien de feuille??
si tu as une seule feuille, il serait à mon sens préférable de créer un classeur de comparaison qui serait utilisé par le destinataire pour comparer les deux fichiers le tien et le sien
enfin ce n'est qu'une idée!!!
Car ta méthode ne permet pas d'y voir clair, car le destinataire sera forcé à chaque fois, d'aller d'une feuille à l'autre pour savoir quoi modifier et surtout risque de se tromper en recopiant
Le plus simple serait que tu passes ton fichier que je puisse voir et te confirmer
Ou aussi si ton fichier ne comporte pas de macro, utiliser ton fichier en googlesheet, là plus de souci tu modifies et l'autre lui à le modifications en direct?
voilà mon avis
a+
Papou
Salut paritec,
merci pour ta réponse
oui il y a près de 20 feuilles dans le classeur
la personne qui reçoit mes infos souhaite avoir un récap.
voici mon classeur, ce serait génial d'avoir une solution, là j'essaie avec un formulaire
Re magnaforever le forum
Je suis pas sur mon pc pour le moment mais le fichier de ton destinataire il a un fichier rigoureusement identique à ton classeur ???? 20 feuille etc etc
A te relire
Papou
Ps: je regarderai au plus tard demain matin et je t’informe
Re paritec,
non
Dans le classeur originel, les autres feuilles, celles où sont les infos, sont différentes de celles de mon fichier tests.
elles sont pleines de mise en forme, couleurs, etc. et il y a tellement d'infos que je préfère que mon code n'y touche pas
la feuille "Journal" est conçue pour être un "historique" sans les détails, vraiment sommaire en quelques sortes.
c'est vraiment difficile à écrire comme code je trouve
par contre, la feuille "Journal" sera identique
pas de panique, ne te plie pas en 10 puissance 6
Bonjour MañanaForever & Paritec
Mouais, sujet fort intéressant ! Le plus compliqué est de piéger les ajouts ou les suppressions de lignes.
Il faut sans doute faire une capture de la touche.
Je vais réfléchir ... même si je pense qu'il risque d'y avoir des trous dans la raquette, enfin on verra. Avec une protection de la feuille on devrait contrôler cela.
Il faut sans doute faire une capture de la touche.
Bon, ce n'est pas suffisant car la suppression ou l'ajout d'une ligne peut se faire par clic droit de la souris !
Il faut donc a minima protéger la feuille et pour les ajouts ou modifs créer 2 boutons spécifiques pour ce faire.
Dans ta macro événementielle, il faut tester toutes les cellules de vCible quand on modifie plusieurs cellules en même temps exemple effacement ou recopie.
Private Sub Workbook_Sheetchange(ByVal vFeuille As Object, ByVal vCible As Range)
If vFeuille.Name <> "Journal" Then
Application.EnableEvents = False
For Each cel In vCible
ValSaisie = cel.Value
vLigne = Application.CountA(Sheets("Journal").Range("b:b")) + 1
Sheets("Journal").Cells(vLigne, 2) = Now
Sheets("Journal").Cells(vLigne, 3) = vFeuille.Name
Sheets("Journal").Cells(vLigne, 4) = cel.Address
If ValSaisie = "" Then
Sheets("Journal").Cells(vLigne, 5) = "Élément supprimé"
Else: Sheets("Journal").Cells(vLigne, 5) = "Élément modifié"
End If
Sheets("Journal").Cells(vLigne, 6) = Environ("username")
Next
Application.EnableEvents = True
End If
End Sub
Je regarde pour proposer 2 boutons ajout ou suppression ligne/colonne.
EUREKA !! Je suis passé par un formulaire et suis assez content du résultat :
Donc dans chaque feuille sauf celle du "journal", je code :
vPrivate Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:XFD1048576")) Is Nothing Then
UfSpecifier.Show
End If
End Sub
Puis, dans le formulaire :
Private Sub CbAnnuler_Click()
Unload Me
MsgBox ("Attention, la dernière modification n'a pas été Archivée dans le Journal !!!!")
End Sub
Private Sub CbArchiver_Click()
'(note) Sélectionner la dernière ligne du tableau
Dim vLigne As Integer
vLigne = Application.CountA(Sheets("Buvard").Range("a:a")) + 1
'(note) Archiver les données du formulaire
Sheets("Buvard").Cells(vLigne, 1) = TxbDate.Value
Sheets("Buvard").Cells(vLigne, 2) = TxbAdresseFeuille.Value
Sheets("Buvard").Cells(vLigne, 3) = TxbAdresseCible.Value
For Each Ctrl In FType.Controls
If TypeOf Ctrl Is MSForms.OptionButton Then
If Ctrl.Value = True Then
Sheets("Buvard").Cells(vLigne, 4) = Ctrl.Caption
Exit For
End If
End If
Next Ctrl
Sheets("Buvard").Cells(vLigne, 5) = TxbAuteur.Value
Sheets("Buvard").Cells(vLigne, 6) = TxbCommentaires.Value
'(note) Tri du journal par ordre chronologique
Sheets("Buvard").Activate
Range("Tableau3[[#Headers],[DATE_MODIF]]").Select
ActiveSheet.ListObjects("Tableau3").ShowAutoFilterDropDown = True
ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort.SortFields. _
Add2 Key:=Range("Tableau3[[#All],[DATE_MODIF]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.ListObjects("Tableau3").ShowAutoFilterDropDown = False
Sheets(TxbAdresseFeuille.Value).Activate
'(note) Fermer le formulaire
Unload Me
End Sub
Private Sub ObAjout_Change()
If ObAjout.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub ObModification_Change()
If ObModification.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub ObSuppression_Change()
If ObSuppression.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub TxbCommentaires_Change()
TxbCommentaires.MultiLine = True
End Sub
Private Sub UserForm_Initialize()
TxbDate.Value = Now
TxbDate.Enabled = False
TxbAdresseFeuille = ActiveSheet.Name
TxbAdresseFeuille.Enabled = False
TxbAdresseCible = Selection.Address
TxbAdresseCible.Enabled = False
TxbAuteur.Value = Environ("username")
TxbAuteur.Enabled = False
End Sub
Il me reste deux soucis :
- L'adresse de la cellule/plage modifiée est la bonne que si je valide avec : "Ctrl Enter" (le "Shift Enter", le "Enter" ou le "Tab", retourne une mauvaise adresse)
- si je modifie une de mes feuilles et que je clique "Annuler" dans mon formulaire, cela me fait une "modification" dans ma feuille qui n'est pas dans le journal (j'ai juste mis un Msgbox pour prévenir)
- je vais encore mettre une protection
Merci les cocos, amigos !!! vous m'avez moralement beaucoup aidé !!
je bois une bierre à la vôtre !
aplush
ah oui, petit détail pour la lecture, j'ai renommé ma feuille "Journal" en "Buvard", entre le code du premier message et le code du dernier... mais je parle de la même chose
Encadre ton code par les balises </>
pour qu'il soit bien lisible
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1:XFD1048576")) Is Nothing Then
UfSpecifier.Show
End If
End Sub
Puis, dans le formulaire :
Private Sub CbAnnuler_Click()
Unload Me
MsgBox ("Attention, la dernière modification n'a pas été Archivée dans le Journal !!!!")
End Sub
Private Sub CbArchiver_Click()
'(note) Sélectionner la dernière ligne du tableau
Dim vLigne As Integer
vLigne = Application.CountA(Sheets("Buvard").Range("a:a")) + 1
'(note) Archiver les données du formulaire
Sheets("Buvard").Cells(vLigne, 1) = TxbDate.Value
Sheets("Buvard").Cells(vLigne, 2) = TxbAdresseFeuille.Value
Sheets("Buvard").Cells(vLigne, 3) = TxbAdresseCible.Value
For Each Ctrl In FType.Controls
If TypeOf Ctrl Is MSForms.OptionButton Then
If Ctrl.Value = True Then
Sheets("Buvard").Cells(vLigne, 4) = Ctrl.Caption
Exit For
End If
End If
Next Ctrl
Sheets("Buvard").Cells(vLigne, 5) = TxbAuteur.Value
Sheets("Buvard").Cells(vLigne, 6) = TxbCommentaires.Value
'(note) Tri du journal par ordre chronologique
Sheets("Buvard").Activate
Range("Tableau3[[#Headers],[DATE_MODIF]]").Select
ActiveSheet.ListObjects("Tableau3").ShowAutoFilterDropDown = True
ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort.SortFields. _
Add2 Key:=Range("Tableau3[[#All],[DATE_MODIF]]"), SortOn:=xlSortOnValues, _
Order:=xlDescending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Buvard").ListObjects("Tableau3").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
ActiveSheet.ListObjects("Tableau3").ShowAutoFilterDropDown = False
Sheets(TxbAdresseFeuille.Value).Activate
'(note) Fermer le formulaire
Unload Me
End Sub
Private Sub ObAjout_Change()
If ObAjout.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub ObModification_Change()
If ObModification.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub ObSuppression_Change()
If ObSuppression.Value = True Then
CbArchiver.Enabled = True
End If
End Sub
Private Sub TxbCommentaires_Change()
TxbCommentaires.MultiLine = True
End Sub
Private Sub UserForm_Initialize()
TxbDate.Value = Now
TxbDate.Enabled = False
TxbAdresseFeuille = ActiveSheet.Name
TxbAdresseFeuille.Enabled = False
TxbAdresseCible = Selection.Address
TxbAdresseCible.Enabled = False
TxbAuteur.Value = Environ("username")
TxbAuteur.Enabled = False
End Sub
Il me reste deux soucis :
- L'adresse de la cellule/plage modifiée est la bonne que si je valide avec : "Ctrl Enter" (le "Shift Enter", le "Enter" ou le "Tab", retourne une mauvaise adresse)
Passe alors l'adresse de Target à ton userform.
Quid des modifications groupées (ex : suppression de plusieurs valeurs en même temps) ?
Peux-tu mettre ton userform et ton fichier ?
Je vais poursuivre hors userform de mon côté. On verra si j'y arrive ! ou pas !
Ok pour les </> !!
lorsque je sélectionne une ligne ou une colonne, et que je l'ajoute ou la supprime, le code retourne les bonnes plages, idem quand je copie.
voici le fichier et le code
si vous avez des idées pour l'améliorer, c'est très volontiers !
Très intéressant (un peu lourd néanmoins car il faut toujours indiquer le motif, mais c'est très rigoureux !).
En toute logique, en cas d'annulation, il faudrait annuler l'opération
Private Sub CbAnnuler_Click()
Unload Me
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox ("Attention, la dernière modification n'a pas été Archivée dans le Journal !!!!")
End Sub
J'ai systématiquement une erreur sur le tri du buvard (que j'ai donc supprimé). Pour alléger, on peut aussi ne faire le tri que lors de l'activation de l'onglet buvard.
Juste pour le fun !
Salut Steelson
Dans ton système:
Superbe !!!!!
Bravo, impressionnant ! condamner une action faite sur une ligne ou une colonne va aussi, une sécurité supplémentaire, c'est bien dans le cas d'espèce !
En tout cas le "undo" et la façon de limiter aussi la sortie d'une cellule est géant !
merci beaucoup beaucoup, c'est du code que je réutiliserai
Dans mon système:
oui en effet, si je modifie 15 cellules, avec mon système, je dois faire 15 fois le formulaire... mais comme il est prérempli, je n'ai que deux clics à faire par formulaire, c'est acceptable, partant du principe que le fichier n'est censé recevoir que quelques ajustements, 5 ou 6 fois tous les semestres.
Géant !
Bonjour magna steelson le forum
bon alors comme je vois j'arrive après la guerre !!!
alors il ne me reste plus qu'à vous dire bon weekend
a+
Papou
PS: en tout cas très joli boulot Steelson
Bon ce n'est pas parfait ! il reste le problème de tri ... donc c'est réservé plutôt à des fiches et non des bases de données. Et puis quand on efface une plage déjà vide, a priori cela ne se passe pas tout à fait comme prévu.
Re stellson le forum
Oui mais comme j’ai demandé le fichier et que le demandeur nous a gratifié d’un fichier rigoureusement vide pour savoir???
Il doit bien y avoir la possibilité de trier et pour l’effacement des cellules vides je n’ai pas regardé mais je vais tester pour voir
A plus
Papou
euh oui, peu importe ce qu'il y a dans les autres feuilles (que "journal") en fait, c'est le principe, tu y mets ce que tu veux
Salut la Steelson !, Salut paritec !
Alors voilà, j'ai amélioré encore mon formulaire, notamment grâce à l'aide de Steelson.
- Désactivation de la fermeture du formulaire à l'aide de la croix en haut à droite, ce qui oblige l'utilisateur à cliquer sur "Annuler" et grâce à la fonction undo de Steelson, les modifications sur la feuille sont annulée, on revient à l'étape précédente;
- Ajout d'une checkbox "!Vérifiez la Cible!" qui empêche de cliquer sur "Archiver" si on a pas fait le contrôle, car à mes yeux le contrôle de l'utilisateur est indispensable (la vérification de son propre travail);
- Protection de la feuille "Buvard/Journal", n'autorisant que le formulaire à y accéder
- Le tri du "Buvard/Journal" ne se fait plus au niveau de la commande "Archiver" mais lorsque le "Buvard/Journal" est sélectionné pour être consulté.
- ---------------------
- Interdire la suppression des feuilles de données
- Automatiser le "Save as" pour que le journal soit réinitialisé et que la version (dans le nom du classeur) change en "_v+1" de manière automatique
- ---------------------
je t'ai ajouté 10 feuilles contenant chacune 10k lignes et 30 colonnes de Data, si bien que le fichier est devenu trop volumineux pour excel-pratique.... donc plus qu'1 feuille seulement, j'espère que cela sera assez... passe pas non plus... plus que 200 lignes et 10 colonnes...
à + et bonne fin de Dimanche à vous
Moi aussi j'ai amélioré. Surtout que je n'arrivais pas à distinguer les 0 et les vides ! Cela me permet aussi de détecter s'il y avait une formule ou non auparavant !
Private Sub Workbook_Sheetchange(ByVal feuille As Object, ByVal cible As Range)
Dim avant() As Variant, apres() As Variant
On Error GoTo fin
If feuille.Name = "Journal" Then Exit Sub
If cible.Columns.Count = Columns.Count Or cible.Rows.Count = Rows.Count Then
Application.EnableEvents = False
Application.Undo
Application.EnableEvents = True
MsgBox "Impossible d'agir sur une ligne ou une colonne complète !"
Exit Sub
End If
Application.EnableEvents = False
ReDim avant(1 To cible.Rows.Count, 1 To cible.Columns.Count)
ReDim apres(1 To cible.Rows.Count, 1 To cible.Columns.Count)
Application.Undo
For lig = 1 To UBound(avant)
For col = 1 To UBound(avant, 2)
avant(lig, col) = cible.Cells(1, 1).Offset(lig - 1, col - 1).FormulaLocal
Next
Next
Application.Undo
For lig = 1 To UBound(apres)
For col = 1 To UBound(apres, 2)
apres(lig, col) = cible.Cells(1, 1).Offset(lig - 1, col - 1).FormulaLocal
Next
Next
For lig = 1 To cible.Rows.Count
For col = 1 To cible.Columns.Count
If avant(lig, col) <> apres(lig, col) Then
With Sheets("Journal").ListObjects(1)
.ListRows.Add
i = .ListRows.Count
With .DataBodyRange
.Cells(i, 1) = Now
.Cells(i, 2) = feuille.Name
.Cells(i, 3) = cible.Cells(1, 1).Offset(lig - 1, col - 1).Address
.Cells(i, 4) = "'" & avant(lig, col)
.Cells(i, 5) = "'" & apres(lig, col)
.Cells(i, 6) = Environ("username")
End With
End With
End If
Next
Next
fin:
Application.EnableEvents = True
If Err Then MsgBox "Erreur #" & Err.Number & " !"
End Sub
Sujet addictif !