3 façons de lister les onglets d’un classeur

11 Comments

Je vous présente ici 3 façons de lister les onglets d’un classeur. La première méthode utilise une macro Excel 4 qui fonctionne encore sur les versions modernes d’Excel.  La deuxième méthode est une formule personnalisée en VBA et la dernière est une routine (Sub) écrite en VBA. Pour cette dernière, je vous présente 4 versions.

Le fichier est disponible en téléchargement ici : Truc002_Lister les onglets.xlsm

Supposons que nous voulons lister le noms des onglets des feuilles de calculs suivantes. L’onglet en rouge est une feuille “Graphique”.

image

Macro Excel 4 – LIRE.CLASSEUR

La première méthode consiste à utiliser la macro Excel 4 suivante : LIRE.CLASSEUR(1). Les macros (ou formules) Excel 4 ne peuvent pas être utilisées directement dans une cellule de Excel mais on peut l’utiliser comme formule dans le gestionnaire de noms. Cette formule retourne le nom du classeur entre [ ] suivi du nom d’un onglets. Voici comment faire:

Aller dans le gestionnaire de noms (CTRL + F3).
Créer un nouveau nom “NO” (pour noms onglets)
Écrire la formule suivante : =TRANSPOSE(LIRE.CLASSEUR(1))
Sélectionner les cellules A6 à A13
Taper =NO et valider avec CTRL + SHIFT + ENTER

Vous devriez avoir le résultat suivant.

 image

On remarque que le noms des onglets se trouvent après le crochet ]. On peut donc extraire le noms des onglets en se créant une formule nommée NomOnglets dans le gestionnaire de noms.

La formule est la suivante :
=TRANSPOSE(DROITE(LIRE.CLASSEUR(1),NBCAR(LIRE.CLASSEUR(1))-TROUVE(“]”, LIRE.CLASSEUR(1))))

On extrait la partie à droite du ] de la chaîne de caractère à l’aide de la fonction TROUVE.
Pour que les données apparaîssent dans une colonne, on utilise la fonction TRANSPOSE.

image

VBA – Différences entre Worksheets, Sheets et Chart

Avant de poursuivre, voyons la différence entre Worksheets, Sheets, et Chart en VBA. Un classeur est composé de différents onglets. Un onglet peut être une feuille de calcul (feuille avec des cellules) ou une feuille de type graphique. Pour éviter des erreurs, il est important de comprendre la différence entre chacun lorsque l’on code en VBA.

Une feuille de calcul est un objet Worksheet qui fait partie de la collection Worksheets. La collection Worksheets contient tous les objets Worksheet d’un classeur.

L’objet Worksheet appartient également à la collection Sheets. La collection Sheets contient toutes les feuilles du classeur (aussi bien les feuilles de graphique que les feuilles de calcul).

La collection Sheets peut contenir des objets Chart ou Worksheet.

Ainsi, si nous ne voulons pas que notre liste contienne le noms des feuilles graphiques, nous utiliserons la collection Worksheets au lieu de Sheets.

Formule personnalisée en VBA pour lister les onglets

En VBA, la fonction suivante ne donnera que le noms des onglets de type feuille de calculs car on utilise la collection Worksheets. La formule retourne un tableau. Il s’agit donc d’une formule matricielle que l’on doit valider avec CTRL  + MAJ + ENTER.

Function NomsOnglets2()
Dim Arr() As String 'Tableau contenant le noms des onglets
Dim i As Integer

ReDim Arr(1 To Application.Caller.Rows.Count)
For i = 1 To Worksheets.Count
Arr(i) = Worksheets(i).Name
Next i

NomsOnglets2 = Application.Transpose(Arr)

End Function

 

En sélectionnant C6:C13, on obtient ceci :

image

Routine en VBA pour lister les onglets

On peut arriver au même résultat avec une macro.  Le fichier disponible en téléchargement contient 4 macros illustrant les différences dans l’utilisation de l’objet Worksheets et Sheets.

Sub ListerOnglets()
' Liste uniquement les onglets de type "Feuille"
' Les onglets de type "Graphique" ne sont pas inclus
'
Dim i As Integer

For i = 1 To Worksheets.Count
Cells(5 + i, 4) = Worksheets(i).Name
Next i
End Sub
Sub ListerOnglets2()
' Liste les onglets de type "Feuille" et "Graphique"
'
Dim i As Integer

For i = 1 To Sheets.Count
Cells(5 + i, 5) = Sheets(i).Name
Next i
End Sub
Sub ListerOnglets3()
' Liste uniquement les onglets de type "Feuille"
' Les onglets de type "Graphique" ne sont pas inclus
'
Dim rg As Range, ws As Worksheet

