How to break this shape reference code in 2 lines?

Question

This code works fine, but i have my sheet BLOCKED for selections.

Sheets("sheet1").Shapes("test_" & i).Select
Selection.Formula = ""

...and i want to to this:

Sheets("sheet1").Shapes("test_" & i).Formula = ""

But i got the object error to not accept this prop or method.

What am i missing?

Tim Williams · Accepted Answer · 2019-10-14T19:38:32.097

0

Formula is a property of the DrawingObject property:

Sheets("sheet1").Shapes("test_" & i).DrawingObject.Formula = ""

For an activeX textbox (assuming you want to change the LinkedCell property):

Dim tb
Set tb = ActiveSheet.Shapes("test_" & i)
tb.OLEFormat.Object.LinkedCell = ""

edited Oct 14 '19 at 19:38

answered Oct 14 '19 at 19:03

Tim Williams

Still not working. "Cannot Set Formula Property of TextBox Class" – William Cezar Oct 14 '19 at 19:15
What exactly is `"Test_" & i` ? Not a plain shape? – Tim Williams Oct 14 '19 at 19:20
I think is a text box. – William Cezar Oct 14 '19 at 19:21
Well, it IS a text box. – William Cezar Oct 14 '19 at 19:29
Yes. I want to change de LinkedCell but still not working. Same error. Your code not working, neither this: Sheets("sheet1").Shapes("test_" & i).OLEFormat.Object.LinkedCell = "" – William Cezar Oct 14 '19 at 19:47
So what kind of "text box" is it? How was it added to the sheet? – Tim Williams Oct 14 '19 at 19:48
i went to Insert>Form>TextBox. And its in a group of shapes. – William Cezar Oct 14 '19 at 20:22
i dont know what happened but now your DrawingObject answer worked fine. thx – William Cezar Oct 14 '19 at 20:42
I was triying to assume (sometimes) "---" to the formula and this is not a valid parameter – William Cezar Oct 14 '19 at 20:57

1 Answers1