Trouble looping through Shapes Collection in Visual Basic

[Jaylee52]

Hi Forum,

I've searched the web and tried everything I can think of on my own, but now I am stuck. I have an Excel page with many rounded rectangle shapes on it. There is a subset of them, whose names all start with the same 6 characters, that I want to loop through in order to set them all to the same fill color.

However I am getting a run-time error 13 "Type Mismatch" error with the code below.

[COLOR="Green"]'Turn all rectangles on the page whose name starts with "RenPan" light teal[/COLOR]
[COLOR="Blue"]Dim[/COLOR] Shp [COLOR="blue"]As Shape[/COLOR]
[COLOR="Blue"]Dim[/COLOR] ShpName [COLOR="blue"]As String[/COLOR]
[COLOR="blue"]For Each[/COLOR] Shp [COLOR="blue"]In[/COLOR] ActiveSheet.Shapes
     ShpName = Left(ActiveSheet.Shapes(Shp).Name, 6)      'Error occurs here
     [COLOR="blue"]If[/COLOR] ShpName = "RelPan" [COLOR="blue"]Then[/COLOR]
          [COLOR="blue"]Call[/COLOR] ButtonLightTeal(ActiveSheet.Shapes(Shp).Name)
     [COLOR="blue"]End If[/COLOR]
[COLOR="Blue"]Next[/COLOR] Shp

Any thoughts? Thanks so much,

 

[tigeravatar]

Jaylee52,

Why not refer to Shp directly?

ShpName = Left(Shp.Name, 6)

 

[Jaylee52]

That worked! Thank you very much!!

 

[Rick Rothstein]

Hi Forum,

I've searched the web and tried everything I can think of on my own, but now I am stuck. I have an Excel page with many rounded rectangle shapes on it. There is a subset of them, whose names all start with the same 6 characters, that I want to loop through in order to set them all to the same fill color.

However I am getting a run-time error 13 "Type Mismatch" error with the code below.

[COLOR=green]'Turn all rectangles on the page whose name starts with "RenPan" light teal[/COLOR]
[COLOR=blue]Dim[/COLOR] Shp [COLOR=blue]As Shape[/COLOR]
[COLOR=blue]Dim[/COLOR] ShpName [COLOR=blue]As String[/COLOR]
[COLOR=blue]For Each[/COLOR] Shp [COLOR=blue]In[/COLOR] ActiveSheet.Shapes
     ShpName = Left(ActiveSheet.Shapes(Shp).Name, 6)      'Error occurs here
     [COLOR=blue]If[/COLOR] ShpName = "RelPan" [COLOR=blue]Then[/COLOR]
          [COLOR=blue]Call[/COLOR] ButtonLightTeal(ActiveSheet.Shapes(Shp).Name)
     [COLOR=blue]End If[/COLOR]
[COLOR=blue]Next[/COLOR] Shp

Any thoughts? Thanks so much,

Untested, but I think this code should do what the above code is attempting to do...

'Turn all rectangles on the page whose name starts with "RenPan" light teal
Dim Shp As Shape
For Each Shp In ActiveSheet.Shapes
  If Shp.Name Like "RelPan*" Then Call ButtonLightTeal(ActiveSheet.Shapes(Shp).Name)
Next Shp

 

[Jaylee52]

Thank you!