Techniques avancées Excel
Des boutons pour changer de feuille Excel
Apprenez à intégrer à vos data books un système de menu de navigation Excel afin de proposer une interface améliorée aux utilisateurs du fichier.
Introduction
Dans cet article nous allons voir comment créer en toute simplicité un menu de navigation permettant de parcourir entre les feuilles de votre classeur. Démonstration ci-dessous :
Télécharger le fichier d’exemple
Pourquoi et quand mettre en place un menu de navigation Excel ?
Ce système de menu de navigation permet de donner une surcouche à l’allure très professionnelle qui ravira les utilisateurs de votre fichier. Avant de continuer, précisons que cette bonne pratique prend uniquement son sens pour les fichiers Excel comportant un nombre important de feuilles . Cela s’applique notamment très bien à un databook ou un outil de reporting que vous serez amené à partager. Réaliser un menu de navigation Excel sur un fichier de 4 ou 5 feuilles tombe, à notre sens, dans la catégorie du superflu.
En terme de pré-requis, il est fortement recommandé que votre classeur soit rangé et structuré. Si ce n’est pas encore le cas, nous vous invitons à consulter notre article sur l’organisation et le rangement d’un classeur
La réalisation du menu de navigation Excel
Le timing
La réalisation du menu intervient en finalisation de fichier, lorsque vous savez que la structure des feuilles est figée, ou ne changera que peu, et que vous avez notamment déjà attribué les noms définitifs de vos feuilles (qui, rappelons le, dans le cas de grands classeurs, doivent être le plus court possible afin de maximiser le nombre de feuilles apparaissant à l’écran). Si vous avez consulté l’article mentionné ci-dessus, cela devrait être le cas.
Réalisation du menu (structure et apparence)
Première étape évidente, il faut créer une nouvelle feuille où l’on va créer un menu qui pour l’instant ne sera pas fonctionnel. La structure de ce dernier se fait au cas par cas et devrait suivre celle de votre fichier tandis que la mise en page ne dépend que de vous.
Dans notre cas, nous avons quatres catégories :
- Summary by region,
- Summary by country,
- Summary by product category,
- Specifics.
Dans chaque catégorie, nous allons ajouter autant de cellules que la catégorie comporte de feuilles tout en leur attribuant un nom. Vous pouvez librement choisir le nom, il n’a pas besoin d’être identique à celui de la feuille visée. Dans la suite de l’article, nous appellerons ces cellules des boutons.
Nous obtenons un menu mis en forme mais pas encore fonctionnel :
Afin de faire comprendre à l’utilisateur que le texte est un “bouton” cliquable, n’hésitez pas à donner une apparence similaire aux liens que l’on voit communément (souligné et/ou couleur différente et/ou en gras et/ou précédé d’un symbole).
Lier boutons et feuilles : les liens hypertextes
Excel offre la possibilité d’ajouter un lien hypertexte à une cellule permettant ainsi d’accéder à une ressources externe (site internet, e-mail, etc.) ou à une ressource interne au classeur. Pour chaque bouton de notre menu, nous allons utiliser un lien hypertexte qui fera référence à une cible interne : la référence d’une cellule de la feuille vers laquelle nous voulons diriger notre utilisateur. Ainsi, au clique sur le texte contenu dans la cellule, la feuille en question sera sélectionnée. Nous aurons donc créé un créer un bouton excel pour changer de feuille.
Pour chaque bouton, il faut :
- Ouvrir la fenêtre d’ajout de lien hypertexte:
- Clique droit sur la cellule > Lien hypertexte; ou
- Sélectionner la cellule > +
- Cliquer sur “Emplacement dans ce document” dans l’encadré “Lier à :
- Sélectionner la feuille désirée sous la catégorie “Référence de cellule”
- Vérifier que A1 soit indiqué dans le champs “Tapez la référence de la cellule”
L’ajout d’un lien hypertexte à une cellule change son format et lui attribue le format par défaut des liens hypertextes Excel. Pour contourner ce petit désagrément, il suffit d’ajouter les liens hypertextes à tous les boutons sauf un, qui va jouer le rôle de “cache” du format préalablement défini. Ensuite, copiez/collez le format du dernier bouton sur les boutons pour lesquels vous venez d’ajouter un lien hypertexte. Pour finir, traitez votre dernier bouton de la même manière (ajout du lien hypertexte puis copier/coller le format).
Maintenant, lorsque vous cliquez sur le texte à l’intérieur de votre cellule, vous êtes automatiquement redirigé vers la feuille désirée. Le plus gros du travail est fait mais vous allez rapidement vous rendre compte que l’on ne vient de faire que le chemin aller et qu’il va falloir implémenter une solution pour faire le chemin inverse, à savoir permettre à l’utilisateur de revenir vers le menu.
Ajout dans toutes les feuilles d’un bouton de redirection vers le menu
Le principe reste le même, nous allons utiliser la même fonctionnalité et créer un bouton “Menu” dans chacune des feuilles du classeur. Pour respecter une cohérence et une continuité dans la navigation du fichier, la best practice est de placer le bouton menu tout le temps au même endroit, et nous vous recommandons une cellule placée en haut à gauche de la feuille. Cette étape est similaire aux explications fournies ci-dessus : il suffit d’ajouter un lien hypertexte renvoyant à notre feuille de menu. On le fait pour une première cellule dans l’une des feuilles du classeur, on définit un format, puis on copie-colle cette même cellule dans toutes les feuilles (le copier/coller préserve bien évidemment le lien hypertexte).
Lors de la création d’un lien hypertexte, le chemin d’accès enregistré par Excel est un texte du type « Feuil1!A1 ». Le lien hypertexte n’est donc pas dynamique et ne changera pas si la cible du lien venait à changer. Cela est notamment le cas lors de la modification du nom d’une feuille : si « Feuil1 » est renommée en « Calculations », l’adresse « Feuil1!A1 » n’existera plus et votre lien hypertexte renverra une erreur. Il ne faut donc plus renommer les feuilles une fois que les liens ont été créés : c’est une des raisons pour laquelle on vous conseille d’effectuer le menu à la fin, une fois que le nom des feuilles ne changera plus (ou presque plus).
Aller (beaucoup) plus vite
Si vous avez réalisé le menu de navigation en suivant l’article, vous avez du constater que l’étape la plus longue est celle qui consiste à rajouter les liens hypertexte à chaque cellule pour créer les boutons. Parce qu’un élément productif automatise, nous avons conçu une macro VBA permettant de réaliser en un seul clic la création d’une liste comportant le nom de toutes les feuilles tout en y associant le lien hypertexte adéquat. Si vous n’êtes pas à l’aise en VBA, ne vous inquiétez pas car vous n’aurez rien à faire d’autre que d’importer la macro et de l’exécuter. Avant d’aller plus loin, ci-dessous une vidéo vous permettant de voir ce que fait la macro :
Importer la macro VBA
Si vous ne savez pas comment importer la macro, vous pouvez consulter notre article pour savoir comment importer et exécuter une macro, ou alors suivre les étapes suivantes :
- Ouvrir le fichier comportant votre menu,
- Ouvrir la console VBA : Onglet Développeur > Visual Basic ( + ),
- Sélectionner sous Projet – VBAProject > [votre fichier] > Microsoft Excel Objects > ThisWorkbook (double clic),
- Placer le curseur dans la fenêtre principale et coller le code VBA (voir ci-dessous),
Code VBA de la macro create_menu :
Sub create_menu() Dim rowStart As Integer Dim colStart As Integer Dim mySh As String mySh = Application.ActiveSheet.Name rowStart = Application.ActiveCell.Row colStart = Application.ActiveCell.Column 'boucle qui va naviguer entre les feuilles For Each sh In Sheets If IsEmpty(Sheets(mySh).Cells(rowStart, colStart).Value) Then rowStart = rowStart + 1 colStart = colStart Else MsgBox ("You can't create the menu here, range contains values") Exit Sub End If Next rowStart = Application.ActiveCell.Row colStart = Application.ActiveCell.Column 'boucle qui va naviguer entre les feuilles For Each sh In Sheets 'on ajoute le nom de la feuille dans une cellule Sheets(mySh).Cells(rowStart, colStart).Value = CStr(sh.Name) Sheets(mySh).Cells(rowStart, colStart).Select 'on ajoute dans cette même cellule le lien hypertexte renvoyant vers la feuille en question ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="#" & "'" & CStr(sh.Name) & "'" & "!A1" rowStart = rowStart + 1 colStart = colStart Next End Sub |
Réalisation du menu de navigation Excel avec du VBA
Avec la méthode VBA, les étapes de création du menu sont légèrement chamboulées et s’agencent de la manière suivante :
- Exécuter la macro VBA
- Structurer, renommer et mettre en forme le menu et ses boutons
- Ajouter le bouton menu à chaque feuille du classeur
Les points 2 et 3 ont déjà été expliqués au-dessus. Pour l’exécution de la macro, il suffit de :
- Ouvrir la fenêtre Macro : Onglet Développeur > Macro (Alt + F8),
- Cliquer sur sur la macro “create_menu” (si vous ne l’avez pas renommée),
- Cliquer sur “Exécuter”.
La macro s’exécute et une liste avec le nom de toutes vos feuilles apparaît. Chaque cellule comporte le lien hypertexte redirigeant vers la feuille en question. Il ne vous reste plus qu’à mettre en page pour proposer un menu propre mais surtout extrêmement pratique aux utilisateurs de votre classeur.
Suite à l’exécution d’une macro, impossible de revenir en arrière. Or, il serait regrettable que lors de l’exécution de la macro, la liste générée supprime le contenu de vos cellules. Ne vous inquiétez pas, le code VBA que l’on vous propose a été conçu de telle sorte que l’exécution s’arrêtera net si la liste générée avait été amenée à supprimer du contenu. Par conséquent, si un message d’arrêt apparaît il suffit de sélectionner (avant l’exécution de la macro) une cellule qui a sous elle suffisamment de cellules vides.
Pour comprendre le code VBA de la macro, un article y est dédiée où l’on vous explique son fonctionnement.
Lire le prochain article pour découvrir comment booster la présentation de vos tableaux de bord grâce aux segments.