XL 2016 Vider combobox après sélection

[Mimi]

Bonjour à tous,
Dans mon classeur il y a 7 feuilles, sur chaque feuille il y a une combobox qui me permet de naviguer dans le classeur, jusque là je n'ai pas de problème.
Lorsque je sélectionne un nom dans la combo, je change de feuille la non plus pas de problème.
Le soucis c'est quand je change de feuille la combo ne se vide pas.
D'ou ma question comment vider la combo quand je change de feuille ??
D'avance merci
Mimi

 

[job75]

Bonjour patricktoulon,

allez juste avant de partir au boulot

Il fallait prendre le temps de boire un café car le code ne va pas si l'on supprime une feuille

A+

 

[ChTi160]

Bonjour Mimi
Bonjour le Fil , le Forum
Une adaptation possible et Perfectible du Code de Patrick (à voir Lol)

Option Explicit
Option Base 1
Public WithEvents ComB As MSForms.ComboBox
    Dim i&, x&
    Dim Ws As Object
    Dim Tbl()
Private Sub Workbook_Open()
With ThisWorkbook
    For Each Ws In .Worksheets
       With Ws
        Tbl = listeSheet(Ws)
           .ChoixOnglet.List = Tbl
           .ChoixOnglet.ListIndex = 0
       End With
    Next Ws
End With
Erase Tbl
Set Ws = Nothing
   Worksheets("Accueil").Activate   'On active la feuille
End Sub
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.[A1].Select
Set ComB = ActiveSheet.ChoixOnglet
   ComB.ListIndex = 0
End Sub
Public Sub comb_Change()
If ComB.ListIndex = 0 Then Exit Sub
    With Sheets(ComB.Value)
        .Activate
        ComB.ListIndex = 0
    End With
End Sub

Public Function listeSheet(Ws)   
    x = 1
    Erase Tbl
    ReDim Preserve Tbl(1 To x)
    Tbl(x) = "<<TOUTES>>"
    For i = 1 To Sheets.Count
        If Sheets(i).Name <> Ws.Name Then
              x = x + 1
        ReDim Preserve Tbl(1 To x)
         Tbl(x) = Sheets(i).Name
        End If
    Next
     listeSheet = Tbl
End Function

Edit : Job75 effectivement
Bonne journée
jean marie

 

[patricktoulon]

re
bonsoir atous
j'avoue ne pas avoir pensé au besoins de suppression
en effet on a une perte de serveur.........
donc la suppression active le sheets et activesheet.... arrive après c'est bizarre mais bon

 

[Mimi]

Merci beaucoup pour vos solutions
Mimi

 

[patricktoulon]

re
bonjours a tous je reviens sur ma proposition
sans module classe
on peut supprimer des sheets

bien sur si on devait aller jusqu'au bout de l'idée de Job75 on devrait ajouter un événement newsheets et lui insérer un combo choixonglet et le remplir
cela dit ( entre parenthèses )la suppression ou l'ajout n'a pas été évoqué dans la demande je ne suis donc pas allé plus loin

Option Explicit
Public WithEvents ComB As MSForms.ComboBox

Public Sub comb_Change()'événement communs
    Sheets(ComB.Value).Activate
End Sub

Private Sub Workbook_Open()
    Dim i%, Liste
    Liste = listeSheet
    For i = 1 To Sheets.Count: Sheets(i).ChoixOnglet.List = Liste: Next
    Workbook_SheetActivate ActiveSheet    'lance la macro
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
    Sh.[A1].Select
    If Sh.Shapes.Count > 0 Then
        With ActiveSheet.ChoixOnglet: .List = listeSheet: .Value = Sh.Name: End With
    End If
End Sub

Public Function listeSheet()
    Dim i&
    ReDim tbl(1 To Sheets.Count)
    For i = 1 To Sheets.Count: tbl(i) = Sheets(i).Name: Next
    listeSheet = tbl
End Function

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
    If Sh.Name <> ActiveSheet.Name And ActiveSheet.Shapes.Count > 0 Then Set ComB = ActiveSheet.ChoixOnglet
End Sub