Generating button to call macro, but not running macro

Question

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

basically you are missing the `Obj.OnAction` which assigns the code to the button click event: `Obj.OnAction = "ButtonTest_Click"` — Scott Craner, Mar 20 '18 at 13:13
Thanks for the follow-up @ScottCraner - I added that in under Obj.Object.Caption but still no luck — S31, Mar 20 '18 at 13:26
`Obj.Object.OnAction = "Mail()"` (or possibly `="Mail"`). You don't need to assign an event procedure (`ButtonTest_Click()`) to the `_Click` event of the object, so I think you can get rid of all of the code after `Code = ...`. — David Zemens, Mar 20 '18 at 13:36
Put the `Obj.Object.OnAction = "ButtonTest_Click"` after the code insert, so the code exists before trying to link to it. — Scott Craner, Mar 20 '18 at 13:41
Shouldn't be necessary if the `OnAction` is calling the `Mail` procedure directly @ScottCraner ? — David Zemens, Mar 20 '18 at 13:41
I agree @DavidZemens just wanted to give options for future viewers that may not be pointing at existng code. — Scott Craner, Mar 20 '18 at 13:43
@s31, where is "Mail" located? If not in a module then you may need to proceed it with the sheet location: https://stackoverflow.com/questions/14572989/vba-have-a-sub-run-another-sub — Scott Craner, Mar 20 '18 at 13:45
@ScottCraner - adjusted it to be under the code insert, but still no go. Additionally, the sub Mail() is found in another module called Email_Template, instead of a sheet location could I do use the module location - `Email_Template.Mail`? — S31, Mar 20 '18 at 13:47
@ScottCraner, by that I just mean when I click on the button created - nothing happens after. The Mail() sub itself creates an email from Outlook that should pop-up, but since nothing happens that pop-up never shows up. — S31, Mar 20 '18 at 13:49
(I can't get this code to work at all -- fails on the `.OleObjects.Add` method & won't let me debug) — David Zemens, Mar 20 '18 at 13:51
in fact, I get it to work without the `Obj.Object.OnAction = "ButtonTest_Click"` just fine. put your code in a new workbook and try it. maybe there is a problem with the workbook. — Scott Craner, Mar 20 '18 at 14:01

score 1 · Accepted Answer · answered Mar 20 '18 at 13:56

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.

Brilliant. Thank you! Still no idea why that one was acting up in such a variety of ways. — S31, Mar 20 '18 at 13:58

Generating button to call macro, but not running macro

1 Answers1