Bonjour à tous,
Moi, je préfère les liens hypertextes :
Option Explicit
Sub CreationOngletMenu()
Dim I As Integer, J As Integer
Dim ShMenu As Worksheet
Dim LigneOnglet As ListRow
Dim AdresseLien As String
On Error GoTo Fin
For I = Sheets.Count To 1 Step -1
If Sheets(I).Name = "Menu" Then
Application.DisplayAlerts = False
Sheets(I).Delete
Application.DisplayAlerts = True
Exit For
End If
Next I
Set ShMenu = Sheets.Add(before:=Sheets(1))
With ShMenu
.Name = "Menu"
.Range(.Cells(10, 1), .Cells(10, 2)) = Array("Onglet", "Lien")
.ListObjects.Add(xlSrcRange, Range("$A$10:$B$10"), , xlYes).Name = "TableDesOnglets"
End With
For I = 1 To Sheets.Count
Select Case Sheets(I).Name
Case "Menu"
Case Else
Set LigneOnglet = ShMenu.ListObjects("TableDesOnglets").ListRows.Add
With LigneOnglet
.Range(1, 1) = Sheets(I).Name
AdresseLien = "'" & .Range(1, 1) & "'!A1"
ShMenu.Hyperlinks.Add Anchor:=LigneOnglet.Range(1, 2), Address:="", SubAddress:=AdresseLien, TextToDisplay:=CStr(I)
End With
Set LigneOnglet = Nothing
End Select
Next I
ShMenu.Activate
GoTo Fin
Fin:
Set ShMenu = Nothing
End Sub