Table des matières automatique en VBA Excel

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


Sujets que vous pourriez aussi aimer :


Sommaire automatique cliquable

Dans ce nouveau volet sur les astuces VBA Excel, nous focalisons l'attention une fois de plus sur les onglets du classeur. Ici, il est question de créer un code VBA dans le modèle Excel pour construire automatiquement un sommaire cliquable sur une nouvelle feuille, en fonction des intitulés des onglets.

Classeur Excel à télécharger
Pour ce développement, nous suggérons d'appuyer l'étude sur un classeur hébergeant plusieurs feuilles.

• Télécharger le fichier compressé sommaire-onglets.rar en cliquant sur ce lien,
• Puis, le décompresser dans le dossier de votre choix,

Comme vous le constatez, la décompression livre le classeur Excel accompagné d'un fichier texte. Ce dernier héberge un code VBA pour créer une nouvelle feuille en première position du classeur actif, avec quelques réglages de mise en forme. Nous l'avons obtenu par enregistrement d'une macro automatique. Puis, nous avons quelque peu épuré le code livré pour éliminer les lignes inutiles.

• Double cliquer sur le fichier du classeur pour l'ouvrir dans Excel,

Nous débouchons sur la première feuille de ce classeur. Elle est nommée Synthese.



Elle est accompagnée de six autres feuilles portant les intitulés respectifs des six premiers mois de l'année.

Créer une nouvelle feuille en VBA
Pour que cet outil soit fonctionnel pour toutes les utilisations d'Excel, nous devons développer ce code dans le modèle Excel, le Personal.xlsb. Puis, nous l'associerons à un bouton que nous placerons dans un ruban personnalisé. Nous proposons de commencer par implanter le code VBA permettant de créer automatiquement une nouvelle feuille en première position du classeur.

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

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

• A la racine du dossier de décompression, double cliquer sur le fichier texte pour l'ouvrir,
• Sélectionner tout son contenu (CTRL + A), puis le copier (CTRL + C),
• Dès lors, revenir dans l'éditeur VBA Excel,

Ce code VBA, nous devons l'implanter dans un module du modèle Excel, qu'il s'agisse d'un nouveau module (Insertion / Module) ou d'un module existant.

• Coller ce code VBA dans un module du modèle,

Comme vous pouvez le constater, cette procédure se nomme nvelleFeuille.

Appeler la procédure VBA
Nous proposons maintenant de détacher le développement à suivre dans une autre procédure. Naturellement, elle doit commencer son traitement par l'appel de cette procédure nvelleFeuille.

• Sous la procédure nvelleFeuille, construire la procédure creerSommaire, comme suit :

Sub creerSommaire()
nvelleFeuille

End Sub

L'appel est ainsi établi au début du traitement pour que la nouvelle feuille soit d'abord créée afin d'accueillir le sommaire automatique à construire.

Créer le bouton de ruban
Avant de poursuivre, nous souhaitons établir la liaison entre un bouton à placer dans un ruban personnalisé et cette procédure. C'est ainsi qu'il permettra de construire un sommaire automatique pour n'importe quel classeur.

• Revenir 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,

Nous affichons ainsi la boîte de dialogue des options Excel.

• Avec la première liste déroulante, choisir la catégorie Macros,
• Avec la liste du dessous, glisser la macro créée dans un groupe d'un ruban, sur la droite,

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



Dès lors après avoir validé la création par le bouton Ok de la boîte de dialogue et en activant l'onglet hébergeant ce nouveau bouton, vous le voyez en effet dans sa section, matérialisé par l'icône choisie. Si vous cliquez dessus, vous constatez la création d'une nouvelle feuille effectivement nommée Sommaire. Elle accueille de même un titre du même intitulé en cellule B2. Bien sûr, si vous l'avez créée, il convient de supprimer cette feuille avant de poursuivre.

Construire le sommaire
Il est maintenant question de parcourir toutes les feuilles du classeur afin de récolter leurs noms. C'est sur cette base que nous devons créer les liens internes. Nous devons donc engager une boucle. Elle doit débuter son analyse à l'indice 2 pour exclure la première feuille, celle du sommaire.

• Revenir dans l'éditeur VBA Excel entre les bornes de la procédure creerSommaire,
• A la suite du code VBA, créer la boucle suivante :

Sub creerSommaire()
nvelleFeuille
For i = 2 To Worksheets.Count

Next i
End Sub

C'est la propriété Count de la collection Worksheets qui renseigne sur le nombre de feuilles présentes dans ce classeur. Et c'est grâce à elle que nous faisons tourner la boucle de la deuxième à la dernière feuille. A chaque passage dans cette boucle, nous devons maintenant créer un lien hypertexte pointant sur la feuille en cours d'analyse. Et pour cela, nous allons exploiter la méthode Add de la collection Hyperlinks.

• Dans les bornes de la boucle, ajouter les deux instructions VBA suivantes :

ActiveSheet.Hyperlinks.AddAnchor:=Cells(i + 2, 2), Address:="", SubAddress:=Worksheets(i).Name & "!A1", TextToDisplay:=Worksheets(i).Name
Cells(i + 2, 2).Font.ThemeColor = xlThemeColorDark1

Plusieurs paramètres sont essentiels. Tout d'abord, grâce à l'attribut Anchor, nous définissons l'emplacement de l'ancre, ici en colonne B et à partir de la ligne 4 (i+2). Nous ignorons l'attribut Address car nous ne souhaitons pas créer un lien absolu pour une adresse Web par exemple. C'est la raison pour laquelle nous réglons l'attribut SubAddress. Nous pointons sur la cellule A1 (!A1) de la feuille en cours d'analyse (Worksheets(i).Name). Puis, nous définissons le texte à afficher avec le nom de la feuille de destination grâce à l'attribut TextToDisplay. Enfin, nous réglons la police de la cellule en cours sur une couleur claire pour que le lien hypertexte ressorte bien sur ce fond noir.

Créer le sommaire
Il est maintenant temps de tester cette nouvelle fonctionnalité que nous avons greffée dans un ruban personnalisé.

• Enregistrer les modifications (CTRL + S) et basculer sur la feuille Excel (ALT + Tab),
• Puis, cliquer sur le bouton creerSommaire dans le ruban personnalisé,

Comme vous pouvez le voir, le sommaire est automatiquement créé. Chaque lien pointe bien vers la feuille qu'il désigne. Certes, nous pourrions envisager de soigner quelque peu l'apparence de ce sommaire avec notamment l'ajout de bordures de cellules. Mais, c'est uniquement la technique qui nous intéressait ici.