XL2003 Select each shape sequentially, show handles, and make visi

[ker_01]

I have a worksheet where some shapes have been "lost" over time (deleted
columns, etc) so now these shapes have zero width. I want to delete them
because I'll be making multiple copies of this template, and don't want the
extra overhead.

activesheet.shapes.select
will select all the shapes, and I can see the ones I want to delete.

However, I am unable to get the handles (and the screen doesn't scroll to
make sure the selected shape is in the UI) when I use:

Sub ShowShapes()

Dim shp As Shape

For Each shp In ActiveSheet.Shapes
shp.Select
DelMe = InputBox("delete this shape?")
If DelMe = "Y" Then
shp.Delete
End If
Next

I have enough shapes that I'd prefer to semi-automate this, rather than
selecting all, then picking one to delete it, then selecting all again, and
so on.

What is the best way to see the handles on a (single) selected shape so I
know it is one of my zero-width ones, and also scroll to ensure it is
"visible" i.e. in the UI screen?

Thanks!
Keith

 

[Dave Peterson]

Maybe...

Option Explicit
Sub ShowShapes()

Dim shp As Shape
Dim TestCell As Range
Dim resp As Long

For Each shp In ActiveSheet.Shapes
Set TestCell = Nothing
On Error Resume Next
Set TestCell = shp.TopLeftCell
On Error GoTo 0

If TestCell Is Nothing Then
'do nothing
Else
Application.Goto TestCell, Scroll:=True
resp = MsgBox("delete" & vbLf & shp.Name & vbLf & "at: " _
& TestCell.Address(0, 0), Buttons:=vbYesNo)
If resp = vbYes Then
shp.Delete
End If
End If
Next shp

End Sub

 

[Dave Peterson]

You can add
shp.select
if you want (right after the application.goto line.)

 

[Dave Peterson]

pps.

There is a nice tool built into excel.

Tools|Customize|commands tab|drawing category
Look for "select multiple objects" on the right hand side
drag it to your favorite toolbar.

After you do that, you can dismiss that dialog.

Then click on the button and choose the shapes you want to select.
Then click Ok
and hit the delete key.

 

[OssieMac]

Hello Keith,

I was looking into this problem and I now see that Dave has posted a good
answer. However, you might find the following modification to Dave's code
helpful because it will make visible those shapes that have zero width or
height.

Sub ShowShapes()

Dim shp As Shape
Dim TestCell As Range
Dim resp As Long

For Each shp In ActiveSheet.Shapes
With shp
If .Width = 0 Or .Height = 0 Then
.Width = 40
.Height = 40
End If
End With

Set TestCell = Nothing
On Error Resume Next
Set TestCell = shp.TopLeftCell
On Error GoTo 0

If TestCell Is Nothing Then
'do nothing
Else
Application.Goto TestCell, Scroll:=True
resp = MsgBox("delete" & vbLf & shp.Name & vbLf & "at: " _
& TestCell.Address(0, 0), Buttons:=vbYesNo)
If resp = vbYes Then
shp.Delete
End If
End If
Next shp

End Sub