CODEWITHSUNDEEP
vbaexcel
S31
S31

Reputation: 934

Generating button to call macro, but not running macro

Working with someone else's code here, and the previous code generates a worksheet called "Test". The code below is something I found from another post, and have adapted it. The aim is to create a button that is pasted on the "Test" sheet and calls on a macro "Mail" once the "Test" sheet is generated.

The issue is the current code does generate a button in the correct position, but it doesn't do anything/doesn't run the Mail() sub once the button is clicked. 

Dim Obj As Object
Dim cmod
Dim Code As String

With ThisWorkbook.Worksheets("Test")

Set Obj = .OLEObjects.Add(ClassType:="Forms.CommandButton.1", _
                               Link:=False, DisplayAsIcon:=False, Left:=435, _
                               Top:=106.5, Width:=89.25, Height:=38.25)

        Obj.Name = "ButtonTest" 
        Obj.Object.Caption = "Email Workbook"
        Obj.Object.OnAction = "ButtonTest_Click" 

        Code = "Sub ButtonTest_Click()" & vbCrLf & _
                "Call Mail" & vbCrLf & _
                "End Sub"

        With .Parent.VBProject.VBComponents(.CodeName).CodeModule
            .insertlines .CountOfLines + 1, Code
        End With
    End With

Upvotes: 0

Views: 370

Answers (1)

David Zemens
David Zemens

Reputation: 53663

I can't get your code to work at all in Excel 2016, fails with some un-debuggable errors when attempting to add the button. Try this similar code, instead:

Sub foo()
Dim Obj As Object
With ThisWorkbook.Worksheets("Test")
        Set Obj = .Buttons.Add(Left:=435, Top:=106.5, Width:=89.25, Height:=38.25)
        Obj.Name = "ButtonTest"
        Obj.Caption = "Email Workbook"
        Obj.OnAction = "Email_Template.Mail"
    End With
End Sub

Above assumes Email_Template is a code module within the same workbook as ThisWorkbook.

Upvotes: 1

Related Questions