Microsoft 365 Accès restreint par onglet suivant utilisateur

[Roger MEYER]

Bonjour,
Je gère une association sportive - club de foot - et pour ce faire j'utilise un classeur Excel qui se compose de 25 onglets dont un onglet BdD.

Équipements / gestion individualisée des commandes des livraisons par catégorie de joueurs et de licenciés / des paiements
licences / selon tableau des catégories
Administratifs / dont la connaissance du joueur / des coordonnées personnelles / d'antériorités etc... mails - adresse - tél etc...

Bref...!!
Via une feuille Sommaire toujours présente j'ai organisé un organigramme par clic sur des boutons d'accès par macro aux différentes feuilles.
Selon cet organigramme il présente les onglets exclusivement nécessaire au traitement de la demande et masque les onglets inutiles.

Cependant je voudrais que certains utilisateurs ne puissent pas accéder à tel ou tel onglet.
Sans nuire aux macros existantes.. ce qui serait le Top

Autrement dit selon l'utilisateur et son mot de passe attribué, il ne pourrait accéder qu'aux onglets dédiés.
Auriez-vous l'amabilité de secourir un vieux Monsieur... mais pas acariâtre du tout..
Soyez-en remercié par avance

A la suite d'un contact possible je pourrais échanger un fichier mais qui pèse lourd (18 Mo)

 

[_Thierry]

Bonsoir @Roger MEYER et Bienvenu dans XLD (Je viens de voir !!) ,
Bonsoir le Forum

C'est possible, pour celà il faut un onglet "Settings" qui contiendra quelque chose comme ça :

Ensuite dans WorkBook_Open on peut :
Trouver le User qui ouvre :

Private Sub Workbook_Open()

    MsgBox Environ("UserName")

End Sub

On peut savoir de quelles feuilles il a droit (en plus des normales) et on cherche les feuilles du "ayant droits"

Option Explicit
Option Compare Text

Private Sub Workbook_Open()
Dim RngSettings As Range, CellSettings
Dim TheUser As String
Dim TheSheets As Variant
Dim x As Integer

TheUser = Environ("UserName")

Set RngSettings = ThisWorkbook.Worksheets("Settings").Range("A2:A10")

For Each CellSettings In RngSettings
    If CellSettings = TheUser Then
        TheSheets = Split(CellSettings.Offset(0, 2), "/")
        For x = 0 To UBound(TheSheets)
            MsgBox TheSheets(x)
        Next
    End If
Next

End Sub

Et pour finir on joue avec "VERYHIDDEN" sur la propriété des Worksheets ...

Option Explicit
Option Compare Text

Private Sub Workbook_Open()
Dim RngSettings As Range, CellSettings
Dim TheUser As String
Dim TheSheets As Variant
Dim x As Integer
Dim WS As Worksheet

TheUser = Environ("UserName")

Set RngSettings = ThisWorkbook.Worksheets("Settings").Range("A2:A10")

For Each CellSettings In RngSettings
 
        If CellSettings = TheUser Then
                If CellSettings.Offset(0, 1) = "ADMIN" Then
                    Exit Sub
                End If
            TheSheets = Split(CellSettings.Offset(0, 2), "/")
        End If
Next CellSettings

For Each WS In ThisWorkbook.Worksheets
    Select Case WS.Name
        Case "Sommaire", "Tagada", "Tsoin Tsoin"
            'Do Nothing on laisse ces feuilles visibles à tous
        Case Else
            WS.Visible = xlSheetVeryHidden
    End Select

If TheSheets = Empty Then Exit Sub

     For x = 0 To UBound(TheSheets)
        If WS.Name = TheSheets(x) Then
           WS.Visible = xlSheetVisible
        End If
     Next x
Next WS

End Sub

Voilà, du coup ca ne change rien niveau Protection et Mot de passe, c'est complètement indépendant !!!

Ah oui pour l'ADMIM il faut avoir cette macro sous la main car le VeryHidden n'est PAS accessible depuis Excel (qui ne montrera JAMAIS ces "VeryHidden Sheets" dans les menus) mais uniquement accessible QUE depuis VBE en mode Design et VBA comme suit :
(Ce qui donne une "relative sécurité" par rapport aux utlisateurs lambbas...)

Sub ShowAllSheets()
Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets
    WS.Visible = xlSheetVisible
Next WS
End Sub

Voilà, je procèderais de la sorte, pour ne pas "casser" l'existant...

Bonne découverte et bonne soirée et on est jamais vieux tant qu'on fait du VBA !!!
@+Thierry

EDIT : Ajout d'un Contrôle (Exit Sub if Empty) si le User est Inconnu dans Settings
Ci-joint Classeur de Travail

 

[Roger MEYER]

