Historique d'entrées aux cellules sécurisées

Le 19/12/2017 à 16:51

Bonjour,

Je dispose d'un classeur Excel 2016 avec plusieurs onglets. Dans ces onglets se trouvent des tableaux avec certaines cellules protégées par 1 ou plusieurs mots de passe (1 personne = 1 mot de passe). Donc plusieurs personnes peuvent avoir accès à une même cellule.

Un problème se pose alors (quant à la sécurité de la société) : comment savoir qui est entré dans la cellule (sécurisée) et quand ?

J'aimerais donc savoir s'il est possible d'avoir un historique montrant toutes les connexions/accès (donc quand une personne entre son mot de passe) avec la date et l'heure pour chaque cellule sécurisée.

Je précise que :

  • je m'y connais vraiment peu en VBA/macros,
  • j'ai effectué beaucoup de recherches sur ce sujet mais étant débutant dans ce domaine je n'ai pas su me servir des quelques informations trouvées pour remplir ma mission.

Merci par avance.

Le 19/12/2017 à 20:19

Bonjour,

Comme il y a plusieurs utilisateurs, j'en déduis que les mots de passe sont récupérés à l'ouverture du classeur dans la procédure événementielle "Workbook_Open()" dans ce cas, il suffit de mémoriser le MDP dans une variable de niveau classeur (dans un module standard) et à chaque sélection de cellule verrouillée, stocker le nom de l'utilisateur qui correspond à ce MDP et le jour et l'heure dans une feuille cachée. Une idée :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Utilisateur As String
    Dim Heure As Date
    Dim Lig As Long

    If Target.Locked = True Then

        Heure = Now

        'les différents MDP qui correspondent aux différents utilisateurs
        Select Case MDP

            Case "azerty": Utilisateur = "Truc"
            Case "uiopq": Utilisateur = "Machin"
            Case "sdfgh": Utilisateur = "Bidule"
            Case "jklmw": Utilisateur = "Chose"

        End Select

        'inscription des différentes valeurs
        With Worksheets("Cachée")

            Lig = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 'sur colonne A
            .Cells(Lig, 1).Value = Utilisateur
            .Cells(Lig, 2).Value = Heure
            .Cells(Lig, 3).Value = Target.Address(0, 0) 'adresse de la cellule

        End With

    End If

End Sub
Le 20/12/2017 à 16:11

Bonjour Theze,

D'abord merci pour votre réponse.

Alors, j'ai ouvert dans Virtual Basic un Module (pour le classeur) après avoir ouvert un 5ème onglet (onglet qui servira au module d'y transférer toutes les informations nécessaires si j'ai bien compris...?). J'y ai inséré votre code.

J'ai ensuite protégé une cellule par le mot de passe "sdfgh" dans le 1er onglet, puis ai fermé le classeur en enregistrant avec les macros. J'ouvre à nouveau le classeur et accède à la cellule protégée par le mot de passe. Je vais voir dans le module, rien n'a changé, pareil pour l'onglet 5.

