Option Explicit
Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim b As OLEObject, code As String
Set b = Sh.OLEObjects.Add("Forms.CommandButton.1")
With b
.Left = 4
.Top = 4
.Width = 100
.Height = 30
End With
code = "Sub CommandButton1_Click()" & vbCrLf
code = code & " MsgBox ""Coucou...""" & vbCrLf
code = code & "End Sub"
With ThisWorkbook.VBProject.VBComponents(Sh.Name).CodeModule
.InsertLines .CountOfLines + 1, code
End With
End Sub