Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Select each shape sequentially, show handles, and make visi
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Select each shape sequentially, show handles, and make visi
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 ker_01 wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Select each shape sequentially, show handles, and make visi
You can add
shp.select if you want (right after the application.goto line.) Dave Peterson wrote: 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 ker_01 wrote: 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 -- Dave Peterson |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Select each shape sequentially, show handles, and make visi
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. Dave Peterson wrote: 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 ker_01 wrote: 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 -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
XL2003 Select each shape sequentially, show handles, and make visi
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 -- Regards, OssieMac |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Show shape on cell select, Hide shape when cell is deselected | Excel Programming | |||
XL2003: Shape-borders on scatter-series' look wavy | Charts and Charting in Excel | |||
xl2000 vs. xl2003 RangeFromPoint and Grouped Shape | Excel Programming | |||
select a chart without the selector handles showing? | Charts and Charting in Excel | |||
Select a chart without the handles appearing? | Excel Programming |