Code VBA - Récupérer des valeurs entre diverses feuilles

[Trunk]

Bonjour à tous,

Je tiens tout d'abord à vous remercier car j'ai pu réussir à trouver un petit bout de code que je cherchais grace aux divers échanges que j'ai pu lire sur ce forum...vous contribuez à aider les personnes, telles que moi, qui ne sont pas expertes en Excel et en développement et qui nécessitent parfois des petits coups de pouce pour parvenir à leurs fins !

Néanmoins, j'ai encore besoin d'aide... Je vous expose mon besoin :

J'ai mis en place dans un fichier excel un petit bout de code VBA qui affiche une feuille spécifique après qu'une personne ait entré un mot de passe (inputbox).

J'aimerais désormais pouvoir afficher à cette personne de manière automatique ce qu'elle est censée voir sur sa feuille dédiée.

--> Nous avons dans ma société un système de gestion électronique de clés contrôlée par des badges, et les personnes qui l'utilisent ont accès à plein de clés et ne savent pas à quoi toutes ces clés correspondent... J'aimerais donc leur montrer au sein d'une feuille l'intitulé des emplacements, mais seulement ceux auxquels ils ont accès.

J'ai fait un fichier excel exemple très raccourci que je joins à ce message, pour imager un peu ...

J'ai une feuille nommée "Accès" où j'ai comme colonnes :

- une colonne avec le mot de passe de la personne,
- ensuite une colonne avec son Prénom NOM,
- ensuite 5 colonnes correspondant à numéros de groupes qui sont en fait les numéros d'emplacements des clés. Sur la ligne de cette personne sont cochées des croix sous les groupes, indiquant les accès qu'a la personne.

Ensuite j'ai une feuille nommée "Emplacements" où l'on retrouve les numéros d'emplacements et en face de chacun d'entre eux l'intitulé des bureaux.

Ce que j'aimerais réaliser :

- Une personne ouvre le fichier, l'invite de commande lui demande un mot de passe. Elle entre le mot de passe et cela lui affiche la feuille qui lui a été réservée dans le code VBA (feuille "Agent1" ou "Agent2" dans mon fichier),

- la feuille contient un tableau comme celui déjà présent qui va alors lui afficher les numéros et intitulés de clés relatives aux accès qu'il a.

--> Pour cela, il faudrait que le code ait le comportement suivant : Le mot de passe est entré, le fichier cherche dans la colonne mot de passe du fichier "Accès", il vérifie les croix cochées sur la ligne de la personne et fait la relation avec les emplacements de la feuille "Emplacements". Puis les numéros et intitulés de clés sont récupérés et s'affichent les uns après les autres dans le tableau de feuille vue par l'agent.

J'ai simulé le fonctionnement de cet automatisme au sein du fichier excel joint.

Les mots de passe pour accéder au fichier sont "test" et "test2" ("test" affiche la feuille 1 et "test2" affiche la feuille 2).

Je ne sais pas si mes explications sont claires, mais j'ai cogité pendant un petit moment avant de me lancer dans l'écriture de ce message en essayant de clarifier au maximum ... Vous me répondrez peut-être que c'est utopique ce que je demande, ou critiquerez peut-être aussi code déjà en place (vous pouvez bien sur! ), quoiqu'il en soit je suis ouvert à toute proposition ou aide, même si partielle, du moment que cela peut me permettre d'avancer...

Merci d'avance à tous ceux qui prendront le temps de me répondre.

 

[skoobi]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Bonjour Trunk, bienvenue surXLD,

Ahhhh des explications comme celle là il y en a pas souvent, c'est limpide je trouve, merci.
Juste une remarque: tu ne dis pas comment l'utilisateur est identifié.
Dans mon exemple, la macro va regarder le nom d'utilisateur excel (dans options onglet général).
J'ai ajouté dans la feuille accès la colonne "Feuille agent"
Voir la proposition jointe.

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Merci beaucoup, j'ai fais de mon mieux

