I too never knew that a shape also hade a formula property. Good one !

"Dave Peterson" wrote:

I used the Shp variable because I couldn't remember where or how deep I had to
go to get to it.

The object browser did help, but it does take a bit of looking (unless you've
got a very good memory).

Rick Rothstein wrote:

I didn't realize that, or even think to look to see if, Shapes had a Formula
property. I learned something new today... thanks. And yes, I agree with
Andy, that is a much cleaner way to go than a macro.

--
Rick (MVP - Excel)

"Dave Peterson" wrote in message
...
But looking through the object browser is painful for my old eyes!

Andy Pope wrote:

Dave, that is certainly a lot cleaner than the macro recorded Excel4 code
:)

Cheers
Andy
--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Dave Peterson" wrote in message
...
Another option for that dynamic link:

Dim Shp As Shape
Set Shp = ActiveSheet.Shapes("Oval 1")
Shp.DrawingObject.Formula = "=A1"


Andy Pope wrote:

Hi,

To create a dynamic link try,

ActiveSheet.Shapes("Oval 1").Select
ExecuteExcel4Macro "FORMULA(""=R1C1"")"

Cheers
Andy

--

Andy Pope, Microsoft MVP - Excel
http://www.andypope.info
"Robert Crandal" wrote in message
...
Excellent! This works fantastic.

BTW, I tried putting a sum() formula in cell A1 instead of a
constant string. My formula is "=sum(A4:A6)". I noticed that if I
change the values of any cells between A4 to A6 that the sum
does not dynamically display on my shape object. Do you know
why this is happening??

Thank u sooo much!


"Patrick Molloy" wrote in
message ...
use the sheet's change event -- right click the sheet tab and
select
view
code

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Range("A1").Address Then
Shapes("Oval 1").Select
Selection.Characters.Text = Target.Value
Target.Select
End If
End Sub



--

Dave Peterson

--

Dave Peterson

--

Dave Peterson
.