Is it Possible to Find Shape at a Location (#find) for XL97?

[JK]

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]

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]

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]

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]

Thank you, Dave. myShape.Type = msoPicture worked like a charm.
Jim Kobzeff