problem with adding button in vba

Question

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 subscript 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).

Answer 1

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

Answer 2

I do know how to code a simple button and had very similar code. I haven't pasted everything, but most important things (i.e. place where it breaks). Anyway I have tried the very much your code

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


'Line below causes problems
Set obj = ws.OLEObjects.Add(ClassType:="Forms.CommandButton.1")
obj.Left = 535
obj.Top = 15
obj.Width = 150
obj.Height = 40
obj.Object.Caption = "Test"

Line commented points to the error. It is the same as it was before. I do have tried the code in a new worksheet and it works perfectly. However when Does anyone know what is wrong with this code? Another thing that I've noticed is that there is change in terms of active sheet between normal run and debugged run. In the first time a spreadsheet is selected that is right to the spreadsheet that I had another button that run the actual code. In the latter case a correct spreadsheet is selected where all the results are.

thx