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