Afficher ou masquer 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 :


Afficher ou masquer automatiquement les feuilles

Dans cette nouvelle formation, pour gagner du temps et pour plus de confort, nous proposons d'ajouter une fonctionnalité intéressante dans l'un des rubans Excel. Il s'agit d'un bouton bascule. Au premier clic, il doit masquer instantanément toutes les feuilles du classeur, sauf la feuille active. Mais s'il détecte que les feuilles sont déjà masquées, au clic, il doit les réafficher toutes. De plus, nous proposons de masquer ces feuilles avec une sécurité supplémentaire, pour que l'utilisateur lambda ne soit pas en mesure de les afficher lui-même, en intervenant sur les onglets.

Classeur Excel à télécharger
Pour développer cette fonctionnalité, nous proposons d'appuyer les travaux sur un classeur Excel hébergeant plusieurs feuilles.

• Télécharger le classeur afficher-masquer-feuilles-au-clic.xlsx en cliquant sur ce lien,
• Double cliquer sur le fichier réceptionné pour l'ouvrir dans Excel,
• Dès lors, cliquer sur le bouton Activer la modification du bandeau de sécurité,

Nous débouchons sur la première feuille de ce classeur. Elle est nommée Synthese. Elle consolide les résultats des six autres feuilles qui portent les intitulés respectifs des six premiers mois de l'année. Ce sont elles que nous devons masquer, une fois les travaux de l'utilisateur terminés. Ce classeur avait été utilisé pour présenter l'intérêt de la fonction Excel Indirect.

La procédure dans le modèle
Pour débuter, nous devons créer la procédure de code VBA dans le modèle Excel, le personal.xlsb, de manière à ce que notre nouveau bouton de ruban soit fonctionnel pour toutes les utilisations d'Excel et pas seulement pour ce classeur.

• 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 VBAProject(PERSONAL.XLSB),

Ainsi, nous désignons explicitement le modèle Excel.

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

Ainsi, nous créons un nouveau module dans le modèle Excel. Sa feuille de code apparaît au centre de l'écran. Elle est forcément vierge pour l'instant. Vous pouvez aussi choisir de créer la procédure dans un module existant dans ce modèle Excel.

• Dans cette feuille de code, créer la procédure afficherMasquer comme suit :

Sub afficherMasquer()

End Sub

Le bouton de macro
Maintenant qu'elle existe, nous proposons tout de suite de la lier à un bouton de ruban pour l'exécuter au clic. Nous nous occuperons d'implémenter son code par la suite.

• Basculer sur la feuille Excel (ALT+ Tab),
• Cliquer droit n'importe où sur le ruban actif,
• Dans le menu contextuel, choisir la commande Personnaliser le ruban,

Ainsi, nous affichons la boîte de dialogue des options Excel.

• Déployer la liste déroulante du centre,
• Puis, choisir la catégorie Macros,
• Depuis la liste du dessous, cliquer et glisser la macro afficherMasquer dans un ruban existant,

Ensuite, il est opportun d'exploiter le bouton Renommer en bas à droite de la boîte de dialogue pour épurer l'intitulé du bouton et lui associer une icône représentative.



Les icônes ne sont pas très nombreuses. L'idée est de ne pas trop s'écarter du thème.

• Cliquer sur le bouton Ok pour valider la création du bouton,

Désormais, un clic sur le bouton dans le ruban où vous l'avez positionné, exécute le code de la procédure VBA afficherMasquer. Cependant ce dernier est encore vide. Donc rien ne se produit pour l'instant.

La déclaration des variables
Et justement, pour débuter le développement de ce code VBA, nous avons besoin de déclarer des variables dont une représentant une feuille du classeur et capable de les parcourir toutes par le biais d'une boucle For Each.

• Revenir dans l'éditeur VBA Excel,
• Dans les bornes de la procédure afficherMasquer, ajouter les déclarations suivantes :

Sub afficherMasquer()
Dim feuille As Worksheet: Dim test As Boolean
Dim nbFeuilles As Byte: Dim i As Byte

End Sub

Nous déclarons la variable feuille comme un objet de type Worksheet. Son rôle est de parcourir toutes les feuilles du classeur pour les afficher ou les masquer. Donc, il est impératif qu'elle soit du même type que les objets à analyser. Nous utiliserons la variable test que nous déclarons comme un booléen pour savoir si ces feuilles sont visibles ou masquées. C'est grâce à elle que nous saurons animer ce bouton bascule pour savoir s'il est question de masquer ou d'afficher les feuilles. La variable nbFeuilles est déclarée comme un entier court (Byte). Elle doit récolter le nombre de feuilles composant ce classeur. Nous l'utiliserons pour parcourir ces feuilles afin de déceler leur état, affichée ou masquée. C'est grâce à la variable de boucle i que nous entreprendrons ce traitement récursif jusqu'à la dernière (nbFeuilles).