(Je cherche en fait à savoir où l'historique est censé être accessible)

Il semble alors que je n'aie pas "activé" le module, il n'a pas l'air de fonctionner de mon côté...

Qu'est-ce que je fais de mauvais? Serait-ce possible s'il-vous-plaît de m'éclairer quant à l'activation et la mise en marche du programme?

Encore merci.

Le 21/12/2017 à 06:55

Bonjour,

Le code que je t'ai donné est une procédure événementielle qui doit se trouver dans le module de la feuille concernée donc, double-clic sur le module de la feuille dans la fenêtre en haut à gauche du VBE et ensuite, coller le code dans la zone d'édition.

Tu dis :

J'ai ensuite protégé une cellule par le mot de passe "sdfgh" dans le 1er onglet...

Le mot de passe à été entrée pour la feuille si j'ai bien compris ? (Onglet "Révision"--->"Protéger la feuille") et dans ce cas, il ne peut y en avoir plusieurs comme dit dans ton premier message :

...par 1 ou plusieurs mots de passe (1 personne = 1 mot de passe)...

Comme tu parlais de plusieurs utilisateurs, je pensais que tu utilisais une procédure événementielle (la même que j'utilise à savoir " Worksheet_SelectionChange()") pour demander le MDP.

Donc, si c'est la feuille qui est protégée (le mot de passe est donc unique, enfin, sur Excel 2007) tu peux utiliser la fonction Environ() pour connaître le nom de l'utilisateur "Utilisateur = Environ("UserName")". Dans le classeur, tu peux utiliser une feuille cachée (nommée ici "Cachée" pour l'exemple) afin de stocker les différentes valeurs :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    Dim Utilisateur As String
    Dim Heure As Date
    Dim Lig As Long

    If Target.Locked = True Then

        Heure = Now

        Utilisateur = Environ("UserName")

        'inscription des différentes valeurs
        With Worksheets("Cachée")

            Lig = .Cells(.Rows.Count, 1).End(xlUp).Row + 1 'sur colonne A
            .Cells(Lig, 1).Value = Utilisateur
            .Cells(Lig, 2).Value = Heure
            .Cells(Lig, 3).Value = Target.Address(0, 0) 'adresse de la cellule

        End With

    End If

End Sub
Le 22/12/2017 à 14:24

Bonjour,

Merci de nouveau votre réponse.

Il y a un problème de compréhension quant à cette histoire de mots de passe : j'ai protégé les feuilles par un mot de passe général (via "Protéger la feuille") ET j'ai ajouté une sécurité en plus en protégeant certaines cellules par des mots de passe (via "Autoriser la modification des plages") en les ayant sécurisées via "Format de cellule" -> "Protection" -> case "Verrouillée" (les cellules vides ne sont pas "Verrouillées"

Il y a donc 1 MDP commun pour toutes les feuilles, et dans ces feuilles certaines cellules peuvent être accessibles via des MDP personnels mais pas pour "Ôter la protection de la feuille".

Pour ce qui est de l'historique, je ne comprends toujours pas comment le faire fonctionner : après avoir attribué un mot de passe (que j'ai fait figurer dans le code de la feuille 5 dans VBA) à une cellule dans la feuille 1 et l'avoir déverrouillée (la cellule pas la feuille), je n'ai pas compris où l'on était censé voir la date, l'heure, celui qui est entré, etc.

=> Où l’historique est-il lisible SVP? Est-ce possible de faire rentrer les données d'historique dans un tableau quelque part?

(1 colonne Date, 1 colonne Heure, 1 colonne MDP entré, 1 colonne Utilisateur, etc.)

Encore merci de prendre le temps de me répondre.

Le 22/12/2017 à 22:16

Bonjour,

A partir du moment où des modifications sont faites dans les plages protégées, tu dois savoir qui fait les modifs, c'est celui qui possède le MDP non ?

Voici un classeur exemple où cinq plages de couleurs différentes sont matérialisées. Pour le test de l'enregistrement des valeurs (dates, heures, etc...), saisie différentes valeurs dans les différentes plages et ensuite, affiche la feuille cachée par Format --> Masquer & Afficher --> Afficher la feuille... Le mot de passe pour garder visible cette feuille est ton pseudo "TRNX". Il n'est pas possible de récupérer le mot de passe mais comme chaque plage correspond à un utilisateur, il suffit de savoir dans quelle plage a été faite la modif pour connaître le nom de l'utilisateur.

Les codes se trouvent dans les modules des deux feuilles. Les tableaux Plages/Utilisateurs doivent être adaptés à tes besoins.

14test-utilisateurs.xlsm (20.28 Ko)
Le 08/01/2018 à 10:00

Bonjour,

Désolé de vous répondre avec ce délai, je passais mes vacances... Donc bonne année!

Sinon je viens de tester le système d'historique que vous m'avez fournis et j'en suis bien content donc merci!

Je vais essayer de l'adapter à mon projet et reviendrai peut-être vers vous si j'en ai le besoin et si vous le voulez bien.

Encore merci.

Le 08/01/2018 à 18:00

Re bonjour,

Eh bien oui je retourne vers vous assez peu de temps après finalement.

Vous me dites que ce n'est pas la peine de connaître le mot de passe de celui qui entre dans une plage car il n'y a qu'une personne par plage. Sauf que non, justement, il y a certaines plages où plusieurs personnes y ont accès.

C'est exactement pour cela que je voudrais que la colonne 'MDP' se remplisse! ( 1 personne = 1 MDP => identification possible )

Maintenant je me tourne vers vous et vous demande s'il y a une solution à ce problème. Nous ne voulons pas séparer les plages en les divisant par le nombre de personnes qui y auront accès (c'est-à-dire diviser une plage en 3 si 3 personnes ont l'accès).

J'ai bien entendu vu dans votre script en vert que ce n'était pas possible d'afficher le mot de passe dans la colonne en question...

Mais n'y a-t-il vraiment aucune alternative?

En attendant vos retours.

Le 15/01/2018 à 19:45

Bonjour,

Si la même plage est accessible par plusieurs utilisateurs donc, avec le même mot de passe, la seule façon de les différencier est d'utiliser la fonction "Environ()" afin de savoir qui est l'utilisateur :

MsgBox Environ("UserName")
Le 16/01/2018 à 13:34

Bonjour,

Merci de revenir me voir et de me répondre.

Etes-vous certain que si je protège une cellule sur une plage via Révision > Autoriser la modification des plages avec un mot de passe je ne pourrai pas le récupérer ? (Une petite page s'ouvre lorsqu'on clique sur la cellule et indique "Une cellule que vous essayez de modifier est protégée par un mot de passe. Entrer le mot de passe pour modifier cette cellule." Je veux juste être sûr que vous voyiez de quoi je parle)

N'y a-t-il pas de fonction associée qui récupérerai ce mot de passe entré ?

Sinon, second problème, j'aimerais additionner des plages mais on m'indique une erreur :

    'adapter les plages...
    TblPlage = Array("A1:D10" & "O1:P1" & "R3:U15", "A11:D20", "A21:D30", "A31:D40", "A41:D50")
    'aux utilisateurs correspondants
    TblUtilisateur = Array("Personne1", "Personne2", "Personne3", "Personne4", "Personne5")

J'ai essayé donc d'attribuer plusieurs plages pour 1 utilisateur mais ça ne fonctionne pas...

J'ai pris en compte votre dernier message mais cela ne semble pas me convenir... En effet chaque utilisateur doit avoir son mot de passe.

Merci!

Le 17/01/2018 à 07:08

Bonjour,

Je ne peux pas être catégorique maisje ne pense pas qu'il soit possible de récupérer le mot de passe sinon, à quoi bon mettre un mot de passe si il est facile pour quiconque de le récupérer ?

Pour pouvoir récupérer le mot de passe, il faudrait qu'il y est un événement "Change", "Exit" ou autre sur le TextBox servant à la saisie et ce mot de passe et ce n'est pas le cas !

Pour la somme de plages de cellules :

Sub Test()

    MsgBox Application.Sum(Range("A1:D10"), Range("O1:P1"), Range("R3:U15"), _
                           Range("A11:D20"), Range("A21:D30"), _
                           Range("A31:D40"), Range("A41:D50"))

End Sub
Le 17/01/2018 à 16:18

Bonjour,

merci encore de votre réponse.

OK je vais voir comment je vais m'organiser pour cette histoire de mots de passe.

Sinon pour l'addition des plages je ne vois pas comment insérer vos lignes... Oui quand je vous disais que je ne m'y connaissais pas bien en VBA... Je vais essayer de suivre les cours proposés par le site.

J'en étais à là :

Private Function Utilisateur(Cel As Range) As String

    Dim TblPlage
    Dim TblUtilisateur
    Dim I As Integer

    'adapter les plages...
    TblPlage = Array("A1:D1" & "O1:P1 & "R3:U15", "A11:D20", "A21:D30", "A31:D40", "A41:D50")
    'aux utilisateurs correspondants
    TblUtilisateur = Array("Personne1", "Personne2", "Personne3", "Personne4", "Personne5")

    For I = 0 To UBound(TblPlage)

        If Not Intersect(Cel, Range(TblPlage(I))) Is Nothing Then

            Utilisateur = TblUtilisateur(I)
            Exit For

        End If

    Next I

    If Utilisateur = "" Then Utilisateur = "Non protégée"

End Sub

Et j'ai essayé de l'introduire de cette manière :

Private Function Utilisateur(Cel As Range) As String

    Dim TblPlage
    Dim TblUtilisateur
    Dim I As Integer

    'adapter les plages...
    TblPlage = Sub Test() 'message d'erreur ici

    MsgBox Application.Sum(Range("A1:D10"), Range("O1:P1"), Range("R3:U15"), _
                           Range("A11:D20"), Range("A21:D30"), _
                           Range("A31:D40"), Range("A41:D50"))

End Sub
    'aux utilisateurs correspondants

    TblUtilisateur = Array("Personne1", "Personne2", "Personne3", "Personne4", "Personne5")

    For I = 0 To UBound(TblPlage)

        If Not Intersect(Cel, Range(TblPlage(I))) Is Nothing Then

            Utilisateur = TblUtilisateur(I)
            Exit For

        End If

    Next I

    If Utilisateur = "" Then Utilisateur = "Non protégée"

End Sub

Je vois bien que ce n'est pas comme ça qu'il faut faire mais je ne trouve pas comment régler le problème...

Merci de m'aider..!

Le 18/01/2018 à 06:47

Bonjour,

Tu parles de somme mais tu montre un code qui retourne le nom d'un utilisateur ?

Sinon, second problème, j'aimerais additionner des plages mais on m'indique une erreur :

Pour une fonction ; "Private Function Utilisateur(Cel As Range) As String" elle doit se terminer par "End Function" et non "End Sub" !

L'erreur viens du fait que tu ne sépares pas tes plages par une virgule lors de la concaténation "A1:D1" & "O1:P1 & "R3:U15" au lieu de "A1:D1," & "O1:P1," & "R3:U15"

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

    MsgBox Utilisateur(Target)

End Sub

Private Function Utilisateur(Cel As Range) As String

    Dim TblPlage
    Dim TblUtilisateur
    Dim I As Integer

    'adapter les plages...
    TblPlage = Array("A1:D1," & "O1:P1," & "R3:U15", "A11:D20", "A21:D30", "A31:D40", "A41:D50")

    'aux utilisateurs correspondants
    TblUtilisateur = Array("Personne1", "Personne2", "Personne3", "Personne4", "Personne5")

    For I = 0 To UBound(TblPlage)

        If Not Intersect(Cel, Range(TblPlage(I))) Is Nothing Then

            Utilisateur = TblUtilisateur(I)
            Exit For

        End If

    Next I

    If Utilisateur = "" Then Utilisateur = "Non protégée"

End Function
Le 18/01/2018 à 17:11

Bonjour,

Merci beaucoup c'est ça que je recherchais.

Maintenant,

Ce qui est résolu :

  • Il y a 10 utilisateurs
  • Chaque utilisateur a ses plages (mais il y a des intersections : certaines personnes peuvent avoir une plage en commun)

Ce qu'il faudrait :

  • Pour toutes les plages de chaque utilisateur doit être attribué une clé (exemple : mettre une clé pour Personne1 pour toutes ses plages, mettre une autre clé pour Personne5 pour toutes ses plages, etc.)
  • N'autoriser l'accès aux plages que si celles-ci sont "possédées" par ses Utilisateurs.
  • Mettre en place via VBA une fenêtre qui s'affiche lorsqu'on essaye de modifier une cellule pour chaque plage pour y entrer sa clé.

Exemple : Plage "A1:D10" => appartient à Personne1 => fenêtre s'ouvre et doit rentrer sa clé => clé entrée => récupérée et mise dans le tableau de l'historique.

Si une plage est accessible par la Personne2 et la Personne6, seules les clés de Personne2 et Personne6 peuvent être rentrées.

En espérant que c'est clair, est-ce possible?

Merci encore de votre aide!

Rechercher des sujets similaires à "historique entrees securisees"