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

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

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" <[email protected]> wrote in message
news:[email protected]...
> 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

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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

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

"Dave Peterson" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
>> news:[email protected]...
>> > 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