Excel VBA - programmatically setting a textbox to a linked cell value fails RRS feed

  • Question

  • In Excel you can insert a TextBox shapes object and link it to a cell's value by selecting the shape, then selecting the formula bar and clicking the cell whose value you'd like to have shown in the TextBox.

    When I record a macro doing this, I get the following.

    Sub RecordedMacro()
    ' RecordedMacro Macro
        ActiveSheet.ChartObjects("Chart 1").Activate
        ActiveChart.Shapes.AddTextbox(msoTextOrientationHorizontal, 0, 12.3, 101.55, _
        Application.CutCopyMode = False
        Selection.Formula = "=Sheet1!R7C5"
    End Sub

    If I simply re-run this macro manually, the macro breaks at the Selection.Formula step with the error Run-time error '1004': Unable to set the Formula property of the TextBox class

    How can I accomplish this task (assign cell value to TextBox) programmatically that Excel allows you to do from the UI?

    Thursday, June 18, 2020 5:48 PM

All replies

  • It is necessary to use the Reference style to which the worksheet is currently set. ie xlR1C1 or  xlA1

    Therefore if the Reference Style is currently set to xlA1 style then the following code.

    Selection.Formula = "=Sheet1!$E$7"

    If the Reference Style is currently set to xlR1C1 style then the following code.

    Selection.Formula = "=Sheet1!R7C5"


        RefStyle = Application.ReferenceStyle      'Save the User's current ReferenceStyle

        Application.ReferenceStyle = xlR1C1        'Set ReferenceStyle to xlR1C1
        Selection.Formula = "=Sheet1!R7C5"       'Assign the Formula in xlR1C1 style to the TextBox
        Application.ReferenceStyle = xlA1            'Reset ReferenceStyle back to the User's initial Style

    Note that the Reference in the TextBox will automatically re-adopt the Reference Style that is reset for the application.

    Regards, OssieMac

    Friday, June 19, 2020 7:06 AM