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

This has been an interesting question and I wouldn't have spent as much
time on it if there wasn't a good chance I'll be needing to do the
same.

Using Google, a thread by Dave Peterson turned up wherein he shows that


Dim wks As Worksheet
Set wks = ActiveSheet

With wks
.Rectangles(1).Formula = "=B1"
End With

works, but my experimentation revealed that this approach works only
for rectangles and circles/ovals (with .Ovals(2).Formula syntax). You
probably have noticed that the Object Browser nor the help files don't
have any information about the Rectangles or Ovals objects (or
collections?) - nor any mention of a .Formula property for the Shape
object.

My first thought was to see if the TextFrame of an AutoShape with a
formula could be 'assigned' to an AutoShape that didn't have one, but
that kept throwing an error.

My next attempt was to interactively create some AutoShapes with
formulas and programmatically create some with the ExecuteExcel4Macro
technique you mentioned and then create a test subroutine:

Dim oAShape1 as Object
Dim oAShape2 as Object

Set oAShape1 = Activesheet.Shapes("Rectangle 1") 'interactive formula
Set oAShape2 = Activesheet.Shapes("Rectangle 2") 'ExecuteExcel4Macro
formula

and then use the VBA IDE's Locals window to examine the oAShape1 and
oAShape2 objects. I eventually found the DrawingObject property was
being used in the ones where the formula had been added
ExecuteExcel4Macro. The DrawingObject class / property doesn't show up
in the Object Browser or the Excel VBA help, either.

The last chunk of experimentation revealed that the following syntax
works on every type of AutoShape I tested it on:

ActiveSheet.Shapes("AutoShape 5").DrawingObject.Formula = "$B$3"

and note that this doesn't require selecting the Shape (which should
speed up your application).

Finally, to my surprise, the above also works in Excel 2007.
Apparently, Shapes got pretty well overhauled for Excel 2007 - if you
import a Excel 2003 shape into Excel 2007 and then look at the .xml
representations, they are really different (at least to my
.xml-untrained eyes!), but somehow the DrawingObject functionality
survived.

BTW, the ExecuteExcel4Macro is an Excel 4 (old, old version) way of
doing macros that predates VBA sheet and code modules. Apparently,
there are some things that can't be done with VBA that still can be done
with Excel4Macro sheets...

Hope this helps. :Bgr

PS - out of curiosity, what sort of application are you working on
where you needed this capability?


--
jamescox
------------------------------------------------------------------------
jamescox's Profile: http://www.thecodecage.com/forumz/member.php?userid=449
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=117120

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