Comment créer un menu dynamique sur Excel ?

Aujourd’hui, je vous propose de découvrir comment mettre en place automatiquement un menu dynamique qui va permettre de sélectionner une feuille de calcul d’un simple clic.

Téléchargement

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

Tutoriel Vidéo

1. Présentation

Pour ce nouveau tutoriel, nous allons nous inspirer d’un précédent cours que nous avions déjà vu il y a quelque temps et dans lequel nous avions mis en place un sommaire automatique dans une feuille de calcul

Avant d’aller plus loin je vous propose donc de revenir sur la manière dont nous avions construit celui-ci.

Bien entendu nous n’y reviendrons que très rapidement, mais si voulez en savoir davantage je vous propose de consulter l'article en question en cliquant ici.

Pour mettre en place le sommaire automatique nous avions alors créé une macro-commande en VBA qui permettait de réaliser les opérations suivantes.

- Tout d’abord nous avions commencé par insérer une nouvelle feuille de calcul dans le classeur que nous avions de nommé « SOMMAIRE » :

    Sheets.Add before:=Worksheets(1)
    ActiveSheet.Name = "SOMMAIRE"

Mais pour éviter de créer une 2^de feuille portant le même nom et dans lequel nous retrouverions le même contenu (c’est-à-dire le sommaire en question), il était au préalable nécessaire de vérifier que cette feuille de calculs n’existait pas déjà.

Si tel était déjà le cas, nous pouvions alors la supprimer :

    If Worksheets(1).Name = "SOMMAIRE"  Then
        Application.DisplayAlerts = False
        Worksheets(1).Delete
        Application.DisplayAlerts = True
        
    End If

- Ensuite, une fois la feuille insérée dans le classeur nous pouvions commencer la construction du sommaire.

Pour cela nous avions alors passé en revue toutes les feuilles de calcul au sein du classeur afin de créer un lien hypertexte permettant de pointer directement sur chacune d’entre elles :

    Dim feuille As Worksheet
    For Each feuille In Worksheets
        ActiveSheet.Hyperlinks.Add  anchor:=Cells(ligne, 1), Address:="", SubAddress:="'" &  feuille.Name & "'!A1", TextToDisplay:=feuille.Name
        ligne = ligne + 1
    Next

Aujourd’hui nous allons aller un petit peu plus loin en créant un menu automatique au niveau de chacune des feuilles de calcul qui reposera en grande partie sur le fonctionnement de ce sommaire.

Ce menu sera régénéré automatiquement à chaque fois que nous allons afficher une nouvelle feuille de calcul.

Il ne sera donc plus nécessaire de créer une feuille sommaire.

L’autre grande différence résidera dans le fait que nous allons utiliser des boutons, et non pas de simples liens hypertextes inclus dans des cellules.

2. Création du sommaire

Avant de commencer la création de notre menu en tant que tel, nous allons insérer une nouvelle colonne dans chaque feuille de calcul, avant la première colonne.

Pour créer automatiquement cette colonne sur toutes les feuilles du classeur, nous les sélectionnons en utilisant la méthode suivante :

• Nous commençons par cliquer sur l’onglet de la première feuille

• Puis, tout en maintenant la touche [Maj] enfoncée, nous cliquons sur le dernier onglet :

Ensuite, nous insérons la colonne, en sélectionnant la colonne A, puis en appuyant sur les touches [Ctrl]+[+] :

Une fois la colonne insérée, nous pouvons lui donner une couleur de fond (par exemple du gris) :

Les feuilles sont maintenant préparées, nous pouvons créer notre macro-commande en développant une petite macro-commande en VBA.

Pour que ce tutoriel soit accessible à tout le monde, nous allons revenir dans le détail sur aspect de cette dernière.

Mais si vous souhaitez aller plus loin dans la découverte du langage VBA, vous pouvez évidemment consulter la formation disponible sur le blog en cliquant ici.

Pour créer notre macro, nous allons lancer l’éditeur de code VBE (pour Visual Basic Editor), qui est l’éditeur de code intégré dans les différentes applications de la suite Office et qui permet de développer des macro-commandes en VBA.

Pour cela, nous appuyons simplement sur les touches [Alt]+[F11] :

VBE s’ouvre alors et pour pouvoir saisir la macro-commande, nous allons y insérer un module.

Un module est une feuille de saisie de code dans laquelle nous allons pouvoir saisir nos macros et fonctions.

Pour insérer un nouveau module, nous nous rendons dans le menu Insertion > Module :

Une fois le module inséré, nous allons pouvoir y insérer la macro en utilisant le mot-clé Sub, suivi du nom de la macro, puis nous validons en appuyant sur la touche [Entrée] :

Sub sommaireDynamique()
End Sub

Comme nous l’avons vu dans la partie précédente, pour récupérer le nom de toutes les feuilles de calculs du classeur, nous allons utiliser une boucle For Each.

Mais avant cela, il sera nécessaire de créer une variable que nous appelons simplement feuille et que nous typons en tant que Worksheet, c’est-à-dire une feuille de calcul :

    Dim feuille As Worksheet

Puis nous pouvons mettre en place la boucle qui permet de passer en revue toutes les feuilles du classeur :

    For Each feuille In Worksheets
    Next

À l’intérieur de cette boucle, nous disposons donc d’un objet qui porte le nom de feuille et qui reprend la feuille en cours d’analyse.

Pour créer un élément qui permettra d’accéder directement à la feuille en question, nous allons insérer un objet de type Zone de nom.

Pour cela, nous utilisons l’instruction ActiveSheet.Shapes.AddTextbox(Orientation, gauche, haut, largeur, hauteur), dans laquelle les arguments sont :