Set rg = [F6]

For Each ws In ThisWorkbook.Worksheets
rg = ws.Name
Set rg = rg.Offset(1, 0)
Next ws
End Sub
Sub ListerOnglets4()
' Liste les onglets de type "Feuille"
' La macro donne une erreur quand elle rencontre un onglet
' de type "Graphique" car nous définission la variable
' ws de type Worksheet. Une feuille graphique est de type Chart
' Il faut donc faire attention dans l'utilisation de
' Worksheets et Sheets
'
Dim rg As Range, ws As Worksheet

Set rg = [G6]

For Each ws In ThisWorkbook.Sheets
rg = ws.Name
Set rg = rg.Offset(1, 0)
Next
End Sub

 

Si vous aimez cet article, n’hésitez pas à le partager dans vos réseaux sociaux ou à laisser un commentaire.

Abonnez-vous au blog pour recevoir directement les nouveaux articles par courriel.

11 comments on “3 façons de lister les onglets d’un classeur

  1. Julie on said:

    Bonjour,
    C’est super mais je n’arrive qu’à faire la première étape avec le nom du fichier avec le nom des onglets.

    La formule suivante affiche une erreur sur NBCAR.
    =TRANSPOSE(DROITE(LIRE.CLASSEUR(1),NBCAR(LIRE.CLASSEUR(1))-TROUVE(“]”, LIRE.CLASSEUR(1))))

    Que faut il que je fasse ?

    Merci

    • GCExcel on said:

      Bonjour,
      Attention, il n’y a pas de backslash () avant les guillemets.
      Sinon, remplacer les virgules (,) par des point-virgules (;) selon votre configuration de Excel.

      =TRANSPOSE(DROITE(LIRE.CLASSEUR(1),NBCAR(LIRE.CLASSEUR(1))-TROUVE(“]”, LIRE.CLASSEUR(1))))

      (edit : les backslash s’ajoutent automatiquement, donc le problème est peut-être les virgules. Sinon, vous pouvez m’envoyer votre classeur par courriel. Voir la page “Contact”.)

  2. saussissoche on said:

    Bonjour,
    Merci de votre excellent article très intéressant . Seulement je constate donc qu’il n’existerait aucun moyen de faire la même chose sans macro, sans fichier xlsm ?
    La fonction cellule ne pourrait pas être utilisable ?
    En vous remerciant
    Cordialement

  3. PELLET on said:

    Bonjour,
    j’ai un fichier excel de type worksheet.
    je n’arrive pas à l’ouvrir
    Ce fichier je l’ai créer directement dans excel et je ne sais pas comment il a été transforme.
    pouvez vous me dire comment je peux retrouver mon fichier original?
    merci d ‘avance de votre aide

  4. dens on said:

    Bonjour,
    Je souhaiterais avoir en visuelle la totalité de mes onglets une trentaine sur 2 ou 3 lignes au lieux de 1 seul ligne glissante.
    merci pour votre aide.

  5. jf on said:

    Bonjour,
    J’aime la première solution qui est simple mais comment faire pour mettre facilement la liste à jour quand il y a des ajouts/ suppressions/ renommages d’onglets svp ?
    Merci.

  6. xavier H on said:

    Bonjour,
    J’utilise beaucoup la fonction lire.classeur grâce à vous, je peux travailler sur pc et sur ipad.
    Je me demandai si il y avait une possibilité de transférer en même temps le contenu d’une cellule de chaque feuille sur la cellule voisine. exemple en reprenant votre exemple:

    B6: Menu C6 valeur (Menu!A3)
    B7: Hypothése C7 valeur (Hypothése!A3)
    Cordialement
    Xavier

  7. serge on said:

    Bonjour,

    Un Grand Merci Article très intéressant

    serge

  8. AVIS sur les explications. : Simples, pragmatiques. Bien pour tous ceux qui ont des notions sans connaitre véritablement les caractéristiques entre çà et çà. Encore une fois l’auteur répond directement et dans la plus simple explication par comparatifs clairs. Continuez, même si vous ce n’est pas votre passion, vous avez le sens de la réflexion de celui qui cherche à comprendre pour appliquer. Vous lui faites gagner un temps précieux. Bravo !

  9. Munsh on said:

    Merci !

Laisser un commentaire

Votre adresse de messagerie ne sera pas publiée. Les champs obligatoires sont indiqués avec *

*

Les balises HTML ne sont pas autorisés.