problem with adding button in vba

I try to create a button on my spreadsheet with the following code

Dim btnSaver As Object

Set btnSaver = Workbooks(1).Worksheets("Results").OLEObjects.Add(ClassType:="Forms.CommandButton.1")

I also tried

Set btnSaver = Workbooks(1).Worksheets("Results").OLEObjects.Add("Forms.CommandButton.1")

I first get message can't enter break mode at this time and I press continue and get error application defined or object defined error - when I run it from excel (when I run it once again with debugger it says subscri开发者_如何学Cpt out of range).

I've checked the code in another spreadsheet and it works (it says can't change vb project programmatically or sth like this but button is created nonetheless).

It only happens when you are stepping through the code in the editor, that you will receive the "Can't Enter Break Mode at this time" prompt. Its not an error, its just telling you that you can't manually stop the code on that line.

The other errors are from having something wrong in your syntax. You only have a few lines of code, so I'm not sure whats wrong. You might try something like this:

Sub Test()

    Dim obj As OLEObject
    Dim wk As Workbook
    Dim ws As Worksheet

    Set wk = Application.ActiveWorkbook
    Set ws = wk.ActiveSheet

    'if manually stepping through this line, it will show
    '"Can't Enter Break Mode at this Time" | Click Continue
    Set obj = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1")

    obj.Left = 48
    obj.Top = 24
    obj.Width = 72
    obj.Name = "btn1"
    obj.Object.Caption = "Test Button"

End Sub