Protection avec restrictions

Bonjour à tous,

Je commence tout juste à me former à VBA grâce au site et à ses cours. Je m’y intéresse car je rencontre des difficultés sur un de mes fichiers de travail.

Sur un fichier Excel, je tiens une base de données dans laquelle sur différentes feuilles, j’ai de nombreux tableaux, qui sont liés entre eux (Recherche V … )

Seulement j’aimerais mettre se tableau à la disposition de tous dans un partage sur le DRIVE en y mettant des protections.

Ainsi au niveau de l’onglet Révision sur chaque feuille je rentre mes restrictions différentes en fonction de leur contenu (tableaux croisée dynamique, filtre, case à coché…) Cela fonctionne très bien.

Lorsqu'il s’agit de faire des mises à jour (plusieurs fois dans le mois) je me retrouve à ôter et mettre la protection trop souvent, d’où ma recherche de protection automatique de mes feuilles.

Sur le net en suivant divers Forum, j’ai trouvé des codes que j’ai essayés. Certains fonctionnent, d’autre moins bien. Les codes que j’ai trouvé de plus, verrouillent les feuilles sans tenir compte des différentes restrictions, et m’empêchent de verrouiller manuellement (dans révision) même si j’utilise le même mot de passe.

Seulement j’aimerais savoir comment puis-je verrouiller/déverrouiller mes feuilles par une macro qui prend en compte les restrictions que je mets pour chacune des feuilles en fonction de leur contenu ?

Et faire en sorte que le bouton associé à cette macro, soit par exemple sur la première feuille qu’il faudra bien évidemment déverrouiller manuellement pour accéder à la macro.

PS : Les restrictions dont je parle sont :

• Sélectionner les cellules verrouillées
• Utiliser les filtres automatiques
• Utiliser les rapports de tableau croisé dynamique

bonjour et bienvenu sur le forum

alors oui c'est possible mais encore faut-il avoir une structure d'un fichier exemple.

combien d'onglets, sur quel(s) onglet(s) il faire telle ou telle protection...

en gros un fichier exemple serait necessaire

fred

Tout d’abord, je vous remercie Fred, de la réponse ; c’est la première fois que je participe à un forum.

Je vous mets alors à disposition le fichier qui paraîtra très certainement incohérent, dans la mesure où j’ai supprimé une partie des informations « sensibles » pour tenter de préserver la confidentialité du document, de l’entreprise, mais aussi des personnes mentionnées.

Feuille 1 :

La première feuille est la page de garde, par laquelle j’aimerais rentrer le mot de passe à l’avenir dans le document. J’aimerais également qu’après enregistrement des modifications, à la prochaine ouverture, nous ne retrouvions à nouveau sur cette page.

Les liens de cette feuilles doivent pouvoir être accessible lorsque le documents est protégé

Feuille 2 ;4 ;5 ;6 ;7 ;8 ;9 ;11 : Restriction souhaité :

- Sélectionner les cellules déverrouillées

Au niveau des feuilles PA 2014 2015 2016… ces feuilles seront copiées au fils des années

Feuille 3 :

Restrictions souhaités :

• Sélectionner les cellules déverrouillées
• Utiliser les filtres automatiques

Restrictions souhaités :

• Sélectionner les cellules déverrouillées
• Utiliser les filtres automatiques
• Utiliser les rapports de tableau croisés dynamique
• Tri

Pourrez-vous m’expliquer par étape la marche à suivre si ce n’est pas trop demandé ?

bonjour

je ne vois pas de fichier joint....

dans un premier temps voir ce que fait l'enregistreur de macro :

ruban affichage /macro/enregistrer une macro

et en faire 3 macros

correspondant au 3 situations de verrouillage.

puis on verra pour adapter en fonction de chaque feuille.

pour faire en sorte de mettre toujours la meme feuille active a l'ouverture d'un document c'est relativement facile

utiliser la fonction mettre une macro dans thisworkbook du projet VBA

