Working with AutoShapes - Adding Text Vs Adding Formulas

[JeffTO]

Hi All

I have a question regarding AutoShapes

I know that you can either Add Text or a Formula to an Autoshape

Within Excel you can select the Autoshape and type the text you want
directly in to the AutoShape
You can also enter a formula in to the Formula bar "=A1" for example -
which will update the text in the AutoShape when the value in A1 is
changed. Once you have a formula in the AutoShape you cant add text
in to the shape unless you first delete the Formula

Now with that understanding I want to work with the AutoShape through
VBA - here is what I have come up with:

I can easily add and manipulate text using the following code - if
there is no forumal in the Autoshape - if there is a formula than this
code does nothing

With ActiveSheet.Shapes("Autoshape 2").TextFrame
.Characters.Text = "WOW"
.Characters.Font.Bold = True
.Characters.Font.Underline = True
End With

If I want to be able to add a formula to an AutoShape this is the only
code I have been able to come up with:

ActiveSheet.Shapes("AutoShape 3").Select
ExecuteExcel4Macro "FORMULA(""=R2C1"")"

Works well enough - but I have a couple of questions:

Is that the only way to get a formula into an AutoShape?
Is there no way of getting a formula without first selectnig the shape
- I try not to select things as much as possible - makes things
cleaner and easier
What is ExecuteExcel4Macro and should I even be attempting to use it?
ExecuteExcel4Macro only using R1C1 format - any way to accomplish this
without using R1C1?

If anyone has any thoughts on this or anything to point me in a new or
different direction that would be very helpful

Thanks,

Jeff

 

[JeffTO]

Hi James

Thanks for the very detailed response - that edfinately solved my
questions.

I am working on a few different types of applications that require
automation - mostly report generation type products. One of the
designes likes to use Autoshapes and I was having problems populating
the autoshape with a formula OR text in an easy way.

Your solution is perfect and provides me with great flexibility in
working with these Autoshapes

Thanks,

Jeff