Programming "Shape" objects
 

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

Programming "Shape" objects (Part 2)
 

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!


"Robert Crandal" wrote in message
...
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

Programming "Shape" objects
 

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



"Robert Crandal" wrote in message
...
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

Programming "Shape" objects
 

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

"Robert Crandal" wrote:

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

.

Programming "Shape" objects (Part 2)
 

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" wrote:

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!


"Robert Crandal" wrote in message
...
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


.

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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




"Robert Crandal" wrote:

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


.

Programming "Shape" objects (Part 2)
 

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

--
Rick (MVP - Excel)


"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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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

Programming "Shape" objects (Part 2)
 

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
.