Initialiser les variables
Dès lors, deux variables peuvent être initialisées. Il s'agit de celle pour connaître le nombre de feuilles présentes dans ce classeur et de la variable booléenne.

• A la suite du code VBA, ajouter les deux affectations suivantes :

...
nbFeuilles = Sheets.Count
test = False
...

C'est la propriété Count de la collection Sheets qui renvoie cet indice sur le nombre de feuilles et que nous stockons donc dans la variable nbFeuilles. Ensuite, nous initialisons la variable booléenne à False. Cela signifie implicitement, par défaut et jusqu'à preuve du contraire, que les feuilles sont visibles et donc qu'elles peuvent être masquées.

Savoir si les feuilles sont masquées
C'est cette variable booélenne dont nous devons basculer l'état si d'aventure nous rencontrons au moins une feuille masquée. Et pour cela, nous pouvons les parcourir avec une boucle For Next classique ou encore avec une boucle For Each. C'est son état qui indiquera à la boucle suivante s'il est question d'afficher ou de masquer toutes les feuilles du classeur Excel.

• A la suite du code VBA, créer la boucle suivante :

...
For i = 1 To nbFeuilles
If (Sheets(i).Visible = xlSheetVeryHidden) Then
test = True
Exit For
End If
Next i
...

Nous parcourons donc toutes les feuilles de la première (1) à la dernière (nbFeuilles). Si l'une d'entre elles est hautement sécurisée (Sheets(i).Visible = xlSheetVeryHidden), nous en concluons que c'est le cas pour les autres. Nous basculons donc l'état de la variable booléenne (test = True) et mettons fin à l'exécution de la boucle (Exit For).

Masquer ou afficher les feuilles
Maintenant, si les feuilles sont visibles (test=False) et qu'il ne s'agit pas de la feuille active, nous pouvons toutes les masquer. Dans le cas où les feuilles sont déjà masquées (test=True), nous devons les rendre visibles pour que ce bouton bascule fasse son office. Pour les parcourir toutes et puisqu'il n'est pas question cette fois d'avorter le traitement tant que la dernière feuille n'est pas atteinte, nous pouvons déclencher une boucle For Each grâce à notre objet nommé feuille.

• A la suite du code VBA, créer la boucle For Each suivante :

...
For Each feuille In Sheets
If (test = False And feuille.Name <> ActiveSheet.Name) Then
feuille.Visible = xlSheetVeryHidden
ElseIf test = True Then
feuille.Visible = xlSheetVisible
End If
Next feuille
...

Pour chaque feuille du classeur (For Each feuille In Sheets), si elles sont visibles (test = False) et que dans le même temps (And), il ne s'agit pas de la feuille active (feuille.Name <> ActiveSheet.Name), alors nous les basculons tour à tour en état masqué sécurisé grâce à la propriété Visible de l'objet feuille. En revanche, si les feuilles sont déjà masquées, nous basculons la propriété Visible de ce même objet dans l'état inverse (xlSheetVisible), pour les afficher toutes.

Visibles ou non visibles au clic
Il est temps de tester ce code VBA, au demeurant très simple.

• Enregistrer les modifications (CTRL + S) et basculer sur la première feuille du classeur,
• Dans le ruban personnalisé, cliquer sur le bouton afficherMasquer que nous avons créé,

Comme vous pouvez le voir, toutes les feuilles du classeur sont aussitôt masquées sauf la feuille qui était active au moment de l'action et ce, avec un niveau de sécurité élevé. En effet, si vous réalisez un clic droit sur l'onglet encore visible, la commande Afficher est indisponible.



Les feuilles sont donc protégées des modifications. Maintenant, si vous cliquez de nouveau sur ce bouton bascule, le code VBA détecte automatiquement leur absence et les affiche toutes de nouveau.



Voilà donc une fonctionnalité fort pratique et qui fait gagner du temps pour un code VBA Excel très simple, vous en conviendrez :

Sub afficherMasquer()
Dim feuille As Worksheet: Dim test As Boolean
Dim nbFeuilles As Byte: Dim i As Byte

nbFeuilles = Sheets.Count
test = False

For i = 1 To nbFeuilles
If (Sheets(i).Visible = xlSheetVeryHidden) Then
test = True
Exit For
End If
Next i

For Each feuille In Sheets
If (test = False And feuille.Name <> ActiveSheet.Name) Then
feuille.Visible = xlSheetVeryHidden
ElseIf test = True Then
feuille.Visible = xlSheetVisible
End If
Next feuille

End Sub