Fred

Dsl pour le fichier je viens de m’apercevoir qu'il est trop lourd (1.25Mo) et même en le compressant il reste trop lourd (400 Ko).

J'ai alors soulagé le fichier de nombreuses informations pour qu'en compressant il soit de 260 kio,

Au début de mes essaies, je pensais justement faire une macro des protections, mais je me suis rendu compte que la macro ne prenait pas en charge ces opérations d'où mon intérêt depuis peu à VBA qui pourrait être la Solution.

Bonjour,

sans macro tu as 'Révision / Permettre la modifications des plages'

Comme plage tu prends toute la feuille (=1:1048576) et dans 'Autorisations d'accès...' ton ajoute ton identifiant ('Ajouter / Avancé... / Rechercher', là tu choisis dans la liste).

Il ne t'embêtera plus.

eric

Bonjour,

Je suis rentré dans ces nouveaux paramètres que je ne connaissais pas encore....

Mais du coup:

- il faudra que je fasse cette manipulation pour chacune des feuilles du classeur? Et lors d'une mise à jour, déverrouiller chaque feuille si je mets un mot de passe?

- Ou si j'ai un peu saisi le sens de cette manipulation, cela implique en mettant une adresse mail ou un identifiant, que je pourrai accéder à ce dossier ouvertement qu'a partir d'un seul et même ordinateur? Ce dernier ne m'appartient pas, mais au groupe pour lequel je travail. et donc peut être échangé à tout moment. De plus, le PC à déjà dans ces paramètres... énormément de paramètres, de restrictions, (en cliquant, un message d'info est apparu stipulant qu'une certaine base de donnée contient déjà plus de 10000 identifiants ou informations et que seuls les 10000 premier serait affiché...)

(Etc... Je vais avouer, un peu compliqué pour un non initié.)

Il n'y aurait il pas d'une part, de codes à insérer dans VBA au niveau de chacune des feuilles, permettant d'y mettre les différentes restrictions en fonction du besoin lors de la protection ? et d'autre part, que cette protection ne demande qu'une seul fois (au niveau de la page de garde) un mot de passe qui permettrait de déverrouiller et verrouiller toutes les autres?

Après, si la solution que m'a proposée Eric est belle et bien la seule, Je serai contraint de m'y mettre "malheureusement".

Merci,

bonjour

je peux essayé de trouver une solution vba mais ce qu'il faut savoir concernant les mots de passe sous excel, on peut les faire sauté plus vite que son ombre pour une personne avertie, ou avec une simple recherche sur google on trouve les programmes faisant sauté les mdp....

par contre j'ai jamais fais un verrouillage autorisant les filtres et les TCD a voir....

fred

Je te remercie Fred !

Et puis Si je cherche à verrouiller c'est simplement pour éviter les mauvaises manip des utilisateurs qui ne vont que consulter pour le moment... Après ceux qui prendrons le temps de chercher à faire sauter les mots de passe sont vraiment déterminés et ils ne me pose pas du tout de problème... rien de très confidentiel...

Si vous parvenez à trouver une solution je suis vraiment intéressé... et cela ne me dérange pas que vous me proposiez de tester différents codes à améliorer.. Que l'on procède par étape en fin de compte.

il faudra que je fasse cette manipulation pour chacune des feuilles du classeur?

Oui, on ne peut pas faire sur une sélection 3D

Et lors d'une mise à jour, déverrouiller chaque feuille si je mets un mot de passe?

Non, justement.

Tu es reconnu et tu n'as plus besoin de déverrouiller les feuilles.

cela implique en mettant une adresse mail ou un identifiant, que je pourrai accéder à ce dossier ouvertement qu'a partir d'un seul et même ordinateur?

C'est lié au classeur, pas au poste.

Par contre ça implique que tu sois connecté sur le PC avec ton identifiant. Si vous êtes plusieurs à l'utiliser sans changer d'identifiant ce n'est pas viable.

