Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it Possible to Find Shape at a Location (#find) for XL97?
I used the following Dave Peterson procedure to find one of ten pictures
pasted on a WS and delete it. (I modified MsgBox myShape.Name to myShape.Delete). Sub testme3() Dim myCell As Range, myShape As Shape Set myCell =Range("A1") For Each myShape In ActiveSheet.Shapes If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If Next myShape End Sub It worked on XP but not XL 97. Is there a similar procedure that will work for XL 97? I simply want to delete a picture pasted to a certain cell to avoid pasting another picture over it. Thank you. Jim Kobzeff |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it Possible to Find Shape at a Location (#find) for XL97?
What happens when you run it in xl97?
If you're running this from a commandbutton (or another control from the control toolbox toolbar), you can change that control's .takefocusonclick to false. If the control that you're using doesn't have that property, then add a line to the top of your code: Activecell.activate (It's a bug that was fixed in xl2k.) If that's not it, ... I think that there was a problem with names of objects in xl97. If I recall correctly, you could have two pictures with the same name. I'd check those pictures to see if any names are duplicated and make them all unique. But I don't remember if that would cause this trouble in xl97. JK wrote: I used the following Dave Peterson procedure to find one of ten pictures pasted on a WS and delete it. (I modified MsgBox myShape.Name to myShape.Delete). Sub testme3() Dim myCell As Range, myShape As Shape Set myCell =Range("A1") For Each myShape In ActiveSheet.Shapes If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If Next myShape End Sub It worked on XP but not XL 97. Is there a similar procedure that will work for XL 97? I simply want to delete a picture pasted to a certain cell to avoid pasting another picture over it. Thank you. Jim Kobzeff -- Dave Peterson |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it Possible to Find Shape at a Location (#find) for XL97?
Thank you Dave. XL 97 doesn't include TopLeftCell just Top. The procedure
highlights For Each myShape In ActiveSheet.Shapes with error. How can I assign unique name to picture when pasting to cell? Thank you in advance. "Dave Peterson" wrote in message ... What happens when you run it in xl97? If you're running this from a commandbutton (or another control from the control toolbox toolbar), you can change that control's .takefocusonclick to false. If the control that you're using doesn't have that property, then add a line to the top of your code: Activecell.activate (It's a bug that was fixed in xl2k.) If that's not it, ... I think that there was a problem with names of objects in xl97. If I recall correctly, you could have two pictures with the same name. I'd check those pictures to see if any names are duplicated and make them all unique. But I don't remember if that would cause this trouble in xl97. JK wrote: I used the following Dave Peterson procedure to find one of ten pictures pasted on a WS and delete it. (I modified MsgBox myShape.Name to myShape.Delete). Sub testme3() Dim myCell As Range, myShape As Shape Set myCell =Range("A1") For Each myShape In ActiveSheet.Shapes If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If Next myShape End Sub It worked on XP but not XL 97. Is there a similar procedure that will work for XL 97? I simply want to delete a picture pasted to a certain cell to avoid pasting another picture over it. Thank you. Jim Kobzeff -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it Possible to Find Shape at a Location (#find) for XL97?
xl97 does have a topleftcell property for some shapes--but not all (same as
every version of excel). Maybe it's better to do check some more properties. Option Explicit Sub testme() Dim myShape As Shape Dim myCell As Range Set myCell = ActiveSheet.Range("a1") For Each myShape In ActiveSheet.Shapes If myShape.Type = msoPicture Then If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If End If Next myShape End Sub Ron de Bruin has a bunch of techniques for dealing with shapes--they're not as straightforward as I would have guessed. http://www.rondebruin.nl/controlsobjectsworksheet.htm ====== And my guess why it didn't work--you have data|autofilter applied. Those dropdown arrows are shapes (and that screws up everything!). JK wrote: Thank you Dave. XL 97 doesn't include TopLeftCell just Top. The procedure highlights For Each myShape In ActiveSheet.Shapes with error. How can I assign unique name to picture when pasting to cell? Thank you in advance. "Dave Peterson" wrote in message ... What happens when you run it in xl97? If you're running this from a commandbutton (or another control from the control toolbox toolbar), you can change that control's .takefocusonclick to false. If the control that you're using doesn't have that property, then add a line to the top of your code: Activecell.activate (It's a bug that was fixed in xl2k.) If that's not it, ... I think that there was a problem with names of objects in xl97. If I recall correctly, you could have two pictures with the same name. I'd check those pictures to see if any names are duplicated and make them all unique. But I don't remember if that would cause this trouble in xl97. JK wrote: I used the following Dave Peterson procedure to find one of ten pictures pasted on a WS and delete it. (I modified MsgBox myShape.Name to myShape.Delete). Sub testme3() Dim myCell As Range, myShape As Shape Set myCell =Range("A1") For Each myShape In ActiveSheet.Shapes If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If Next myShape End Sub It worked on XP but not XL 97. Is there a similar procedure that will work for XL 97? I simply want to delete a picture pasted to a certain cell to avoid pasting another picture over it. Thank you. Jim Kobzeff -- Dave Peterson -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Is it Possible to Find Shape at a Location (#find) for XL97?
Thank you, Dave. myShape.Type = msoPicture worked like a charm.
Jim Kobzeff "Dave Peterson" wrote in message ... xl97 does have a topleftcell property for some shapes--but not all (same as every version of excel). Maybe it's better to do check some more properties. Option Explicit Sub testme() Dim myShape As Shape Dim myCell As Range Set myCell = ActiveSheet.Range("a1") For Each myShape In ActiveSheet.Shapes If myShape.Type = msoPicture Then If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If End If Next myShape End Sub Ron de Bruin has a bunch of techniques for dealing with shapes--they're not as straightforward as I would have guessed. http://www.rondebruin.nl/controlsobjectsworksheet.htm ====== And my guess why it didn't work--you have data|autofilter applied. Those dropdown arrows are shapes (and that screws up everything!). JK wrote: Thank you Dave. XL 97 doesn't include TopLeftCell just Top. The procedure highlights For Each myShape In ActiveSheet.Shapes with error. How can I assign unique name to picture when pasting to cell? Thank you in advance. "Dave Peterson" wrote in message ... What happens when you run it in xl97? If you're running this from a commandbutton (or another control from the control toolbox toolbar), you can change that control's .takefocusonclick to false. If the control that you're using doesn't have that property, then add a line to the top of your code: Activecell.activate (It's a bug that was fixed in xl2k.) If that's not it, ... I think that there was a problem with names of objects in xl97. If I recall correctly, you could have two pictures with the same name. I'd check those pictures to see if any names are duplicated and make them all unique. But I don't remember if that would cause this trouble in xl97. JK wrote: I used the following Dave Peterson procedure to find one of ten pictures pasted on a WS and delete it. (I modified MsgBox myShape.Name to myShape.Delete). Sub testme3() Dim myCell As Range, myShape As Shape Set myCell =Range("A1") For Each myShape In ActiveSheet.Shapes If Intersect(myShape.TopLeftCell, myCell) Is Nothing Then 'do nothing Else MsgBox myShape.Name Exit For End If Next myShape End Sub It worked on XP but not XL 97. Is there a similar procedure that will work for XL 97? I simply want to delete a picture pasted to a certain cell to avoid pasting another picture over it. Thank you. Jim Kobzeff -- Dave Peterson -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Where do I find buttons to change shape to 3-D and rotate in 2007 | Excel Discussion (Misc queries) | |||
How to find out the cell location | Excel Discussion (Misc queries) | |||
find location max value in column | Excel Discussion (Misc queries) | |||
find location | Excel Programming | |||
Find Value in Array and report on Location | Excel Programming |