Selecting most of very many programmatically
 

I've been struggling with this for days, and this group is my last hope.

I need to select a large subset of autoshapes from a larger set of the
autoshapes in a worksheet. I tried recording a macro, to see how
the selection process gets coded by Excel. But the business end
of the macro is:

ActiveSheets.Shapes.Range(Array(...)).select

where ... is a list of variants containing the name-property values of the
autoshapes, separated by commas.

I can fill an array of variants with the name-property values of interest.
But for the life of me I can't get the array of variants into the argument
field of the Array function.

Is there some simple way of selecting many shapes from a larger set of
shapes and doing so programmatically? I just don't have much hair
left that I can pull out any more.

Tony Rizzo

Selecting most of very many programmatically
 

Sub SelectSubset()
Dim v() As Variant
v = Array("Rectangle 2", "Oval 5")
ActiveSheet.Shapes.Range(v).Select
End Sub

worked for me. The declaration

Dim v() as Variant

was important.

this also worked:

Sub SelectSubset()
Dim v() As Variant
ReDim v(0 To 1)
v(0) = "Rectangle 2"
v(1) = "Oval 5"
ActiveSheet.Shapes.Range(v).Select
End Sub

--
Regards,
Tom Ogilvy


"Tony Rizzo" wrote in message
...
I've been struggling with this for days, and this group is my last hope.

I need to select a large subset of autoshapes from a larger set of the
autoshapes in a worksheet. I tried recording a macro, to see how
the selection process gets coded by Excel. But the business end
of the macro is:

ActiveSheets.Shapes.Range(Array(...)).select

where ... is a list of variants containing the name-property values of the
autoshapes, separated by commas.

I can fill an array of variants with the name-property values of interest.
But for the life of me I can't get the array of variants into the argument
field of the Array function.

Is there some simple way of selecting many shapes from a larger set of
shapes and doing so programmatically? I just don't have much hair
left that I can pull out any more.

Tony Rizzo

Selecting most of very many programmatically
 

what you can also do is expanding
a selection by setting the replace argument to false..

Sub SubSet()
Dim shp As Shape
Dim sel As DrawingObjects

Application.ScreenUpdating = False
ActiveCell.Select
For Each shp In ActiveSheet.Shapes
If shp.AutoShapeType = msoShapeOval Then
shp.Select False
End If
Next
Set sel = Selection
Application.ScreenUpdating = True

End Sub



--
keepITcool
| www.XLsupport.com | keepITcool chello nl | amsterdam


Tom Ogilvy wrote :

Sub SelectSubset()
Dim v() As Variant
v = Array("Rectangle 2", "Oval 5")
ActiveSheet.Shapes.Range(v).Select
End Sub

worked for me. The declaration

Dim v() as Variant

was important.

this also worked:

Sub SelectSubset()
Dim v() As Variant
ReDim v(0 To 1)
v(0) = "Rectangle 2"
v(1) = "Oval 5"
ActiveSheet.Shapes.Range(v).Select
End Sub