Et merci pour ta réponse rapide et le code proposé, qui m'a l'air assez complet. Toutefois, je n'ai plus d'invite à l'ouverture du fichier demandant un mot de passe comme c'était précédemment ? Je ne vois que les feuilles "Accès" et "Emplacement" ... ?

Vis à vis de l'identification de l'utilisateur, le fichier que j'avais joint à mon message comportait un petit bout de code VBA qui demandait un mot de passe via une Inputbox à l'ouverture du fichier.

Dans le code VBA, je faisais afficher telle ou telle feuille en fonction du mot de passe qui était entré.

J'avais également ajouté une boucle pour que cette inputbox s'affiche tant qu'un mot de passe correct n'était pas entré. Si l'utilisateur entre un mauvais mot de passe ou tente d'annuler, un MsgBox lui demande s'il veut fermer le fichier : si oui, fermeture du fichier, si non, boucle sur l'inputbox.

Voilà voilà ...

Je pense que la partie "mot de passe" ne doit plus être active dans le fichier que tu transmets ?

 

[skoobi]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Re,

Toutefois, je n'ai plus d'invite à l'ouverture du fichier demandant un mot de passe comme c'était précédemment ? Je ne vois que les feuilles "Accès" et "Emplacement" ... ?

oui c'est vrai car j'ai en fait déplacé le code dans un module pour faire les tests, désolé
Voici le code à mettre dans ThisWorkbook:

Private Sub Workbook_Open()
Dim Agent As String, TrouveUser As Range, LigUser As Long, BoxValue As String
Dim TabEmplac(), TabEmplacUser(), Col As Long, NbreAcces As Long, i As Long
'Masque directement la feuille si cette derniere est visible
  If Sheets("Agent1").Visible = True Then Sheets("Agent1").Visible = xlVeryHidden
  If Sheets("Agent2").Visible = True Then Sheets("Agent2").Visible = xlVeryHidden
  'on identifie l'utilisateur
  Agent = Application.UserName
'  Agent = "Arnaud EXEMPLE"
  'dans la feuille accès
  With Sheets("Accès")
    'on identifie la ligne de l'utilisateur
    Set TrouveUser = .Columns("B").Find(Agent, LookIn:=xlValues, lookat:=xlWhole)
    If Not TrouveUser Is Nothing Then
      LigUser = TrouveUser.Row
    Else
      MsgBox Agent & " n'est pas dans la liste!", vbExclamation
      Exit Sub
    End If
    Do
      BoxValue = InputBox("Mot de passe requis", "Mot de passe")
      'Rend la feuille visible si mot de passe OK
      If BoxValue = .Range("A" & LigUser).Value Then
        Sheets(.Range("C" & LigUser).Value).Visible = True
        Exit Do
      Else
        MsgBoxValue = MsgBox("Le mot de passe est incorrect ! Voulez-vous fermer ce fichier ?", vbYesNo + vbQuestion, "Erreur de saisie")
        If MsgBoxValue = vbYes Then
          ThisWorkbook.Close
        End If
      End If
    Loop
'on récupère le tableau emplacement
    TabEmplac = Sheets("Emplacements").Range("D6", Sheets("Emplacements").[D65536].End(xlUp)).Value
    i = 0
    For Col = 4 To .[IV6].End(xlToLeft).Column
'on vérifie l'acces des emplacements
      If .Cells(LigUser, Col).Value = "X" Then
        i = i + 1
  'on cré un tableau VBA qui correspondra aux acces de l'agent
        ReDim Preserve TabEmplacUser(1 To 2, 1 To i)
        TabEmplacUser(1, i) = .Cells(6, Col).Value
        TabEmplacUser(2, i) = TabEmplac(.Cells(6, Col).Value, 1)
      End If
    Next
  End With
'dans la feuille de l'agent
With Sheets(Sheets("Accès").Range("C" & LigUser).Value)
'on vide le tableau
  .Range("C7", .[D65536].End(xlUp).Offset(1, 0)).ClearContents
