Microsoft 365 Autoriser la modification de la couleur de cellules protégées par plage (et utilisateurs)

[Delsam]

Bonjour,

Nouvelle sur le forum, je coince sur un besoin.

J'ai une feuille protégée dans laquelle des plages de données doivent être renseignées par des utilisateurs (6 plage, 6 utilisateurs).
J'ai utilisé la rubrique "permettre la modification des plages" avec des codes puis j'ai protégé ma feuille (sélectionner les cellules vérouillées et cellule dévérouillées). La protection fonctionne bien s'il s'agit d'entrer du texte, mais il n'y a plus la possibilité d'ajouter une couleur à la cellule .

Le souci est que les cellules à renseigner par les utilisateurs doivent être remplies par des couleurs, en plus du texte, hors lorsque je protège la feuille, si je coche "format de cellule", la protection par plage ne s'applique plus pour la couleur et tous les utilisateurs peuvent faire des modifications.

Si vous avez des idées..

Merci.

 

[Dudu2]

Bonjour,
Est-ce que la couleur est au libre choix de l'utilisateur "propriétaire" de la plage ou bien est-ce qu'elle répond à des règles qui pourraient être mises sous forme de MFC ?

 

[Dudu2]

Sinon il faut faire du code en VBA mais ce n'est pas trivial car il n'y a aucun évènement de la feuille qui est déclenché sur une modification de format. Donc on ne sait pas si un format a été modifié !

Une première solution consiste à mémoriser les couleurs de fond de toutes les cellules des zones modifiables à l'ouverture du classeur et lorsqu'une cellule est sélectionnée ou avant l'enregistrement du classeur, vérifier que les couleurs de fond n'ont pas été modifiées sauf si l'édition de la cellule est autorisée (Range.AllowEdit property), auquel cas il faut l'accepter et stocker ce changement.

L'inconvénient de cette solution est que c'est juste pour contrôler la couleur de fond. Tous les autres paramètres du format (couleur & taille de caractère, bordures, etc...) resteront modifiables sans contrôle sauf si intégrés 1 à 1 dans ce système de protection.

Une deuxième solution consiste à avoir un bouton pour proposer le dialogue de couleur de fond.
La modification de Format doit être exclue de la protection de la feuille et le code doit connaître le mot de passe de protection de la feuille pour la déprotéger et la re-protéger le temps de la colorisation de la cellule par le code. Le bouton va évidement vérifier préalablement si l'édition de la cellule est autorisée (Range.AllowEdit property) ce qui implique que:
- soit l'utilisateur aura préalablement saisi le mot de passe lors d'une modification dans la plage
- soit le code devra détecter que le mot de passe n'a pas été encore saisi par une copie de la cellule candidate sélectionnée vers elle-même qui implicitement affichera le dialogue de saisie de mot de passe et partira éventuellement en erreur si le mot de passe n'a pas été correctement saisi, donc à protéger avec On Error et autoriser ou non en fonction.
L'inconvénient de cette solution est qu'elle doit connaître le mot de passe et donc doit le stocker dans le code qui, pour un utilisateur averti, est accessible facilement.

Note: j'ai vérifié que Range.AllowEdit = True pour une plage définie dans Révision / Autoriser la modification de plages et dont le mot de passe a été correctement saisi. Il n'est donc même pas nécessaire d'examiner ses plages ActiveSheet.Protection.AllowEditRanges.Item(x).Range.Address.

 

[Delsam]

Bonjour Dudu2,

Merci d'avoir pris le temps de me répondre. J'avoue que tout ne mets pas très claire dans ta réponse, je suis relativement novice sur les codes...

J'ai récupéré un code sur le site qui correspondait a des modifications et des calculs sur planning. Le principe me convient bien : avec MFC pour les couleurs. Sauf que le code déprotège la feuille et du coup mes plages initialement entrées par l'interface d'excel ne servent plus à rien.

Si j'essaye d'enlever la ligne de déprotection dans le code j'ai un message d'erreur "1004".
Mais comme je l'ai indiqué, je n'y comprend pas grand chose dans les codes, j'essaye juste d'adapter...

Ci-dessous le code utilisé
---------------------
Option Explicit 'oblige à déclarer toutes les variables

Public R As Byte 'déclare la variable R
Public G As Byte 'déclare la variable G
Public B As Byte 'déclare la variable B
Public cel As Range 'déclare la variable cel

