Afficher dans une colonne la liste de toutes les feuilles du classeur

[fd.30]

Bonjour à tous les Excelliens,

Je vous re-sollicite encore une fois pour m'aider à trouver la solution à mon problème.

En PJ, j'ai un classeur qui est composé d'une quarantaine de feuilles; et je voudrais avoir sur la feuille "Global" une liste de toutes les feuilles de ce classeur de façon automatique ; dans le but après de créer un menu sur la page d'accueil "Global"

Merci d'avance de votre collaboration

FD

 

[Catrice]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir,

Ci-dessous un exemple à tester :

Sub Test()
With Sheets("   Global    ")
    .Range("A1").CurrentRegion.Offset(1, 0).Resize(, 1).Clear
    For Each X In Sheets
        If X.Name <> "   Global    " Then .Range("A65536").End(xlUp).Offset(1, 0) = X.Name
        'Enlever le If pour afficher le nom de la feuille Global dans la liste.
    Next
End With
End Sub

 

[Papou-net]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonjour fd.30, Catrice,

Une autre version :

Private Sub Worksheet_Activate()
With ActiveWorkbook
  .Sheets("   Global   ").Range("A2:A65536").ClearContents
  For n = 2 To .Sheets.Count
    Cells(n, 1) = .Sheets(n).Name
  Next
End With

End Sub

J'ai placé cette macro dans l'événement Activate de la feuille "Global", ainsi cette feuille se mettra à jour automatiquement en cas d'ajout de nouvelles feuilles, simplement en la réactivant. D'autre part, la boucle commence à 2, ainsi on ignore la feuille "Global".

Espérant avoir aidé.

Cordialement.

 

[Robert]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir fd, bonsoir le fourm,

Deux macros événementielles pour être assurer la mise à jour des ajouts autant que des suppressions d'onglets... Dans le composant ThisWorkbook la macro événementielle NeewSheet qui se déclenche à l'ajout d'un nouvel onglet :

Private Sub Workbook_NewSheet(ByVal Sh As Object) 'à l'ajout d'un nouvel onglet
For x = 1 To Sheets.Count 'boucle sur tous les onglets
    With Sheets("   Global    ") 'prend en compte l'onglet "   Global    "
        .Range("A2:A" & .Range("A65536").End(xlUp).Row).ClearContents 'efface les anciennes données
        .Cells(x + 1, 1).Value = Sheets(x).Name 'ajoute le nom de l'onglet dans la colonne A
    End With 'finde la prise en compte de l'onglet "   Global    "
Next x 'prochain onglet de la boucle
End Sub

et la même, à l'activation de l'onglet " Global " (donc dans le composant Feuil1(" Global ")), pour mettre à jour en cas de suppression d'un onglet :

Private Sub Worksheet_Activate()
Range("A2:A" & Range("A65536").End(xlUp).Row).ClearContents 'efface les anciennes données
For x = 1 To Sheets.Count 'boucle sur tous les onglets
    Cells(x + 1, 1).Value = Sheets(x).Name 'ajoute le nom de l'onglet dans la colonne A
Next x 'prochain onglet de la boucle
End Sub

Édition :

Bonsoir Catrice, Papu-net, on s'est croisé...

 

[Staple1600]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir


Une petite pointe de jadis

EXEMPLE :Avec un classeur contenant 3 feuilles

1) Insertion/Noms/Définir : NFEUIL=LIRE.CLASSEUR(1)
2) Sélectionner A1:A3
3) Dans la barre de formule :=TRANSPOSE(NFEUIL)

Valider avec CTRL+SHIFT+MAJ

Et voilà votre liste des feuilles du classeur
Cool, non ?


EDITION : PS : pour n'avoir que le noms des feuilles
=TRANSPOSE(STXT(NFEUIL;TROUVE("]";NFEUIL)+1;255))

 

[bcharef]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir FD, Catrice, Papou & Robert,
Bonsoir à toutes et à tous.

je pense que ce fichier ira très bien ,il vient de notre ami Thierry.

Cordialement.

BCharef

Edit : Salut ami Staple , pas du tout que vous soyez invisible, juste un problème technique.

 

[Staple1600]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir bcharef


Suis-je invisible ?

Et comment tu récupères le nom des feuilles dans une colonne d'une feuille avec ce code ?

Sub Menu()
Application.CommandBars("Workbook tabs").ShowPopup 500, 200
End Sub

 

[bcharef]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir FD, Catrice, Papou, Robert & Staple,
Bonsoir à toutes et à tous.

Staple

Sincèrement, je trouve la solution que vous aviez proposée par formule et génial.

Comme, il est toujours utile de porter à votre connaissance que le nombre de caractère maximal d'un nom d'une feuille ne dépasse pas 31 caractère.

A vous lire.

Cordialement.

BCharef.

 

[fd.30]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir


Une petite pointe de jadis

EXEMPLE :Avec un classeur contenant 3 feuilles

1) Insertion/Noms/Définir : NFEUIL=LIRE.CLASSEUR(1)
2) Sélectionner A1:A3
3) Dans la barre de formule :=TRANSPOSE(NFEUIL)

Valider avec CTRL+SHIFT+MAJ

Et voilà votre liste des feuilles du classeur
Cool, non ?


