Select Objects by Object Name

[xlnt]

I have a sheet with several different "Shapes" including lines, buttons, etc. I want to select the shapes by type programmatically. My attempts so far included the following:

For Count = 1 To ActiveSheet.Shapes.Count
If ActiveSheet.Shapes(Count).Name Like "Line*" Then
line_array = line_array & Count & ", "
End If
Next Count
line_array = Left(line_array, Len(line_array) - 2)
ActiveSheet.Shapes.Range(Array(line_array)).Select

In the above code I was hoping to concatenate a list of Shapes with the word "Line" in the object name, but the error occurs in the concatenated array in the last line of code, so I was barking up the wrong tree.

Does anyone know how to select objects by type, rather than selecting all the objects on the sheet?

 

[Domenic]

Try something like this...

[font=Verdana][color=darkblue]Option[/color] [color=darkblue]Explicit[/color]

[color=darkblue]Sub[/color] test()

    [color=darkblue]Dim[/color] MyArray() [color=darkblue]As[/color] [color=darkblue]String[/color]
    [color=darkblue]Dim[/color] Shp [color=darkblue]As[/color] Shape
    [color=darkblue]Dim[/color] Cnt [color=darkblue]As[/color] [color=darkblue]Long[/color]
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] Shp [color=darkblue]In[/color] ActiveSheet.Shapes
        [color=darkblue]If[/color] Shp.Name [color=darkblue]Like[/color] "Line*" [color=darkblue]Then[/color]
            Cnt = Cnt + 1
            [color=darkblue]ReDim[/color] [color=darkblue]Preserve[/color] MyArray(1 [color=darkblue]To[/color] Cnt)
            MyArray(Cnt) = Shp.Name
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] Shp
    
    ActiveSheet.Shapes.Range(MyArray).Select

[color=darkblue]End[/color] [color=darkblue]Sub[/color]
[/font]

 

[xlnt]

"The specified parameter has an invalid value."

Hmmmm . . . It loops through all of the code successfully but breaks on the last line. I'm out of my depth when it comes to declaring variables with () after them, and more so when it comes to ReDim Preserve. To me it looks as though you were trying to create an array based on the name of each shape, something like "Line 1", "Line 2", "Line 3", etc. . .

The hang up with this method at least appears to be with the way we are looping through the objects to concatenate the array.

 

[Domenic]

The macro loops through each shape within the active worksheet. If the name of the shape starts with the text 'Line', the name is added to the array. Then, the array of names is used for the selection. I've tested it and it seems to work fine.

 

[xlnt]

What version of Excel are you running? I've tested your code on a computer running Excel 2003 Student and another computer running 2003 Pro. On both computers, I received the 1004 runtime error "The specified parameter has an invalid value." It always breaks on the last line of code. The activesheet has six lines and one button shape - I assigned the macro to the button shape and ran it by clicking there. I also tried running the code straight from the VBE module1. I'm at a loss.

 

[xlnt]

I changed the data type on MyArray from String to Variant and the code works now. Interesting.

 

[Domenic]

I'm using Excel 2010. However, the code should work regardless of version. If you change the data type for MyArray back to String do you still get the original error?

 

[xlnt]

It goes back to the same error. When I put it back to Variant, it runs again.

 

[Domenic]

That's interesting... As far as I know, the Name property of the Shape object returns a string value. As such, the array variable can be declared as a String variable. Of course, an error will occur if the desired shapes do not exist. So you may want to prevent the error by replacing...

[font=Verdana]     ActiveSheet.Shapes.Range(MyArray).Select[/font]

with

[font=Verdana]    [color=darkblue]If[/color] Cnt > 0 [color=darkblue]Then[/color]
        ActiveSheet.Shapes.Range(MyArray).Select
    [color=darkblue]Else[/color]
        MsgBox "None found...", vbExclamation
    [color=darkblue]End[/color] [color=darkblue]If[/color]
    [/font]

What values does the variable MyArray contain when it reaches the last line of code and tries to select the desired shapes?

 

[xlnt]

My assessment of the Shape().Name returning a string is this: If you were to simply loop through the shapes and concatenate the names, you would end up with a string inside quotations. If you look at my original attempt, the concatenated string of shape names looked something like this "Line 1, Line 2, Line 3"; however, when you loop through the shapes and assign each Shape.Name to an array position, it looks like this "Line 1", "Line 2", "Line 3", etc., with the quotes surrounding each element of the array and commas separating them. Your code works where mine didn't because each individual Shape.Name string is assigned to a position within the array, but the array itself is not a string, it is a matrix. I don't know a data type which corresponds with a matrix. Perhaps the most perplexing issue is that it appears to work in 2010 and not in 2003.

Your additional code is excellent for handling errors.