Public derf As String
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If Not FeuilleExiste(derf) Then MsgBox "la feuille " & derf & " a été supprimée"
End Sub
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
derf = Sh.Name
End Sub
Function FeuilleExiste(NomF As String) As Boolean
Dim MaVar
On Error GoTo fin
FeuilleExiste = False
Set MaVar = Sheets(NomF)
FeuilleExiste = True
Exit Function
fin:
End Function
et j'insert je fait application.run("CM") dans le module de création des nouvelles feuilles.Sub CM()
ActiveSheet.Select
'Machines1
k = ActiveSheet.Range("C3").Value
ActiveWorkbook.Names.Add Name:=k, RefersToR1C1:= _
"=OFFSET(R101C8,0,0,COUNTA(R101C8:R120C8)-1)"
'Machines2
k = ActiveSheet.Range("D3").Value
ActiveWorkbook.Names.Add Name:=k, RefersToR1C1:= _
"=OFFSET(R101C9,0,0,COUNTA(R101C9:R120C9)-1)"
'Machines3
k = ActiveSheet.Range("E3").Value
ActiveWorkbook.Names.Add Name:=k, RefersToR1C1:= _
"=OFFSET(R101C10,0,0,COUNTA(R101C10:R120C10)-1)"
'Machines4
k = ActiveSheet.Range("F3").Value
ActiveWorkbook.Names.Add Name:=k, RefersToR1C1:= _
"=OFFSET(R101C11,0,0,COUNTA(R101C8:R120C11)-1)"
End Sub
mais je sais pas comment le déclencherSub ELIM()
ActiveSheet.Select
For i = 3 To 6
k = ActiveSheet.Sheet(3, i).Value
On Error Resume Next
ActiveWorkbook.Names(k).Delete
End Sub