Après, si la solution que m'a proposée Eric est belle et bien la seule

Non, une simple macro peut déprotéger et reprotéger tout ou partie des feuilles d'un coup.

C'était pour mettre les différentes possibilités à ta portée.

eric

bonsoir voici une proposition si j'ai bien compris...

par contre j'ai un message d'erreur a l'ouverture du fichier lier au TCD fait sur une feuille protégée que je n'arrive pas à effacer...

a l'ouverture du fichier activation des macros :

passage sur la feuille 1 en feuille active et vérouillage des feuilles par defaut

j'ai mis un bouton pour l'onglet 1 pour deverouillé le tout

le mot de passe est définit dans le code VBA il ne faudra pas oublié de verrouillé le code en affichage pour eviter de le voir apparaitre

ici mdp = toto

fred

Bonsoir,

de mon coté, j'ai résolu le problème de protection en n'en mettant pas...

Ceci dit je travail en réseau. Donc mon ordi avec le fichier source sans protection et à chaque ouverture fermeture du fichier une copie sur le réseau. C'est la copie du réseau qui est accessible par le reste du personnel.

Ils peuvent donc déstructurer le fichier, à la première ouverture du fichier source, il sera remplacer par la version "à jour".

Si vous travailler sur le même ordi, alors il y a la possibilité de mettre le fichier source dans un dossier personnel "protégé" à votre nom. Là il faut peut-être passer par l'administrateur de votre parc informatique.

@ bientôt

LouReeD

Je te remercie de la réponse aussi détaillée Eric.

Je pense être davantage intéressé par la solution Macro /VBA...

Si il y a une marche a suivre, je suis tout ouïe.

Bonjour,

je crois que tu as raté la réponse de fred en haut.

eric

Effectivement je n’avais pas eu de notifications pour le post de Fred, Merci Eric.

J’ai pris le temps d’étudier le travail de « chef » que tu as pu faire sur le fichier, et franchement je suis déjà satisfait de la protection.

Une grande partie de ce que j’ai pu expliquer à été traité sur les différentes feuilles.

Néanmoins serait-il possible d’entreprendre encore quelques modifications ?

Feuille 1 :

• Les objets peuvent toujours être déplacés malgré les restrictions :
• Les liens sont accessible en mode verrouillé : OK

Feuilles 2 : OK

Feuilles 3 :

• Utilisation des filtres : OK
• Les listes déroulantes sont encore accessibles mm si la modification n’est pas permise :

Feuilles 4 :

• Les filtres des tableaux sont accessibles : ok
• En mode verrouillé, on ne peut pas ajouter de « champ à inclure » : 
• Les utilisateurs peuvent changer le nom des tableaux :

Je me suis aperçu qu’après déverrouillage, mise à jour quelconque du fichier, enregistrement, et fermeture ; Il se trouve qu’à la réouverture du fichier les feuilles sont en mode déverrouillé et donc permettent des modif.

Grace à ce que tu as mis dans VBA, j’ai ajouté un bouton pour le module verrouiller afin de protéger l’ensemble des feuilles.

- Je me suis aperçu également que je pouvais ôter la protection manuellement aussi : ok

Seulement serait-il possible qu’à l’ouverture du fichier la page affichée soit automatiquement celle de la page de garde ?

Je suis d’autant plus satisfait car, en insérant les codes VBA que tu m’as partagés, sur le vrai document, il à l’air de fonctionner presqu’à la perfection.

En effet, en réinsérant les boutons, la protection fonctionne correctement, mais au verrouillage, un message d’erreur s’affiche :

«

Erreur d’exécution’1004’ :

Mot de passe non valide. Vérifier que la touche VERR.MAJ n’est pas activée et que vous respectez la bonne case.

FIN Déblocage Aide

»

Le Déblocage me renvoie dans VBA à ce niveau :

Sub deprotection()

Dim demande_mdp As String

