MSGBOX a la création ou suppression d'un objet définie.

Option Explicit
Public Nb_Motpass As Integer

'appel a l'ouverture du classeur
Public Sub Compte_Motpass()
    Dim nm As Name
    On Error Resume Next
    Nb_Motpass = 0
    For Each nm In ActiveWorkbook.Names
        If nm.Name Like "Motdepasse*" Then
            Nb_Motpass = Nb_Motpass + 1
        End If
    Next nm
End Sub
'appel par Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'de chaque feuille
Public Sub DeCompte_Motpass()
    Dim nm As Name, CNb_Motpass As Integer
    On Error Resume Next
    
    CNb_Motpass = 0
    For Each nm In ActiveWorkbook.Names
        If nm.Name Like "Motdepasse*" Then
            CNb_Motpass = CNb_Motpass + 1
        End If
    Next nm
    If CNb_Motpass <> Nb_Motpass Then
        MsgBox "Mot_pass Avant: " & Nb_Motpass & vbNewLine & "Motpass_Maintenant: " & CNb_Motpass, vbInformation, "CHANGEMENT NOM FORMULE"
        Nb_Motpass = CNb_Motpass
    End If
End Sub