Private Sub Workbook_Open()
Dim a(), i&
ReDim a(1 To Me.Sheets.Count, 1 To 2)
For i = 1 To Me.Sheets.Count
a(i, 1) = Me.Sheets(i).Name
a(i, 2) = Me.Sheets(i).CodeName
Next
Me.Names.Add "Feuilles", a, Visible:=False 'nom défini masqué
Me.Saved = True 'évite l'invite à la fermeture
End Sub
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Dim a, i&, f As Object
a = [Feuilles]
If IsError(a) Then Exit Sub
Application.DisplayAlerts = False
If Me.Sheets.Count <> UBound(a) Then Workbooks.Open Me.FullName
On Error Resume Next
For i = 1 To UBound(a)
Set f = Nothing: Set f = Me.Sheets(a(i, 1))
If f Is Nothing Then Workbooks.Open Me.FullName
If f.CodeName <> a(i, 2) Then Workbooks.Open Me.FullName
Next
End Sub