Dim i As Byte

init

demande_mdp = InputBox("renseigner le mot de passe")

If demande_mdp <> mdp Then

MsgBox ("Mot de passe erroné")

Exit Sub

End If

For i = 2 To Sheets.Count

Sheets(i).Unprotect mdp

Next

End Sub

Franchement je pense qu’en tenant compte de ces modification mon problème est réglé …. ! Franchement je suis content et j’ai hâte.

Je n'ai pas compris lorsque tu as fait référence à TCD . (mais je l'ai vu dans les code VBA)

bonsoir

Feuille 1 :

- Les objets peuvent toujours être déplacés malgré les restrictions :

Fait

Feuilles 3 :

• Utilisation des filtres : OK
• Les listes déroulantes sont encore accessibles mm si la modification n’est pas permise :

Feuilles 4 :

• Les filtres des tableaux sont accessibles : ok
• En mode verrouillé, on ne peut pas ajouter de « champ à inclure » : 

- Les utilisateurs peuvent changer le nom des tableaux :

je viens de remarquer cela mais je ne sais pas comment faire

Je me suis aperçu qu’après déverrouillage, mise à jour quelconque du fichier, enregistrement, et fermeture ; Il se trouve qu’à la réouverture du fichier les feuilles sont en mode déverrouillé et donc permettent des modif.

Seulement serait-il possible qu’à l’ouverture du fichier la page affichée soit automatiquement celle de la page de garde ?

je viens de mettre une macro qui verrouilles les feuilles a la fermeture du fichier évidemment si les macros sont actives plutôt qu'a l'ouverture du fichier ainsi que le passage a la page 1.

pour qu'a l'ouverture du fichier cela soit la page 1 c'est déjà fait mais comme les securtiés des macros doit être activé il faut attendre que l'utilisateur clique activé les macros pour que cela soit fait. si les utilisateurs n'activent pas les macros cela n'est pas possible....

Sheets(i).Unprotect mdp

est-ce que le mot de passe utiliser pour verrouiller la feuille est le même que celui dispo dans le code

mdp = "toto" ?? si c'est pas le cas normal que cela se produise....

fred

Bonsoir,

une technique vu sur le Web pour "forcer" l'utilisateur à mettre les macros en marche :

à la fermeture du fichier, cacher toutes les feuilles, sauf celle nommée "Macro", sur cette feuille un message informe de mettre les macros pour accéder au fichier. donc à la prochaine ouverture elle seule sera affichée et demandera de mettre les macros en marche, une fois les macros activées, il y en a une à l'ouverture du fichier qui montre toutes les feuilles et cache la feuille "Macro", la boucle est bouclée...

@ bientôt

LouReeD

Fred,

le travail que tu as accompli est tout simplement génial.... Je ne vois même pas comment je peux dire ça autrement. Je te remercie d'avoir passé du temps sur mon sujet. Le fichier que j'ai imaginé est réel... "For real"

Je me rend compte que les codes VBA que tu as inscrit me permettent de faire fonctionner un fichier qui comporte d'autres feuilles que tu n'a mm pas pris en compte ... C'est tout simplement génial qu'ils s'appliquent à n'importe quelle feuille rajoutée.

J'aimerais pouvoir comprendre ce que tu as fait et pouvoir bidouiller juste un petit peu... Mais je vais continuer à étudier ce que tu as fait pour tenter de comprendre. (c'est dommage qu'il n'y aie pas de commentaires au niveau des codes comme dans les cours!)

Je te remercie, c'est juste génial

voici le code commenté

fred

Ca à l'air un peu plus compréhensif, je te remercie.

Dernière question je pense que c'est la dernière, :

Il y t il une possibilité que le mdp soit en étoile (*) pour ne pas qu'il s'affiche à l'écran ? lol ! Ce n'est pas très important, mais j'y pensais ce matin quand je testais le fichier et qu'il y avait du monde autour de moi !