Menu Onglet

Bonsoir @ tous;
Suite à différentes questions récentes pour naviguer facilement dans un
classeur comportants un nombre important d'onglets je vous propose une
nouvelle soluce avec l'ajout d'un combobox dans le menu standard.

Dans ThisWorkbook:
____________________________________________
Private Sub Workbook_Activate()
ComboOnglets
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
SupComboOnglets
End Sub

Private Sub Workbook_Deactivate()
SupComboOnglets
End Sub

Private Sub Workbook_NewSheet(ByVal Sh As Object)
SupComboOnglets
ComboOnglets
End Sub

Private Sub Workbook_Open()
ComboOnglets
End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyBar As CommandBarComboBox
Set MyBar = Application.CommandBars("standard"). _
Controls("Onglets")
With MyBar
.Clear
For i = 1 To Worksheets.Count
If Sheets(i).Visible = True Then
.AddItem Sheets(i).Name
End If
Next i
.Text = ActiveSheet.Name
End With
End Sub
_______________________________________________________
Dans un module standard:

Private MyNewBar As New ComboBoxSheets

Sub ComboOnglets()
Dim MyBar As CommandBarComboBox
Set MyBar = Application.CommandBars("standard"). _
Controls.Add(msoControlComboBox)
With MyBar
.Caption = "Onglets"
For i = 1 To Worksheets.Count
If Sheets(i).Visible = True Then
.AddItem Sheets(i).Name
End If
Next i
.DropDownLines = 50
.DropDownWidth = -1
.ListHeaderCount = 0
.Text = ActiveSheet.Name
.Width = 100
End With
MyNewBar.SynchroBox MyBar
MyBar.Visible = True
End Sub

Sub SupComboOnglets()
On Error Resume Next
Application.CommandBars("standard"). _
Controls("Onglets").Delete
End Sub
_________________________________________________________
Dans un module de classe à nommer ComboBoxSheets:

Private WithEvents ComboBoxSheets As Office.CommandBarComboBox

Private Sub Class_Terminate()
Set ComboBoxSheets = Nothing
End Sub

Private Sub ComboBoxSheets_Change(ByVal Ctrl As Office.CommandBarComboBox)
Dim Onglet As String
Onglet = Ctrl.Text
Sheets(Onglet).Select
End Sub

Sub SynchroBox(box As CommandBarComboBox)
Set ComboBoxSheets = box
End Sub
____________________________________________________________

Voili si ça peut servir
--
@+
Thierry
--
@+
Thierry