EDITION : PS : pour n'avoir que le noms des feuilles
=TRANSPOSE(STXT(NFEUIL;TROUVE("]";NFEUIL)+1;255))

Bonjour à tout le forum,

Mille merci pour vos multiples solutions, néanmoins je n'ai pas réussi à faire la procédure décrite par Staple1600, car peut-être pas assez détaillé pour mon ptit niveau Excel

procédure ci-dessous :

) Insertion/Noms/Définir : NFEUIL=LIRE.CLASSEUR(1)
2) Sélectionner A1:A3
3) Dans la barre de formule :=TRANSPOSE(NFEUIL)

Valider avec CTRL+SHIFT+MAJ

Et voilà votre liste des feuilles du classeur
Cool, non ?


EDITION : PS : pour n'avoir que le noms des feuilles
=TRANSPOSE(STXT(NFEUIL;TROUVE("]";NFEUIL)+1;255))

 

[fd.30]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir FD, Catrice, Papou & Robert,
Bonsoir à toutes et à tous.

je pense que ce fichier ira très bien ,il vient de notre ami Thierry.

Cordialement.

BCharef

Edit : Salut ami Staple , pas du tout que vous soyez invisible, juste un problème technique.

Bonjour bcharef,

Le fichier que tu m'as donné est tout simplement super; mais quand le nombres de feuilles dépasse 16, il apparait une option "plus de feuilles" que je souhaiterais éviter afin de voir toutes ma liste de feuille.
Quand tu cliques sur "plus de feuilles" une fenetre "Activer" s'ouvre pour faire apparaitre avec l'ascenceur toutes les feuilles du tableau. Sais-tu comment faire apparaitre directement cette fenêtre à l'aide d'une macro car ça pourrait me convenir.

Merci à toi

 

[jeanpierre]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonjour fd.30, le fil,

Regarde ce lien, le post #11, j'ai déposé une macro complèmentaire de J.W. qui s'installe dans la barre du haut d'excel. Pas de limitation.

https://www.excel-downloads.com/thr...tant-le-choix-ou-lacces-a-une-feuille.129983/

Bon après-midi.

Jean-Pierre

 

[jeanpierre]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Re,

Sinon sur le même fil, posts #3, #4 et #10, d'autres liens....

 

[Staple1600]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir



message édité pour cause obsolescence programmée.

PS: au besoin les explications données ici suffisent

 

[Brigitte]

Re : Afficher dans une colonne la liste de toutes les feuilles du classeur

Bonsoir,

Sans doute un peu tard... Thierry nous avait donné un bout de code... assez simple. Il suffirait de le rendre "automatique" afin que cela se mette à jour.

Puis d'utiliser une macro que m'avait faite Roger pour en faire des liens hypertexte.

Donc voici les codes à adapter bien sûr :

POUR COMPTER, LISTER les feuilles : par Thierry xld

Sub CompteFeuille()
MsgBox "Ce Classeur contient : " & ThisWorkbook.Worksheets.Count & " Feuilles"
End Sub
 
Sub ListeFeuille()
Dim i As Byte
For i = 1 To ThisWorkbook.Worksheets.Count
Cells(i, 1) = ThisWorkbook.Worksheets(i).Name
Next
End Sub
 
Sub CompteFeuillemoinsune()
'pour ne pas compter la feuille listing
MsgBox "Ce Classeur contient : " & ThisWorkbook.Worksheets.Count - 1 & " Feuilles"
End Sub

POUR LES LIENS HYPERTEXTE : par Roger2327

Pour rendre en liens hypertexte, à l'ouverture par exemple de la feuille en question, les liens :

Sub Liens()
Dim tablo As Variant, i As Integer, data As Collection, derval As String
Dim derlig As Integer, cell As Range, ligne As Integer
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
tablo = Sheets("ARCHIVES - Historique").Range("A1:F" & Sheets("ARCHIVES - Historique").Range("A20000").End(xlUp).Row)
Set data = New Collection
For i = 1 To UBound(tablo)
    On Error Resume Next
        If tablo(i, 1) = "GRH" Then
        data.Add i, tablo(i, 5) & tablo(i, 6)
        End If
Next i
derlig = Sheets("GRH").Range("B10000").End(xlUp).Row
For Each cell In Sheets("GRH").Range("B2:B" & derlig)
ligne = data(cell.Value & cell.Offset(0, 1).Value)
    If cell.Offset(0, -1).Value <> ligne Then
        cell.Offset(0, -1).Value = ligne
        cell.Hyperlinks.Add Anchor:=cell.Offset(0, -1), Address:="", SubAddress:= _
        "'ARCHIVES - Historique'!A" & ligne
    End If
Next cell
    Columns("A:A").Select
    With Selection.Font
        .Name = "Tahoma"
        .Strikethrough = False
        .Superscript = False
        .Subscript = False
        .OutlineFont = False
        .Shadow = False
         Selection.Font.ColorIndex = 5
         Selection.Font.Underline = xlUnderlineStyleSingle
    End With
End Sub

La macro de Roger permet, à l'ouverture de la feuille GRH de mon classeur, de recalculer les liens (car il y a des rajouts, mises à jours...) dès son ouverture.

Bonne soirée.