Protéger ou déprotéger toutes les feuilles du classeur

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Protéger et déprotéger

C'est une fois encore la délicieuse et précieuse boucle VBA For Each qui va nous ouvrir la voie pour ajouter une fonctionnalité intéressante dans l'environnement Excel. Il est question de créer deux boutons. Le premier doit permettre de protéger toutes les feuilles du classeur avec mot de passe. Le second doit permettre de supprimer instantanément toutes les protections en vigueur sur les feuilles du classeur actif.

Classeur Excel à télécharger
Pour développer ces nouvelles fonctionnalités, nous suggérons d'appuyer les travaux sur un classeur Excel hébergeant plusieurs feuilles protégées et à juste titre.

• Télécharger le fichier proteger-deproteger-vba.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Puis, cliquer sur les boutons des bandeaux de sécurité pour libérer les ressources,

Nous découvrons un classeur Excel constitué de cinq feuilles.

• En bas de la fenêtre Excel, cliquer sur l'onglet Facturation pour activer sa feuille,

Comme son nom l'indique, cette feuille héberge une solution pour facturer les clients.

• Cliquer sur l'une des cellules de la colonne G, par exemple en G6,
• Puis, enfoncer la touche Suppr du clavier,

Comme vous pouvez le constater, la modification est refusée et pour cause ! Cette cellule comme les autres de la colonne G entre autres, porte une formule. Ces cellules sont donc verrouillées et cet état est rendu actif car la feuille est elle-même protégée.

• Cliquer maintenant sur une cellule de la colonne E, par exemple E6,
• Puis, saisir une quantité comme : 3 et valider avec la touche Entrée,

Cette fois, la modification est autorisée. En effet, bien que la feuille soit protégée et comme les cellules de cette colonne ne portent pas de formules, l'utilisateur est autorisé à intervenir pour définir la quantité de produits achetés par le client. C'est ainsi que la facture finale se construit automatiquement.

Il en va de même sur la majorité des autres feuilles de ce classeur. Toutes les cellules portant des calculs sont protégées tandis que toutes les autres sont libérées. Mais si l'utilisateur souhaite engager des modifications générales sur ces feuilles, une fonctionnalité consistant à déverrouiller massivement les protections peut s'avérer intéressante. Et c'est elle que nous souhaitons mettre en place.

Créer les procédures VBA
Les procédures à créer doivent être enregistrées dans le modèle Excel et doivent être associées à des boutons de ruban. Ainsi, elles seront disponibles pour toutes les utilisations d'Excel.

• Réaliser le raccourci clavier ALT + F11 pour basculer dans l'éditeur VBA Excel,
• Dans l'explorateur de projet sur la gauche, cliquer sur l'élément VBA Project (PERSONAL.XLSB),

Il s'agit du modèle Excel. Ainsi, nous le désignons explicitement.

• En haut de l'éditeur, cliquer sur le menu Insertion,
• Dans les propositions, choisir l'option Module,

Ainsi, nous créons un nouveau module que nous associons au modèle. Il est destiné à recevoir les deux macros VBA Excel devant permettre de protéger et déprotéger toutes les feuilles du classeur. Sa feuille de code apparaît au centre de l'écran. Naturellement, elle est encore vierge à ce stade.

• Dans la feuille de code, créer les procédures deProtegerTout et protegerTout, comme suit :

Sub protegerTout()

End Sub

Sub deProtegerTout()

End Sub

Ôter toutes les protections
Nous proposons de débuter par l'implémentation du code VBA de la seconde procédure, celle devant permettre de lever tous les verrous de protection sur toutes les feuilles du classeur actif. Pour cela, nous devons déclarer une variable représentant une feuille au sens large. Ainsi, nous l'utiliserons pour parcourir la collection des feuilles du classeur. Nous avons aussi besoin d'une variable capable de réceptionner le mot de passe de protection à demander à l'utilisateur.

• Dans la procédure deProtegerTout, ajouter les déclarations et affectations suivantes :

