<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui" onLoad="RibbonRun">

' (компонент: ribbon, атрибут: onload), 2010+
Private Sub RibbonRun(ribbon As IRibbonUI)
Application.OnKey "^{F9}", "'" & ThisWorkbook.Name & "'!MakeHeader"
    'Объявите глобальную переменную объекта ленты: Public gobjRibbon As IRibbonUI
    'Set gobjRibbon = ribbon
End Sub

Sub Check_NewSheet(ByVal sh As Object)
    Dim sh_CodeName As String, sh_Name As String, i As Integer
On Error Resume Next
    sh_CodeName = ActiveWorkbook.VBProject.VBComponents(sh.CodeName).Properties("_Codename")
If Err.number > 0 Then
i = 0
Do While sh_CodeName = ""
    sh_CodeName = ActiveWorkbook.VBProject.VBComponents(sh.CodeName).Properties("_Codename")
DoEvents
i = i + 1
Loop
End If
sh_Name = sh.Name
    MsgBox "Code Name - " & sh_CodeName & vbCrLf & "Name - " & sh_Name & vbCrLf & _
    "Attempts - " & i, vbOKOnly, "MESSAGE FROM WorkBook.NewSheet"
End Sub

Sub TestAddIt()
Dim sh As Worksheet
    Set sh = ThisWorkbook.Worksheets.Add
    With Application.VBE.MainWindow
        .WindowState = 1
        .Visible = False
    End With
    MsgBox sh.Name & " -VBE- " & sh.CodeName
End Sub

Private Sub Workbook_NewSheet(ByVal sh As Object)
Stop
'    M2.NewSheet sh

  Dim i As Long
  i = 0
  Debug.Print "Проверка"
  Do While sh.CodeName = ""
    i = i + 1
    Debug.Print "Повторение " & i
    DoEvents
  Loop
  Debug.Print "Codename=" & sh.CodeName & vbCrLf & " Число Повторений " & i

End Sub

Private Sub Workbook_NewSheet(ByVal sh As Object)
'Stop                                                      ' для включения пошагового выполнения.
    M2.NewSheet sh
End Sub

Option Explicit
Sub NewSheet(ByVal sh As Object)
Dim sh_CodeName As String, sh_Name As String, i As Integer
 i = 0
ret:
sh_CodeName = sh.CodeName: sh_Name = sh.Name
Do While sh_CodeName = "" And i < 10
 Application.Wait (Now + TimeValue("0:00:01"))             ' даже задержку на 10 секунд поставил. не помогла
 i = i + 1
GoTo ret
Loop
MsgBox "Code Name - " & sh_CodeName & vbCrLf & "Name - " & sh_Name, vbOKOnly, "СООБЩЕНИЕ ОТ WorkBook.NewSheet"
End Sub