how to set same assign commandbutton in each worksheet

Question

I want to make commandButon every time I insert newsheet with the same name(TestButton). In the hope that if CommandButton click will call the procedure Tester. This applies to the CommandButton in all sheet. My code is as following:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Dim Obj As Object
Dim Code As String
Dim LF As String 'Line feed or carriage return

LF = Chr(13)


Set Obj = ActiveSheet.OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
            Link:=False, DisplayAsIcon:=False, Left:=880, Top:=20, Width:=100, Height:=50)
Obj.Name = "TestButton"
'buttonn text
 ActiveSheet.OLEObjects(1).Object.Caption = "Send"

 'macro text
  Code = "Sub TestButton_Click()" & LF
  Code = Code & "Call Tester" & LF
  Code = Code & "End Sub"
 'add macro at the end of the sheet module
  With ActiveWorkbook.VBProject.VBComponents(ActiveSheet.Name).CodeModule
               .insertlines .CountOfLines + 1, Code
  End With
End Sub

Sub Tester()
MsgBox "You have click on the test button"
End Sub

but I get an error message "Run-time error 1004 Programmatic access to Visual Basic is not trusted". how to solve it?

Answer 1

You should setup a worksheet how you want it and hide it. Use that worksheet as a template. Whenever you add a worksheet, replace it with a copy of the template.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    Dim WorkSheetName As String

    Dim i As Integer
    With Application
        .ScreenUpdating = False
        .EnableEvents = False
        .DisplayAlerts = fasle

        i = Sh.Index

        Worksheets("HiddenTempalte").Copy After:=Worksheets(i)


        WorkSheetName = Sh.Name

        Sh.Delete

        Worksheets(i).Name = WorkSheetName

        .DisplayAlerts = True
        .EnableEvents = True
        .ScreenUpdating = True
    End With

End Sub

Answer 2

"Trust Access to VBA Project":

How to check from .net code whether "Trust access to the VBA project object model" is enabled or not for an Excel application?

Consider using a Forms button instead:

Private Sub Workbook_NewSheet(ByVal Sh As Object)

    With Sh.Buttons.Add(Left:=880, Top:=20, Width:=100, Height:=50)
        .Caption = "Send"
        .OnAction = "Tester"
    End With

End Sub

Public Sub Tester()
    MsgBox "You have click on the test button"
End Sub