XL 2019 Création d'un historique via VBA

[Mate59]

Bonjour,

Alors je vous explique mon problème, j'aimerai créer un historique de modifications sur mon fichier Excel via un onglet caché où je serai le seul à y accéder. J'aurai bien voulu passer par la fonction que propose Excel mais cela ne fonctionne pas, est-ce à cause du fait que mon fichier à de la macro?

Dans cet historique, j'aimerai inclure:
- la date
- l'heure
- l'utilisateur (que j'ai défini dans un onglet pour permettre une connexion à l'ouverture du fichier afin que certain n'est pas accès à tout)
- la feuille concernée
- la case
- la valeur avant modification et celle d'après.

les mdp sont:
pour la connexion: mcoppin avec comme mdp ecrmucbom
pour ôter la protection des feuilles c'est 8665
et l'accès pour la macro, elle se fait avec le même mdp que celui de la connexion

Merci beaucoup de l'aide que vous m'apporterez.

 

[sylvanu]

Bonjour Mate,
Joli sujet.
En PJ un essai avec pas mal de modifs. Dans l'ordre :
1- Création d'une feuille Tracabilité
2- Les variables Rôle et ValeurVolée doivent être en public. ( pour mémorisation )
3- Masquage au démarrage. J'en ai profité pour simplifier le module :

Private Sub Workbook_Open()
'Masquer les pages à l'ouverture sauf login.
For Each F In Worksheets
    If F.Name = "Login" Then
        Sheets(F.Name).Visible = True
    Else
        Sheets(F.Name).Visible = 2
    End If
Next F
End Sub

4- Dans toutes les feuilles "à tracer", mettre :

Sub Worksheet_Change(ByVal Target As Range)
    ' Sur valeur modifiée, excécute traça pour mémorisation
    If Target.Count > 1 Then Exit Sub
    Traça ActiveSheet.Name, Target.Address, Target.Value
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    ' "Vole" la valeur de la cellule dès que l'on clique dessus pour récupérer valeur avant modification
    If Target.Count > 1 Then Exit Sub
    ValeurVolée = Target.Value
End Sub

