Dynamically create formula for shapes VBA

[The_Steward]

Hey!

Trying get this code to work:

shapes.OLEFormat.Object.Formula = namesheet.Cells(names, 10)

Error Code says it can't set formula property of rectangle.

I am trying to get shapes I created to in another part of macro to dynamically change their textframe when the cell they correspond with is changed.

Any help really appreciated.

 

[Domenic]

Try the following . . .

shapes.DrawingObject.Formula = "=" & namesheet.Cells(names, 10).Address(external:=True)

Note, however, naming your variable shapes can be a bit confusing. First, Excel contains a Shapes collection. Secondly, shapes suggests more than one shape. Therefore, try naming your variable something else. Actually, since you're creating shapes within a loop, you could name your variable something like currentShape as an example.

Hope this helps!

 

[The_Steward]

Try the following . . .

shapes.DrawingObject.Formula = "=" & namesheet.Cells(names, 10).Address(external:=True)

Note, however, naming your variable shapes can be a bit confusing. First, Excel contains a Shapes collection. Secondly, shapes suggests more than one shape. Therefore, try naming your variable something else. Actually, since you're creating shapes within a loop, you could name your variable something like currentShape as an example.

Hope this helps!

Worked perfectly! Thankyou!