Selecting Shapes

[RLang]

Hi,
I'd like to create write a function behind a button that selects all the
jpg images embedded on the worksheet surface. The command I found for this
is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the
command which provides for one shot selection. I have not been successful
selecting one image at a time without the previous selection "de-selecting".

Since there are other buttons and labels on this same worksheet that I don't
want selected, I want to start the Array beyond these indexes. Assumption:
the jpg's will always be a contiguous set of indicies once the first shape
index (type = 13) is found in the shaperange array. I can find the starting
index easy enough looping through the shapes array looking at the type.

Since the total number of shapes can vary, I can't hardcode in the Array
function indicies and I don't know how to place a variable into the arglist
of the Array function rather than literals. Any advice?

 

[Leith Ross]

Hi,
I'd like to create write a function behind a button that selects all the
jpg images embedded on the worksheet surface. The command I found for this
is activesheet.Shapes.Range(Array(n1, n2, ...)).Select. At least this is the
command which provides for one shot selection. I have not been successful
selecting one image at a time without the previous selection "de-selecting".

Since there are other buttons and labels on this same worksheet that I don't
want selected, I want to start the Array beyond these indexes. Assumption:
the jpg's will always be a contiguous set of indicies once the first shape
index (type = 13) is found in the shaperange array. I can find the starting
index easy enough looping through the shapes array looking at the type.

Since the total number of shapes can vary, I can't hardcode in the Array
function indicies and I don't know how to place a variable into the arglist
of the Array function rather than literals. Any advice?

Hello RLang,

Here is a macro that will select all Pictures on the active sheet. It
does so by creating a 1 based Variant array, which is what the Array
function does. The difference here is the Variant array is dynamic.
Add a standard module to your project and copy this code to it.

Macro Code
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Option Base 1

Sub SelectAllPictures()

Dim N As Long
Dim ShpArray() As Variant

For Each Shp In ActiveSheet
If Shp.Type = msoPicture Then
N = N + 1
ReDim Preserve ShpArray(N)
ShpArray(N) = Shp.Name
End If
Next Sht

Shapes(ShpArray).Select

End Sub
'---------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Sincerely,
Leith Ross

 

[RLang]

Hi Leith,
thanks for responding. I'm getting a "Type Mismatch" error on the final
select statement. Everything else works up to that point. Here's my code:

Option Base 1
Public Sub cmdSelectAll_Click()

Dim ImgArray() As Variant, j As Long, Shp

j = 0
For Each Shp In ActiveSheet.Shapes
If Shp.type = 13 Then
j = j + 1
ReDim Preserve ImgArray(j)
ImgArray(j) = Shp.Name
End If
Next Shp
Shapes(ImgArray).Select

End Sub

 

[Leith Ross]

Hi Leith,
thanks for responding. I'm getting a "Type Mismatch" error on the final
select statement. Everything else works up to that point. Here's my code:

Option Base 1
Public Sub cmdSelectAll_Click()

Dim ImgArray() As Variant, j As Long, Shp

j = 0
For Each Shp In ActiveSheet.Shapes
If Shp.type = 13 Then
j = j + 1
ReDim Preserve ImgArray(j)
ImgArray(j) = Shp.Name
End If
Next Shp
Shapes(ImgArray).Select

End Sub

Hello RLang,

Sorry about that. The line should read...

ActiveSheet.Shapes(ShpArray).Select

Sincerely,
Leith Ross

 

[Dave Peterson]

Based on Leith's response:

Option Explicit
Sub SelectAllPictures2()

Dim N As Long
Dim ShpArray() As Variant
Dim Shp As Shape

N = 0
For Each Shp In ActiveSheet.Shapes
If Shp.Type = msoPicture Then
N = N + 1
ReDim Preserve ShpArray(1 To N)
ShpArray(N) = Shp.Name
End If
Next Shp

If N = 0 Then
MsgBox "No pics"
Else
ActiveSheet.Shapes.Range(ShpArray).Select
End If

End Sub

 

[RLang]

Thanks very much Leith, I'm now squared away based on your advice.

 

[RLang]

Thanks as well Dave!

Based on Leith's response:

Option Explicit
Sub SelectAllPictures2()

Dim N As Long
Dim ShpArray() As Variant
Dim Shp As Shape

N = 0
For Each Shp In ActiveSheet.Shapes
If Shp.Type = msoPicture Then
N = N + 1
ReDim Preserve ShpArray(1 To N)
ShpArray(N) = Shp.Name
End If
Next Shp

If N = 0 Then
MsgBox "No pics"
Else
ActiveSheet.Shapes.Range(ShpArray).Select
End If

End Sub