Autres liste déroulante et onglets Excel 2021

[PIERRE02]

Bonjour à tous
j'avais besoin d'une liste déroulante me permettant d'avoir accès aux onglets de mon classeur
j'ai trouvé sur le web un module qui permet de le faire mais je souhaiterai ne pouvoir sélectionner les onglets qu'à partir d'un onglet de référence (par ex menu)
QQ à t il la solution ?
voici le module en question
merci de votre aide

Private Sub ComboBox1_Change()
'Updateby Extendoffice
If ComboBox1.ListIndex > -1 Then Sheets(ComboBox1.Text).Select
End Sub
Private Sub ComboBox1_DropButtonClick()
Dim xSheet As Worksheet
On Error Resume Next
Application.ScreenUpdating = False
Application.EnableEvents = False
If ComboBox1.ListCount <> ThisWorkbook.Sheets.Count Then
ComboBox1.Clear
For Each xSheet In ThisWorkbook.Sheets
ComboBox1.AddItem xSheet.Name
Next xSheet
End If
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
Private Sub ComboBox1_GotFocus()
If ComboBox1.ListCount <> 0 Then ComboBox1.DropDown
End Sub

 

[vgendron]

Bonjour
les onglets que tu ne souhaites PAS lister sont connus?
dans ce cas, il suffit de les exclure au moment de la boucle

for each xSheet in Thisworkbook.sheets
if xSheet.name<> "Menu" and wSheet.name<>"Nom2toIgnore" then
combobox1.additem xsheet.name
end if
next xSheet

 

[sylvanu]

Bonsoir Pierre,
Un essai en PJ avec deux macros événementielles.
Quand on clique sur B3 on construit la liste déroulante avec le nom des feuilles présentes :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Chaine$, F
    If Not Application.Intersect(Target, [B3]) Is Nothing Then
        For Each F In Worksheets
            If F.Name <> "Menu" Then Chaine = Chaine & "," & F.Name
        Next F
        Chaine = Mid(Chaine, 2)
        With Range("B3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Chaine
        End With
    End If
End Sub

Quand on a choisit sa feuille alors on va dessus :

Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fin: If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [B3]) Is Nothing Then
         If Target = "" Then Exit Sub
         [B4].Select: Feuille = [B3]: Sheets(Feuille).Select
    End If
Fin:
End Sub

A adapter à votre besoin.

 

[PIERRE02]

c'est exactement ce que je voulais
merci à vous deux
excellente fin de journée

 

[PIERRE02]

Bonsoir Pierre,
Un essai en PJ avec deux macros événementielles.
Quand on clique sur B3 on construit la liste déroulante avec le nom des feuilles présentes :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Chaine$, F
    If Not Application.Intersect(Target, [B3]) Is Nothing Then
        For Each F In Worksheets
            If F.Name <> "Menu" Then Chaine = Chaine & "," & F.Name
        Next F
        Chaine = Mid(Chaine, 2)
        With Range("B3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Chaine
        End With
    End If
End Sub

Quand on a choisit sa feuille alors on va dessus :

Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Fin: If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, [B3]) Is Nothing Then
         If Target = "" Then Exit Sub
         [B4].Select: Feuille = [B3]: Sheets(Feuille).Select
    End If
Fin:
End Sub

A adapter à votre besoin.

Bonjour
j'ai testé les deux exemples
celui de Sylvavu fonctionne très bien pour les onglets existants mais nécessite une mise à jour de la liste déroulante en cas d'onglet supplémentaire
Celui de Vgendron me parait plus efficace dans la mesure ou il liste tous les onglets situés après "menu" mais j'ai beaucoup de mal a adapter la variable "Nom2toignore" pour cacher les onglets type "onglet1" "onglet2" etc
Désolé de vous solliciter à nouveau

 

[vgendron]

Hello

euh non.. à aucun moment, mon code n'est capable de dire si l'onglet est avant ou après l'onglet menu..
ce qu'il faut
1) ton fichier...
2) la liste des onglets qu'il faut ignorer
==> Si cette liste est fixe:== facile, suffit de modifier ma ligne de code
if ws.name<>"menu" and.. and... and...
==> si la position des onglets est connue (exemple: les onglets à ignorer sont TOUS après l'onglet "Menu"
on va pouvoir jouer avec le codename: Sheets(1) = le premier onglet sheets(2).. le deuxième.... sheets(Sheets.count) = le dernier

 

[sylvanu]

Bonjour,

celui de Sylvavu fonctionne très bien pour les onglets existants mais nécessite une mise à jour de la liste déroulante en cas d'onglet supplémentaire

C'est complétement faux. Justement la macro Worksheet_SelectionChange est là pour ça :

 

[PIERRE02]

Merci de cette précision mais ce code liste également les onglets avant l'onglet "Menu"
je souhaite que les ongles 1 et 2 ne soient pas affichés et lire ceux après "Menu"

 

[sylvanu]

mais nécessite une mise à jour de la liste déroulante en cas d'onglet supplémentaire

C'était donc faux.

je souhaite que les ongles 1 et 2 ne soient pas affichés et lire ceux après "Menu"

Ces deux onglets porteront ces noms ou autre chose ?

 

[PIERRE02]

C'était donc faux.

Ces deux onglets porteront ces noms ou autre chose ?

Il y en aura plusieurs avec des noms différents

 

[sylvanu]

Il faut reperer où se trouve la feuille Menu puis ne mémoriser que les feuilles suivantes.
un essai en PJ avec la modif suivante :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Chaine$, Position%, F
    If Not Application.Intersect(Target, [B3]) Is Nothing Then
        Position = 1    ' Repère l'index de la feuille Menu
        For Each F In Worksheets
            Position = Position + 1
            If F.Name = "Menu" Then Exit For
        Next F
        Position = Position - 1
        For Each F In Worksheets    ' Ne liste que les feuilles suivantes à Menu
            If F.Index > Position Then Chaine = Chaine & "," & F.Name
        Next F
        Chaine = Mid(Chaine, 2)
        With Range("B3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Chaine
        End With
    End If
End Sub

 

[PIERRE02]

Il faut reperer où se trouve la feuille Menu puis ne mémoriser que les feuilles suivantes.
un essai en PJ avec la modif suivante :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim Chaine$, Position%, F
    If Not Application.Intersect(Target, [B3]) Is Nothing Then
        Position = 1    ' Repère l'index de la feuille Menu
        For Each F In Worksheets
            Position = Position + 1
            If F.Name = "Menu" Then Exit For
        Next F
        Position = Position - 1
        For Each F In Worksheets    ' Ne liste que les feuilles suivantes à Menu
            If F.Index > Position Then Chaine = Chaine & "," & F.Name
        Next F
        Chaine = Mid(Chaine, 2)
        With Range("B3").Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:=Chaine
        End With
    End If
End Sub

C'est exactement ça
merci beaucoup