Programming "Shape" objects

[Robert Crandal]

My spreadsheet contains multiple "shape" objects. I would like to be able
to
change the properties of my various shapes when certain events happen, but
the problem is I don't know the names of my shapes. How are shape objects
referenced in Visual Basic code??

Do I use the following reference format :

Worksheets(1).Shapes(1)..... etc. etc....

If I have 10 shapes on my spreadsheet does, how does Visual Basic determine
which shape gets assigned which numer or name??

Thank you!

Robert

 

[Robert Crandal]

Well, I seem to have discovered that I can reference and change any property
of any shape object using the Worksheets(1).Shapes(?)...etc name reference.
So I guess u can ignore my previous question.

However, now I have another question:

Shape objects can contain text in the middle. I would like my shape object
to
always display or mirror the contents of cell "A1". So any time the
contents
of cell A1 changes, I would like my shape object to notice this event and
copy
the contents of A1 to my shape object's text property.

How can this be done??

Thank you!

 

[Peter T]

Select the object and its name should normally appear in the Names box left
of the input bar. You could also select the object and in the VBE's
immediate window, Ctrl-g, type
?selection.name
and hit enter.

VB doesn't determine which names get assigned (unless of course VB is
changing the name). Default names are given as "ObjectType ID" where the
ID/counter increments by one every time a new object is placed or inserted
on the sheet.

Worksheets("Sheet1").Shapes("Rectangle 1")...
or
Worksheets(1).Shapes(1).....

You can also use the index number, which represents the "order" of the shape
on the sheet. By default the newest added shape is "on top" and will have
the highest index. Note though the order, and index, can be changed both
manually and programmatically.

Regards,
Peter T

 

[Patrick Molloy]

Sub shownames()
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
Debug.Print sh.Name
Next
Set sh = ActiveSheet.Shapes("Oval 6")
With sh
.AutoShapeType = msoShape32pointStar
End With
End Sub

in my demo, I dropped several shapes onto a sheet, the first loop gave me
the default names, one of which I typed into the SET Sh statement, and then I
chnaged thetype of shape from an oval to a start

 

[Patrick Molloy]

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


With many of these types of question, switching on the macro recorder and
seeing what Excel itself does can be enlightening.

 

[Robert Crandal]

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]

there's no direct link between the shape and teh cell ... unlike with
formula, so while cells may do 'dirty' and recalc, your shape won't. What
you could do is use the sheet's calculate event, or even the change event, to
run the code ...

Private Sub Worksheet_Change(ByVal Target As Range)
RefreshShapes
Target.Select
End Sub

Sub RefreshShapes()
Shapes("Oval 1").Select
Selection.Characters.Text = Target.Value
End If

 

[Rick Rothstein]

Try this Change event code instead of your current one...

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Target.Dependents.Address = "$A$1" Then
Shapes("Oval 4").TextFrame.Characters.Text = Range("A1").Value
End If
End Sub

 

[Andy Pope]

Hi,

To create a dynamic link try,

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

Cheers
Andy

 

[Dave Peterson]

Another option for that dynamic link:

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

 

[Andy Pope]

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

Cheers
Andy

 

[Dave Peterson]

But looking through the object browser is painful for my old eyes!

 

[Rick Rothstein]

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.

 

[Dave Peterson]

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).

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.

 

[Patrick Molloy]

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