Nom d'onglet

Private Sub Worksheet_Activate()
ActiveSheet.Name = ActiveSheet.Range("A1").Value
End Sub

Option Explicit

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    NomFeuileValide ActiveSheet.Range("A1").Value
End Sub

Private Sub NomFeuileValide(ByVal Nom As String)
Dim i As Integer

    Select Case Len(Nom)
        Case 0: Exit Sub
        Case Is > 31: Nom = Left(Nom, 31)
    End Select

    For i = 1 To Len(Nom)
      Select Case Mid(Nom, i, 1)
          Case ":", "/", "\", "?", "*", "[", "]"
                Mid(Nom, i, 1) = " "
      End Select
    Next

    ActiveSheet.Name = Trim(Nom)
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then Exit Sub
ActiveSheet.Name = ActiveSheet.Range("A1").Value
End Sub