Sub deProtegerTout()
Dim feuille As Worksheet: Dim motPasse As String

motPasse = InputBox("Mot de passe de déprotection ?")

End Sub

Nous déclarons donc un objet de type Worksheet pour représenter n'importe quelle feuille du classeur. Puis, nous déclarons la variable motPasse comme un texte (As String). Et nous l'utilisons pour recevoir la saisie de l'utilisateur suite à la sollicitation engagée par une boîte de dialogue InputBox qui lui offre une zone de saisie.

Nous devons maintenant parcourir chacune des feuilles du classeur pour ôter les protections respectives en tenant compte du mot de passe en vigueur divulgué par l'utilisateur. Pour cela et comme nous l'avons annoncé, nous avons besoin d'amorcer une boucle For Each.

• A la suite du code de la procédure, créer la boucle For Each suivante :

...
For Each feuille In Worksheets
feuille.Unprotect motPasse
Next feuille
...

Grâce à notre objet feuille, nous parcourrons la collection des feuilles du classeur (Worksheets). Et pour chaque feuille passée en revue, nous exploitons la méthode Unprotect avec le mot de passe en paramètre pour faire sauter toutes les protections.

Désormais, nous devons actionner ce code par le biais d'un bouton à placer dans l'un des rubans, en haut de la fenêtre Excel.

• Enregistrer les modifications (CTRL + S) et revenir sur l'une des feuilles du classeur (ALT + Tab),
• Cliquer droit n'importe où sur le ruban actif,
• Dans le menu contextuel, choisir la commande Personnaliser le ruban,

La boîte de dialogue des options Excel apparaît.

• Déployer la liste déroulante du centre,
• Dans les propositions, choisir la catégorie Macros,
• Dans la liste du dessous, sélectionner la macro PERSONAL.XLSB!deProtegerTout,
• Puis la glisser dans un groupe d'un ruban,

Ensuite, il convient d'exploiter le bouton Renommer en bas de la boîte de dialogue pour lui attribuer un intitulé explicite et une icône représentative.

• Dès lors, cliquer sur le bouton Ok pour valider la création du bouton,
• Puis, cliquer sur l'onglet du ruban hébergeant ce nouveau bouton,
• Enfin, cliquer sur ce bouton pour déverrouiller toutes les feuilles du classeur Excel,
• A l'invite, taper le mot de passe : 1319,
• Revenir sur la feuille nommée facturation,
• Sélectionner de nouveau la cellule G6,
• Puis, enfoncer de nouveau la touche Suppr du clavier,

Cette fois, plus aucun message d'interdiction ne s'interpose. La cellule portant une formule, bien que verrouillée, a été vidée de son contenu puisque la feuille n'est plus protégée. Cela signifie que sa formule est perdue.

• Réaliser le raccourci clavier CTRL + Z pour annuler la suppression,

Vous pouvez effectuer le même constat en tentant des modifications sur des cellules portant des formules sur d'autres feuilles. Bien qu'étant originellement verrouillées, puisque la protection de chaque feuille a sauté, elles ne sont plus préservées de toute intrusion illicite.

Protéger toutes les feuilles
Maintenant, nous proposons de créer la procédure qui réalise le processus inverse. Elle doit protéger toutes les feuilles avec un même mot de passe. Le mécanisme est identique. Seule la méthode associée à l'objet feuille change.

• Revenir dans l'éditeur VBA Excel,
• Copier le code de la procédure deProtegerTout dans la procédure protegerTout,
• Puis, l'adapter comme suit :

Sub protegerTout()
Dim feuille As Worksheet: Dim motPasse As String

motPasse = InputBox("Mot de passe de protection?")

For Each feuille In Worksheets
feuille.Protect motPasse
Next feuille
End Sub

Dès lors, il convient d'associer cette procédure à un bouton de ruban. Après avoir cliqué sur ce dernier, si vous tentez les mêmes modifications que précédemment sur les cellules verrouillées, l'intervention est refusée. Les cellules originellement déverrouillées restent disponibles quant à elles.