'on récupère le nombre d'acces
  NbreAcces = UBound(TabEmplacUser, 2)
'on copie le résultat
  .Range("C7:D" & NbreAcces + 7 - 1).Value = Application.Transpose(TabEmplacUser)
End With
End Sub

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Je viens d'essayer, mais j'ai uniquement un message qui m'indique que (si on prend mon prénom Jean et mon nom DUPOND) "jdupond" n'est pas dans la liste.

Est-ce qu'il te serait possible de ne pas filtrer sur ce qui est placé dans l'onglet général mais plutôt sur le mot de passe qui est entré, par exemple :

Jean DUPOND ouvre le fichier, on lui demande un mot de passe. Il entre son mot de passe et sa feuille dédiée s'affiche.

Comportement : le code VBA fait une recherche de ce mot de passe dans la feuille "Accès" et récupère les accès qui sont sur la même ligne (les cellules ayant des croix). Il recherche les numéros et intitulés de bureaux au sein de la feuille "Emplacements" et les copie dans la feuille de Jean DUPOND au sein du tableau prédéfini.

--> Est-ce possible selon toi ?

Parce qu'en fait "jdupond" n'existera nul part dans les différentes feuilles, par contre "Jean DUPOND" sera dans une cellule de la même ligne que le mot de passe ...

Merci encore pour ton aide !!!

 

[WILFRIED]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

SAlut trunk,

voici une petit proposition.

Le mots de passe administrateur (pour débloquer les feuilles et les macros) est WSAL74

J'espère que cela te conviendra

A+

Hoops salut skooby, je me suis encore fait avoir par le rafraichissement d'écran! je suis partie bille en tête sur mon idée et j'ai même pas pensé à rafraichir.

A+

 

[skoobi]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Jean DUPOND ouvre le fichier, on lui demande un mot de passe

Ok mais qu'est-ce qui permet de savoir que c'est Jean DUPOND qui ouvre le fichier?
Il faut bien faire le lien entre le mot de passe et l'utilisateur!
Où alors j'ai zappé une étape!

et les copie dans la feuille de Jean DUPOND

Est-ce que le nom de la feuille sera Jean DUPOND?

Edit: salut Wilfried

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Deux réponses simultanées ! C'est pas sur le forum microsoft qu'on aurait une réactivité pareille !

Merci pour ta proposition Wilfried, je regarde cela dès que possible avant la fin d'après-midi pour te répondre.

skooki, voici le déroulé :

- l'utilisateur Jean DUPOND a un mot de passe 1234, que seul lui a,
- il se connecte via l'inputbox,
- le code vérifie le mot de passe et lui affiche la feuille qui lui a été dédiée (nous dirons la feuille 1),
- En plus de cela, le code vérifie la feuille "Accès", où est renseigné dans une colonne "mot de passe" le "1234" devant le nom de Jean DUPOND (sur la même ligne).
- Une vérification est faite sur cette ligne pour connaître les accès de Jean DUPOND (nous dirons par exemple qu'il y a une croix sous le 1, le 3 et le 5).
- Le code vérifie la feuille "Emplacement et récupère les numéros des emplacements (accès numéro 1 = emplacement numéro 1) et leurs intitulés, et les affiche dans le tableau de la feuille 1 les uns à la suite des autres, sous cette forme là en somme :

Emplacement | Intitulé
1 | Bureau XXX
3 | Bureau XXX
5 | Bureau XXX

J'espère que cela clarifiera tes interrogations skooki ... n'hésite pas à me le dire si ce n'est pas le cas.

PS : MER-CI encore !

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

C'est carrément presque ça !

Y'a juste deux choses :

1/ Il faudrait que l'invite de mot de passe s'affiche à l'ouverture du fichier (là c'est un bouton "gestion des accès" qui est en fait situé dans la barre d'édition d'excel). Si je pars de ton exemple, l'utilisateur n'aurait pas d'autre choix que de saisir le mot de passe correspondant à son nom/prénom, ou de quitter le fichier excel (boucle tant que mauvais mot de passe saisi + bouton "quitter" en somme).