• Orientation : permet de définir l’orientation du texte, msoTextOrientationHorizontal ou 1 pour orientation horizontale classique,
• Gauche : position du bord gauche de la forme,
• Haut ; position du bord haut de la forme,
• Largeur : largeur de la forme,
• Hauteur : hauteur de la forme

        Dim bouton As Shape
        Set bouton =  ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 0, [a1].Width,  30)

Ici, nous plaçons le bouton tout en haut à gauche de la feuille de calcul (aux coordonnées 0, 0), avec la largeur de la cellule A1 également et non y définissons une hauteur de 30 points

Sauf que pour tenir compte de l’ensemble des boutons qui vont constituer le menu, nous allons revoir rendre la position haute du menu dynamique.

Pour cela, nous allons introduire une nouvelle variable que nous appelons positionY et que nous devons déclarer avant la boucle For Each :

    Dim positionY As Integer
    positionY = 0
    
    For Each feuille …

De cette manière, nous pouvons utiliser cette variable au niveau de l’argument Haut :

        Set bouton =  ActiveSheet.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, positionY,  [a1].Width, 30)

Et avant de quitter la boucle, nous allons incrément la variable positionY de 30 points, afin de préparer la variable pour le bouton suivant :

        positionY = positionY + 30

Maintenant que le bouton est inséré, nous allons pouvoir le personnaliser, en commençant insérer le texte qui correspond au nom de la feuille :

        bouton.TextFrame2.TextRange.Characters.Text =  feuille.Name

Puis nous pouvons modifier l’apparence du bouton en lui appliquant un style :

bouton.ShapeStyle = msoShapeStylePreset13

>

Et pour finir, nous allons pouvoir insérer le lien hypertexte qui permettra d’accéder directement à la feuille de calcul, en utilisant la méthode que nous avions utilisée dans le tutoriel sur la mise en place du sommaire (ActiveSheet.Hyperlinks.Add) :

        ActiveSheet.Hyperlinks.Add Anchor:=bouton,  Address:="", SubAddress:="'" & feuille.Name &  "'!A1"

Le lien hypertexte est donc ancré sur le bouton et redirige vers la cellule A1 de la feuille en cours d’analyse.

Et voilà, nous pouvons maintenant tester la macro en appuyant sur la touche [F5], puis en allant visionner le résultat dans la feuille de calcul :

Le menu est bien fonctionnel, en revanche, si nous changeons de feuille, celui-ci ne sera pas généré…

Pour créer un menu automatiquement sur chaque feuille, nous allons tout simplement utiliser un évènement qui va analyser les actions effectuées sur le classeur pour exécuter une action à un moment donné.

Pour en savoir plus sur les évènements vous pouvez consulter le chapitre dédiée extrait de la formation sur l’apprentissage de VBA en cliquant ici.

Pour lancer la génération du menu à chaque changement de feuille de calcul, nous allons insérer l’évènement Workbook_SheetActivate.

Pour cela, nous double cliquons sur la feuille de classeur dans l’explorateur de projet de VBE :

Puis nous sélectionnons les événements de classeur (Workbook) dans le menu déroulant situé au-dessus de la feuille de code :

Et dans le second menu, nous choisissons SheetActivate :

VBE ajoute alors l’évènement dans la feuille de code :

Private Sub Workbook_SheetActivate(ByVal  Sh As Object)
 
End Sub

Il suffit ensuite d’appeler la macro sommaireDynamique à l’intérieur de cet évènement :

Private Sub Workbook_SheetActivate(ByVal  Sh As Object)
    Call sommaireDynamique
End Sub

Maintenant, nous pouvons sélectionner une autre feuille pour visionner le menu :

Par contre, si nous revenons sur une feuille dans laquelle se trouve déjà inséré le menu, celui-ci sera ajouté une seconde fois…

Pour empêcher cela, nous allons tout simplement donner un nom personnalisé à chaque bouton, ce qui nous permettra de pouvoir les supprimer avant de créer le menu.

Pour ajouter un nom, nous revenons dans la boucle For Each, puis nous ajoutons la ligne :

        bouton.Name = "menu_" &  feuille.Name

Maintenant, nous allons insérer une boucle, juste avant la boucle principale, qui nous permettra de passer en revue tous les objets de la feuille, à la recherche d’objet dont le nom commence par « menu_ »

    Dim sBouton As Shape
    For Each sBouton In ActiveSheet.Shapes
        If sBouton.Name Like "menu_*" Then
            sBouton.Delete
        End If
    Next

Nous avons donc déclaré un nouvel objet (sBouton) que nous utilisons dans une boucle For Each, et lorsque le nom de celui-ci commence par « menu_ », alors nous le supprimons.

Pour finir notre macro, nous allons vouloir mettre en évidence la feuille active dans le menu, et pour cela, nous remplaçons la ligne suivante :

        bouton.ShapeStyle = msoShapeStylePreset13

Par :

        If feuille.Name = ActiveSheet.Name Then
            nouveauBouton.ShapeStyle = msoShapeStylePreset14
        Else
            nouveauBouton.ShapeStyle = msoShapeStylePreset13
        End If

C’est-à-dire que nous cherchons à savoir si le nom de la feuille en cours d’insertion dans le menu correspond au nom de la feuille active.

Lorsque c’est le cas, nous appliquons le style msoShapeStylePreset14, et dans le cas contraire, nous conservons le style msoShapeStylePreset13 :

Et voilà, la macro est maintenant terminée !

Il ne reste plus qu’à ajuster la taille de la colonne A sur toutes les feuilles de calcul :

L’intérêt de générer à nouveau le menu lors de chaque changement de feuille, c’est que si nous modifions le nom d’une feuille, celle-ci sera automatiquement modifiée sur le menu :

De la même manière, toute nouvelle feuille sera automatiquement incluse :