Bonsoir Thierry,
Super sympa de me répondre..
Je vais donc demain me consacrer à cette découverte ... comprendre ce que vous me dites..
Parce que ce soir ce n'est pas très clair... Je ne suis pas un PRO... loin... très loin s'en faut..
Mais je vais m'accrocher et je reviendrais vers vous dès que possible.

Et puis Merci aussi pour vos encouragements
On n’est pas vieux tant que l’on cherche. [ Jean Rostand ]

 

[Jacky67]

Bonjour à tous
Autre proposition simple
Nom de l'utilisateur et mot de passe demandés au démarrage du classeur
Dans l'exemple en Pj

Utilisateur==>MDP
Moi======>111
Toi ======> 222
Lui === ===>333
Elle ======>444

Le tout en sachant que les mots de passe dans Excel sont illusoires

 

[_Thierry]

Bonsoir @Jacky67, re @Roger MEYER , le Forum

J'ai un plantage à l'ouverture du Userform, mais bon en le lançant à la main, attention toutefois car la Propriété des Feuilles Visible = False est facilement accessible depuis Excel (Contrairement à VeryHidden)

Bonne soirée
@+Thierry

 

[Jacky67]

Hello Thierry
Certes oui, cela est très facilement modifiable (en principe quand tous les tests sont terminés)
Cette ligne
If Sh.Name <> "Sommaire" Then Sh.Visible = False
par
If Sh.Name <> "Sommaire" Then Sh.Visible = 2
Tu écris avoir un plantage, y a t'il un message d'erreur?

Bonne nuit

 

[_Thierry]

Bonjour @Jacky67

Pour le plantage voici le message :

Sur Cette ligne

En fait on n'a pas cette errreur si on a Excel fermé et qu'il s'ouvre en lançant ton fichier, mais avec Excel et VBE ouverts sur d'autres fichiers ca pête ! Mais ca ne me surprend pas avec l'Evaluate [Capitaine Crochet] qui ne s'y retrouve pas ! C'est courant et c'est pour ça que je les évite au bénéfice de Déclarations et Sets complètement qualifiés... même si c'est plus long à écrire !

Pour la modif sur xlVeryHidden (2) oui c'est sûr c'est simple il restera aussi ici où se sera un peu plus délicat :
Sheets(.Cells(1, i).Text).Visible = Trim(UCase(.Cells(Lig, i))) = "X"

Bonne journée
@+Thierry

 

[GALOUGALOU]

bonsoir roger meyer bonsoir le fil bonsoir le forum

dans le classeur ci joint, à l'ouverture aucun onglet n'est visible. de plus une procédure oblige à activer les macros.
les utilisateurs ouvre uniquement les onglets désignés dans la feuille paramétrage par l'administrateur qui défini un identifiant et un mot de passe pour chaque utilisateur.
l'administrateur a tous les droits, et ouvre tous les onglets par identifiant admin mot de passe admin.
en page de garde, les différents identifiants et mots de passe sont affichés pour la prise en main du classeur.
la fonction dans le classeur qui vérifie le mot de passe

Function VerifMDP(Utilisateur As String, MdP As String) As Boolean
Dim rngTrouve As Range
VerifMDP = False 'par défaut, renvoie FAUX

With Sheets("parametrage") 'dans la feuille paramétrage
     'cherche, colonne A, le nom d'utilisateur saisi
    Set rngTrouve = .Columns(1).Cells.Find(Utilisateur, lookat:=xlWhole)
    If rngTrouve Is Nothing Then 'si il ne trouve pas
        VerifMDP = False 'la fonction renvoie faux
    Else 's'il le trouve
        'vérifie que le mot saisi feuille parametrgae colonne B est identique
        'au mot de passe saisi dans l'USF
        If rngTrouve.Offset(0, 1) <> MdP Then
            VerifMDP = False 'si FAUX
        Else
            VerifMDP = True 'si VRAI
        End If
    End If
End With
End Function

cordialement
galougalou

 

[Roger MEYER]

Bonjour @Jacky67 , @+Thierry , galougalou

Vous êtes super sympa..
Ceci dit je n'ai pas eu une minute pour moi et regarder avec beaucoup d'attention vos propositions.
Néanmoins la semaine prochaine sera mise à ce profit..
Les retraités sont souvent tenus à des déplacements fréquents, non prévus, tournés vers les petits enfants.
Je vous tiendrais évidemment au courant de mes pérégrinations dans Excel et VBE..
Je voulais juste vous dire que je suis toujours vivant et avec la ferme intention de me mettre au boulot.
Bonne soirée à tous..

 

[Roger MEYER]

Bonsoir Galougalou, bonsoir le fil,
Je me suis projeté dans la dernière proposition de GALOUGALOU..
Je pensais avoir tout compris.
J'ai donc simulé mes onglets pour exécuter la macro notamment à l'ouverture selon l'utilisateur et son mot de passe.
Je me retrouve avec une erreur et je ne comprend pas ce qui se passe. Je suis bloqué..
Probablement bêtement