( Attention, je n'ai modifié que la feuille Suivi PL pour le test )
A chaque fois qu'on clique sur une cellule, Worksheet_Selection mémorise la valeur avant modification.
Quand la valeur est modifiée alors la macro Worksheet_Change lance la macro Traça.
5- Module de traçabilité Traça :

Public ValeurVolée
Sub Traça(NomFeuille, AdresseCellule, ValeurCellule)
    With Sheets("Tracabilité")
        DL = 1 + .Range("A65500").End(xlUp).Row ' Dernière ligne
        .Cells(DL, 1) = Date
        .Cells(DL, 2) = TimeValue(Now)
        .Cells(DL, 3) = Rôle
        .Cells(DL, 4) = NomFeuille
        .Cells(DL, 5) = AdresseCellule
        .Cells(DL, 6) = ValeurVolée
        .Cells(DL, 7) = ValeurCellule
    End With
End Sub

Mémorise les données dans la feuille Traçabilité.

YaPuKa ... finaliser et mettre à votre sauce quelles feuilles doivent être tracées.

 

[Mate59]

Merci pour ton aide!
Par contre, comment fais-tu pour faire apparaître l'utilisateur?

lorsqu'en 2 tu me dis que cela dois être en public, peux-tu m'expliquer plus en détail?

Merci d'avance

 

[sylvanu]

Bonjour,
1- Pour l'instant la feuille traçabilité peut être visible si vous affichez les noms d'onglets. De plus je l'ai mis en XLSheetVeryHidden pour être "bien cachée"
Je ne sais pas comment vous voulez la rendre visible en utilisation normale.
Dans cette PJ j'ai rendu cette feuille visible si Admin.
2- Lorsqu'une variable est utilisée dans un module, elle n'est "visible" que par celui ci.
Mais il faut que je mémorise Rôle pour pouvoir l'archiver plus tard.
Dans ce cas on déclare :

Public Rôle as String

et la variable est déclarée en publique donc visible par tout le monde. Donc quand je veux archiver celle ci est disponible. Idem pour VariableVolée, je mémorise la valeur sur un clic cellule mais l'archive quand la valeur de celle cellule est modifiée.

NB: Vous pouvez en admin rendre les onglets visible avec :

ActiveWindow.DisplayWorkbookTabs = True

et les remasquer avec False

 

[Mate59]

J'ai réussi à faire certaines choses, notamment le suivi sur plusieurs feuilles. Par contre, je me suis rendu compte qu'en utilisant un autre utilisateur, le suivi ne fonctionnait pas bien. A quoi cela peut être dû?

 

[sylvanu]

1- Pour les données non enregistrées en traça, cela est dû au fait que des cellules étaient fusionnées, et donc en F et G ne pouvait pas se faire. Les Valeurs avant et après ne doivent qu'avoir une seule colonne. J'ai joué sur la largeur.
2- Pour le User, la variable public n'est pas mémorisée ( contrairement à ValeurVolée qui marche bien )
Je ne comprends pas pourquoi. Peut être la gestion du Login.
Pour contourner le problème je stocke le user en Traçabilité H1.
En PJ cela a l'air de marcher.

 

[Mate59]

Bonjour Merci! ça à l'air de fonctionner à merveille!
Par contre pour mon information, comment as-tu fais apparaître l'utilisateur en H1?

 

[sylvanu]

Bonjour,
1- Dans CommandButton1_Click :

' Mémorisation user
Sheets("Tracabilité").[H1] = Txt_User

2- ???
J'ai simplement élargi les colonnes à la main.
Comme dans Trace, j'ai :

        .Cells(DL, 6) = ValeurVolée
        .Cells(DL, 7) = ValeurCellule

Les deux valeurs sont en F et G, donc il ne doit pas y avoir de cellules fusionnées. Donc j'ai "défusionné" les colonnes et simplement élargi pour l'esthétique.

 

[Mate59]

Super Merci beaucoup pour toute l'aide que tu m'as apporté, je vais enfin pouvoir continuer à agrandir mon fichier

 

[Mate59]

Bonjour,
Sais-tu comment faire apparaître sur une page dans une case après la connexion, un message de bienvenue?

Merci d'avance

 

[sylvanu]

Bonjour,
Vous pourriez le faire via un msgbox, avec dans Login :

    ' Message de bienvenu
    MsgBox "Bien le bonjour " & Txt_User & "." & Chr(10) & "Comment allez vous ?"
    'Vider Formulaire Connection.
    Txt_User = ""
    Txt_pass = ""

J'ai supprimé tous les autres Txt_User="" pour récupérer le nom de l'utilisatur.

 

[Mate59]

Merci pour ta rapidité, mais plutôt que d'avoir une fenêtre qui s'ouvre, je préférerai avoir dans une case comme l'image ci-dessous sauf que la je l'ai fais manuellement et pourquoi pas y ajouter un smiley à la fin de la phrase.

 

[sylvanu]

Alors on peut rajouter ça à la fin de Login :

    ' Message de bienvenu
    With Sheets("Tableau de Bord")
        .[Q3] = "BIENVENU " & Txt_User & "!   J"    ' Message, "J" est le smiley en police Wingdings
        .[Q3].Characters(Start:=Len(.[Q3]), Length:=1).Font.Name = "Wingdings"
    End With
    
    'Vider Formulaire Connection.
    Txt_User = ""
    Txt_pass = ""

Comme précédemment, tous les Txt_User = "" ont été supprimés pour l'ajouter une seule fois à la fin.

 

[Mate59]

Merci beaucoup pour toute l'aide que tu m'apportes

 

[thunder23]

Bonjour le forum,

Je souhaite ré-ouvrir ce sujet car je voudrais l'adapter à mon fichier. J'ai réussi pour une modification sur une feuille excel (sauf que ça me note pas la valeur avant modification) mais vue que je passe par des USF pour certaines modifications sur des feuilles, est-ce que tu aurais une piste sur ce sujet @sylvanu ?
Ah aussi, je voudrais que ça me mette également dans traçabilité lors des ouverture du fichier.

Merci d'avance