2/ Il faudrait que le fichier présente en arrière plan une feuille vierge (je la personnaliserai ultérieurement pour donner un peu plus de cachet), mais qui ne soit pas une des feuilles correspondant à un des utilisateurs.

Je regarde le comportement de plus près ...

 

[skoobi]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Re,

Tous les points sont très clairs sauf celui-ci:

- En plus de cela, le code vérifie la feuille "Accès", où est renseigné dans une colonne "mot de passe" le "1234" devant le nom de Jean DUPOND (sur la même ligne).

Dans ce cas nous sommes d'accord qu'il est fait uniquement une vérification du mot de passe... curieux comme vérification car on générale on vérifie le login + mot de passe, mais bon, c'est toi qui vois...
Donc ci-joint une nouvelle proposition.

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Effectivement, si y'a un login, c'est mieux, mais comme j'en demandais déjà beaucoup ... j'ai simplifié en demandant uniquement un mot de passe dans l'inputbox (cf premier fichier que j'avais joint).

Je regarde ta proposition... MERCI !

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Parfait ! Le comportement a l'air idéal ...

Je fais quelques tests complémentaires pour me mettre en situation d'exploitation courante et je te/vous fais un retour.

Franchement ... MERCI !

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Juste pour vous dire que je n'ai pas encore eu le temps de tester le fichier, mais je le fais cet aprèm sans faute !

 

[Trunk]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Bonjour,

J'ai jeté un oeil au fichier, en fait les feuilles "Accès" et "Emplacements" sont visibles par toutes les personnes qui consultent le fichier.

Il faudrait dans l'idéal que quand elles ouvrent le fichier, que seule une feuille lambda soit affichée derrière l'Inputbox, et qu'à la saisie du mot de passe, la feuille "Accès X" dédiée à la personne s'affiche.

Les feuilles Accès et Emplacements ne doivent être visibles que par l'administrateur, qui a dans ce cas un mot de passe particulier.

De plus, est-ce qu'on peut verrouiller le code VBA pour que personne d'autre que l'administrateur n'y ait accès, et verrouiller également les modifications, idem, seulement autorisées pour l'administrateur ?

Merci beaucoup d'avance pour ton / votre aide.

 

[skoobi]

Re : Code VBA - Récupérer des valeurs entre diverses feuilles

Re bonjour,

Bonjour,
J'ai jeté un oeil au fichier, en fait les feuilles "Accès" et "Emplacements" sont visibles par toutes les personnes qui consultent le fichier.

Il faudrait dans l'idéal que quand elles ouvrent le fichier, que seule une feuille lambda soit affichée derrière l'Inputbox, et qu'à la saisie du mot de passe, la feuille "Accès X" dédiée à la personne s'affiche.

Je pense que cela tu peux le faire tout seul non? Il suffit d'ajouter les 2 lignes pour ces 2 feuilles en début de macro comme pour agent1 et agent2.

Les feuilles Accès et Emplacements ne doivent être visibles que par l'administrateur, qui a dans ce cas un mot de passe particulier.

Ca devrait aussi être dans tes cordes:
Si BoxValue = mot de passe administrateur alors affiche les feuilles accès et emplacements.

De plus, est-ce qu'on peut verrouiller le code VBA pour que personne d'autre que l'administrateur n'y ait accès?

Pour protéger le code VBA:
Dans la fenêtre projet sous VBE:
1-ferme l'arborescence du projet pour ne voir apparaître que le nom du fichier
2-bouton droit>propriétés de VBA projet
3-onglet protection: coche verrouiller l'affichage du projet et cré le mot de passe.

... et verrouiller également les modifications, idem, seulement autorisées pour l'administrateur

Comprends pas .