Run-Time Error '1004': Unable to get the Add property of the OLEObjects class

Question

I have a worksheet that upon opening it makes sure that every sheet has a button. So when a new sheet is present that doesn't have a button, it is set to add it. A few months ago I'm pretty sure this worked, but now (after not using this sheet for several months) I'm getting error '1004': "Unable to get the Add property of the OLEObjects class." The error occurs on the "set btn" line. What is causing this and how can I fix it?

    Private btn As OLEObject

Public Const sButtonName1 As String = "btnTorqueCurveFit" 
Public Const sBtnMessage1 As String = "Calculate Constant Torque Constants" 
Public Const sButtonName2 As String = "btnESPCurveFit" 
Public Const sBtnMessage2 As String = "Calculate Constant ESP Constants" 
Public Const sButtonLeft1 As Long = 302.25 
Public Const sButtonLeft2 As Long = 364.25


Private Sub AddTorqueButton(n As Worksheet)
    'Add a Button to the Sheet
    Set btn = n.OLEObjects.Add(ClassType:="Forms.CommandButton.1", Link:=False, DisplayAsIcon:=False, Left:=" & sButtonLeft1 &", Top:=3.75, Width:=60, Height:=57.75)
    btn.Name = sButtonName1
    btn.Object.Caption = sBtnMessage1
    btn.Object.Font.Bold = True
    btn.Object.WordWrap = True

    'Modify the sheet's code to have newly added button call the general code in the module
    Dim codeblock As CodeModule
    Dim vbComp As VBComponent
    Dim lineC As Integer
    Dim Ap As String, _
        Lf As String, _
        Tabs As String, _
        inputStr As String

    Set vbComp = ActiveWorkbook.VBProject.VBComponents(n.CodeName)
    Set codeblock = vbComp.CodeModule

    Tabs = Chr(9)
    Lf = Chr(10)
    Ap = Chr(34)

    inputStr = "Private Sub " & sButtonName1 & "_Click()" & Lf & Tabs & _
                    "ConstTorqueButtonAction ActiveSheet" & Lf & _
                "End Sub"

    With codeblock
        lineC = .CountOfLines + 1
        .InsertLines lineC, inputStr
    End With
End Sub

Macro settings = 'Enable All,' Active X settings = 'Enable All,' the document is networked, but network documents are set to be trusted. It seem to be an issue with this workbook specifically as I have another workbook that use the same "set btn" style of code and it work on this machine, but the code displayed above produces an error. Any help or insight is appreciated.

Have you checked that `n` is a Worksheet when it breaks, perhaps the calling subroutine is passing `n` as `Nothing` or `Empty`? — Ross McConeghy, Aug 13 '14 at 16:53
Yes, it seems to not be passing null and is actually a sheet. https://www.dropbox.com/s/6ryo1oa6f4m16h9/IsItAWorksheet.bmp — neogeek23, Aug 13 '14 at 17:09
What does this mean/do? `Left:=" & sButtonLeft1 &"`. If you use a number (just as a test), it will work fine. — djikay, Aug 13 '14 at 17:20
These Consts are defined elsewhere. When I look for the definition of sButtonLeft1 though it fails to find it. This may be the problem somehow, ctrl+f finds it and it should be accessible, no? Public Const sButtonName1 As String = "btnTorqueCurveFit" Public Const sBtnMessage1 As String = "Calculate Constant Torque Constants" Public Const sButtonName2 As String = "btnESPCurveFit" Public Const sBtnMessage2 As String = "Calculate Constant ESP Constants" Public Const sButtonLeft1 As Long = 302.25 Public Const sButtonLeft2 As Long = 364.25 — neogeek23, Aug 13 '14 at 17:32
It does seem odd that you're passing a formula-like string to the numeric `Left` parameter though the method does accept a variant for it. — Ross McConeghy, Aug 13 '14 at 17:33
In any case, that's your error. To help further, we'd need to see your code. It sounds like an access issue. For example, does the module where the button is defined have `Option Private Module` on top? If it's in an add-in, does your code have it as a reference? Hard to say what the exact issue is. — djikay, Aug 13 '14 at 17:39
Just saw your latest edit. Obvious question: Why do `Left:=" & sButtonLeft1 &"` when `sButtonLeft1` is a `Long`? Just do: `Left:=sButtonLeft1`? — djikay, Aug 13 '14 at 17:42
I changed the [Left:=" & sButtonLeft1 &"] to [Left:=302.25] and it added the button. I would like to have that value be a constant as there are things that line up with it and if I want to change how things are arranged it is only a matter of changing one value rather than several instances. I'm going to see if I can get the defined value to work, but worst comes to worst I can just hardcode this value. If you wouldn't mind post this as an answer so I can marked it as answered. Thanks. — neogeek23, Aug 13 '14 at 17:46
I've tested it here and I can definitely use `sButtonLeft1` as-is. There's no need for the `&`s, you shouldn't be passing a string anyway. — djikay, Aug 13 '14 at 17:50
Actually nevermind, I think at one time I thought the parameters were passed as a long string but that isn't what is going on. Removing the "& pieces works. — neogeek23, Aug 13 '14 at 17:52

score 1 · Accepted Answer · answered Aug 13 '14 at 17:55

The reason for the error is the way that the Left argument is specified when calling OLEObjects.Add. There is no need to be passing it as a string and the & characters around it are unnecessary. In fact, that whole thing causes the error.

Since the constant sButtonLeft1 is already of type Long, you should just be passing it directly. Therefore, instead of passing a string like this:

... , Left:=" & sButtonLeft1 &", ...

you should be calling it with the long parameter directly like this:

... , Left:=sButtonLeft1, ...

This should resolve the problem.

Run-Time Error '1004': Unable to get the Add property of the OLEObjects class

1 Answers1