Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel - Golf - how to display "-2" as "2 Under" or "4"as "+4" or "4 Over" in a calculation cell | Excel Discussion (Misc queries) | |||
copy data from one sheet to another "entry has to be same shape" | Excel Discussion (Misc queries) | |||
Excel 2007 Bug - "Locked" Property is Broken for Shape Objects | Excel Programming | |||
Excel 2007 Bug - "Locked" Property is Broken for Shape Objects | Excel Programming | |||
How to prevent "Changed ... Save?" Message after disabling Shape? | Excel Programming |