"Sheets(.Cells(1, i).Value).Visible = True"
Pouvez-vous me sortir de cette ornière.
Sur cette simulation je souhaite conserver la feuille sommaire en permanence et le choix fait ensuite selon la feuille paramétrage.
La feuille ouverture ne sera conservé que par moi même avec celle du paramétrage.
En sauvegarde de l'ensemble.
merci par avance

 

[ChTi160]

Bonjour Roger
Bonjour le Fil ,le Forum
juste testé avec "ADMIN"
il semble que à partir de la feuille "Encaissements" les feuilles n'existent pas dans le fichier exemple d'ou l'erreur dans la Boucle .
jean marie

 

[_Thierry]

Bonjour @Roger MEYER , @ChTi160 , @GALOUGALOU , le Forum

Jean Marie a raison, tu n'as pas toutes les feuilles dans ce classeur de test qui sont listées dans "Paramètres".

Ceci dit, l'algo devrait savoir gérer ce genre de cas, je me suis donc permis d'ajouter une gestion d'erreur et un contrôle basic dans la Sub AfficheFeuilles(Utilisateur As String)

Bonne soirée
@+Thierry

 

[Roger MEYER]

Bonjour @+Thierry, @ChTi160 , @GALOUGALOU , le Forum
Messieurs vous avez été brillants..
Donc sur ce Fichier Excel Tout fonctionne.
Dès demain je transpose tout cela sur ma vraie base de données avec tous mes onglets et surtout mon onglet "Sommaire" sur lequel je dispose de toutes les liaisons macros qui masquent ou ouvrent les onglets en fonction des choix réalisés par l'opérateur.
Mais sans jamais donner accès aux onglets interdits du départ......!!!!????

J'espère n'avoir à subir aucune interférence entre cette interdiction de départ selon l'utilisateur et les macros d'accès à certains onglets d'autres part
Une bonne nuit là-dessus est nécessaire pour être frais et dispos..
Je n'hésiterais pas à revenir vers vous.
Encore MERCI

 

[GALOUGALOU]

re roger Meyer, bonsoir thierry, chti160, jacky67 bonsoir le forum
je ne l'avais pas précisé, mais le classeur proposé ne fonctionne correctement que si les onglets sont rigoureusement identiques en nom et en nombre.
mais grâce à thierry (merci thierry) le problème est contourné.
pour la deuxième réflexion

'espère n'avoir à subir aucune interférence entre cette interdiction de départ selon l'utilisateur et les macros d'accès à certains onglets d'autres part

si l'opérateur déclenche une macro qui fait appel à un onglet masqué une erreur sera signifiée.
ce qui implique une logique simple, masquer par une procédure les boutons inacessibles à un opérateur précis, et (ou) gérer dans toutes les macros une procédure d'erreur du style

     On Error GoTo GestionErreur
Sheets("BdD").Select
     Exit Sub
GestionErreur:
msgbox "Accés interdit"
Sheets("OUVERTURE").Select

un opérateur faisant appel a un onglet non autorisé, ici BdD, sera redirigé vers l'onglet OUVERTURE

de plus pensez à protéger votre code vba avec un mot de passe pour éviter des modifications par virtual basic et l'ouverture des onglets non autorisés.

un petit rappel, toutes ces protections guideront les opérateurs sur les bonnes pratiques, mais n’empêcheront pas les actions malveillantes par un opérateur mal intentionné.
cordialement
galougalou

 

[Roger MEYER]

Bonjour @+Thierry, @ChTi160 , @GALOUGALOU , le Forum
Messieurs si vous aviez été brillants.. hier..
Aujourd'hui il vous faut renouveler... j'ai besoin de vous.
Mais mon vrai fichier Excel pèse 4Mo - donc trop volumineux pour cette messagerie.
Il y a beaucoup de tableaux croisés dynamiques. Beaucoup d'onglets
J'aurais aimé que vous puissiez le voir et m'indiquer pourquoi je ne peux pas ouvrir toutes les feuilles (28) de l'onglet paramétrage. Accès avec (Admin). Je ne comprend pas.
Pourriez-vous me communiquer vos mails pour que je vous en fasse destination via WETRANSFER par exemple
Je n'ai pas pu tester toute la suite mais globalement mes macros sur l'onglet sommaire ouvre TOUT...
Ce qui ne devrait pas être le cas.
Exemple en cliquant sur la ligne FINANCIER je ne devrais pas pouvoir ouvrir l'onglet Encaissement qui est réputé inaccessible excepté pour Admin..
Merci de me faire savoir comment on peut avancer ensemble