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é.
  • ---------------------
Je pense éventuellement rajouter :
  • 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
  • ---------------------
@paritec

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 !

Rechercher des sujets similaires à "journal modifications"