Public Sub defcoul() 'définition de la couleur des cellules
Dim Coul As Long 'déclare la variable Coul

'ActiveSheet.Unprotect 'déprotège l'onglet
Coul = RGB(R, G, B) 'définit la variable Coul

With Selection
.Interior.Color = Coul 'attribue la couleur de remplissage Coul à la sélection

End With

ActiveCell.Select

'ActiveSheet.Protect 'protège l'onglet
End Sub

----------------------

Sinon pour préciser le souhait de la direction, cela concerne des plannings. L'utilisateur doit pouvoir entrer des infos sur la plage qui lui est autorisé uniquement, notamment de la couleur, mais il n'est pas vraiment restreint sur sa plage, par contre il ne doit pas intervenir sur celles des autres.

C'est uniquement le notion de couleur qui m'ennuie car sinon l'interface d'excel ferait bien le taf...

Merci.

 

[Dudu2]

Bonjour,

Tu n'iras pas loin avec ce bout de code.

Tu as défini des Plages par utilisateur (uniquement sur mot de passe spécifique, car je suppose que tu n'as pas défini les utilisateurs des Plages dans la rubrique <Autorisations> de celles-ci ).
Le problème est double:
- Colorer des cellules (simple)
- S'assurer que ce coloriage est fait uniquement dans la Plage définie pour l'utilisateur (complexe).
Le texte de ma réponse #3 te donne 2 options avec chacune un inconvénient.

1 - Est-ce que tu es OK pour que l'utilisateur puisse modifier le format des cellules de toutes les Plages A L'EXCEPTION de la couleur de fond (on peut aussi ajouter à l'exception la couleur et taille de caractère ou d'autres éléments de format que tu voudrait protéger) des Plages qui ne lui appartiennent pas ? Si oui -> Solution 1 (demande plus de ressources CPU et peut-être des petits temps de latence à la sélection)

2 - Est-ce que tu es OK pour avoir le mot de passe de la feuille dans le code VBA qu'un utilisateur averti peut consulter et donc passer outre ses limitations ? Si oui -> Solution 2 (la plus simple à mettre en œuvre)

Je vais fabriquer un exemple de la 2ème solution.

 

[Delsam]

Bonjour,
Suis Ok pour les points 1 et 2, c'est pas gênant.
Pour les autorisations des utilisateurs, je les ai effectivement définies dans la rubrique "permettre la modification des plages". Dans notre collectivité, la rubrique nous permet d'accéder à notre active directory ce qui permet de gérer aisément les droits d'écriture sur les plages et c'est transparent pour l'utilisateur. Sauf que cela ne fonctionne pas pour la mise en forme des cellules sur plage...

 

[Dudu2]

J'ai basé la solution sur le fait que les plages sont protégées par mot de passe dédié à chaque utilisateur. En lisant ta réponse, j'en viens à en douter et si l'autorisation est directement donnée sur une plage à un utilisateur sans mot de passe, ce serait infiniment plus simple car on n'aurait pas le mot de passe à gérer.

Donc merci de préciser si l'autorisation est basée sur mot de passe, sur utilisateur de l'Active Directory ou sur les 2 (ce qui me semble peu utile sur tu as l'Active Directory).

J'ai été un peu optimiste avec la Solution 2 (voir fichier joint).
3 x Hélas ! La dé-protection/re-protection de la feuille pour changer la couleur de fond ré-initialise le fait que le mot de passe a été saisi pour les plages autorisées en modification, d'où la nécessité de le re-saisir à chaque fois ce qui n'est pas très acceptable.

Pour limiter les effets de ce problème, la saisie du mot de passe est faite par le code VBA du bouton (et non via Excel) de manière à déprotéger à nouveau automatiquement les plages déjà déprotégées avant l'opération de dé-protection/re-protection de la feuille.

Cependant, ce mécanisme ne fonctionne qu'en passant pas le bouton car lors d'une modification de contenu, c'est Excel et lui-seul qui procède à la saisie du mot de passe sans qu'on puisse intervenir.
Donc il y a quand même des situations où le mot de passe sera demandé plusieurs fois en fonction de la séquence des opérations.
Et là on n'y peut pas grand chose.
- SAUF à avoir AUSSI les mots de passe des Plages dans le code ou
- SAUF à avoir un bouton dédié à la dé-protection préalable de la plage valide autant pour les couleurs que pour le contenu (solution la plus ergonomique) ou
- SAUF à ne pas avoir de mot de passe sur les plages et en baser la sécurité uniquement sur les Utilisateurs de l'Active Directory (solution la plus simple).

 

[Dudu2]

Sinon voici la 1ère solution qui fonctionne pas si mal.

Pas besoin de stocker aucun mot de passe, mais le contrôle des modifications de couleur de fond se fait a posteriori, sur un changement de sélection ou de feuille ou de tentative d'enregistrement du classeur puisqu'Excel n'offre pas d'évènement sur un changement de format.

Comme le principe s'appuie sur la comparaison d'état des cellules des Plages à chaque évènement avant/après (quand c'est justifié, c.a.d. pour les Plages encore protégées et couvertes par la sélection précédente susceptibles d'avoir subi des modifications) j'ai limité par paramètre le nombre de cellules d'une Plage à 1000.
Les comparaisons sont rapides mais évidemment pour un grand nombre de plages constituées d'un grand nombre de cellules il est possible qu'une certaine latence apparaisse lors des changements de sélection. Dans le fichier de test cela est non observable, mais à vérifier sur un cas réel.

Comme déjà indiqué, à part la couleur de fond, toutes les modifications de format sur une Plage non attribuée sont possibles puisque la modification de Format des cellules est autorisée (cochée) dans la protection des feuilles, sauf à en définir les éléments supplémentaires (taille, couleur de caractères, etc...) à protéger qu'il faudrait alors intégrer dans le code, ce qui n'est pas un gros problème.

Le code s'applique à n'importe quel classeur et supporte des Plages à modifications autorisées sur des feuilles différentes.

Fichier mis à jour le 12 Octobre à 12h07

 

[Delsam]

Merci dudu2 pour le temps passé, je teste les solutions demain.

 

[Delsam]

Bonjour,

Ce que tu proposes me semble pas mal, en test en tout cas, car je n'arrive pas à l'intégrer à mon fichier... je ne comprends pas bien comment sont détectées les protections dans le code (enfin s'il n'y avait que ça... ).

A tout hasard, je te transmets une partie du fichier concerné. actuellement il y a un userform mais qui ne prend pas en compte les droits paramétrés sur les plages. Pour l'écriture dans les cellules les autorisations/restrictions fonctionnent bien.

Les autorisations des plages se font bien pour les utilisateurs de l'active directory uniquement.

Sinon j'ai pensé à un contournement, mais je sais pas si c'est possible : ne faire apparaitre le userform qu'à la sélection des plages autorisées (fermeture si sélection d'une cellule d'une autre plage) ? Je pense que cela pourrait peut-être être plus simple...

Bref, si tu as encore un petit moment à m'accorder, je t'en remercie. Sinon merci pour le temps passé sur ce projet.

 

[Dudu2]

Les autorisations des plages se font bien pour les utilisateurs de l'active directory uniquement.

Il n'y a donc pas de mot de passe sur les Plages. Correct ? STP confirme !

Je regarderai ton fichier ce soir. Je n'ai pas saisi le rapport entre UserForm et Plages, j'espère comprendre avec le fichier.

 

[Delsam]

Non pas de mot de passe.
Il n'y a pas de lien entre le userform et les plages. Il servirait juste a faciliter le renseignement des cellules, mais il peut être zappé.
Merci.

 

[Dudu2]

Ok donc s'il n'y a pas de mot de passe contrairement à ce que j'avais interprété en lisant cette phrase du post initial:

J'ai utilisé la rubrique "permettre la modification des plages" avec des codes puis j'ai protégé ma feuille

Par conséquent les Plages sont par défaut modifiables en contenu pour les Utilisateurs de l'Active Directory qui ont été autorisés. Ils n'ont pas à saisir un mot de passe pour modifier leur(s) Plages(s).

La solution 2 devient donc possible sans l'inconvénient de la re-saisie du mot de passe après dé-protection/re-protection de la feuille.

 

[Dudu2]

Dommage que tu n'aies pas envoyé le fichier avant. Là, c'est simple.
Je me suis basé sur le code existant pour utiliser le UserForm.

Je me suis défini comme utilisateur sur la 1ère plage (User). A supprimer dans ton environnement.

A noter que le VBA a besoin de connaître le mot de passe de la feuille, donc il est donc stocké dans la constante Private Const ProtectionFeuille = "del" que tu peux / dois adapter le cas échéant.

 

[Delsam]

Super ! Merci beaucoup, c'est exactement ce dont j'avais besoin.
Merci encore.
Bonne journée.