VBA - How to select/change shapes with the same name

Elolo

New Member
Joined
Oct 5, 2018
Messages
4
Hi, I developed a macro to change shapes colors based on the value of a cell. Originally I wrote this code

Rich (BB code):
ActiveSheet.Shapes.Range(Array("Col")).Select
If Range("D69") = "0" Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
End If

It was working well until I realized it was not changing the format of all shapes with the same name (it changes only one of them)
Doing a little of researching in blogs I think that the looping function can address what i am looking for.
I tried again with the following code (see changes in red).

Rich (BB code):
For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "Col" Then
ActiveSheet.Shapes.Range(Array("Col")).Select
If Range("D69") = "0" Then
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Selection.ShapeRange.Fill.ForeColor.RGB = vbYellow
End If
End If
Next Shape


But still not changing the format of the second shape with the same name.


Would somebody please provide advice / recommendations?

Thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Try if this works:
Code:
[COLOR=#ff0000]For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "Col" Then[/COLOR]
[COLOR=#008000]'ActiveSheet.Shapes.Range(Array("Col")).Select[/COLOR]
If Range("D69") = "0" Then
Shape.Fill.ForeColor.RGB = RGB(127, 127, 127)
ElseIf Range("D69") = "1" Then
Shape.Fill.ForeColor.RGB = vbRed
ElseIf Range("D69") = "2" Then
Shape.Fill.ForeColor.RGB = vbYellow
[COLOR=#ff0000]End If[/COLOR]
End If
[COLOR=#ff0000]Next Shape[/COLOR]
 
Last edited:
Upvote 0
Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Thanks you very much! It has work! Even with a smaller code!

I have now another issue that I could not resolve.
I am using the same logic to link the 'text' of shapes with same name to another cell. I could it make it work but I want to have it in % format (as it is in the cell). I've try different suggestions that I found in blogs but I could not make it work. See below the code I'am using.
Code:
For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "TextCol" Then
Shape.TextFrame.Characters.Text = Range("F69")
Shape.TextFrame.TextRange.Characters.NumberFormat = ("0.00%")
End If
Next Shape

Would you help me with this one as well?
 
Upvote 0
Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

What exactly is in F69?
 
Upvote 0
Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Is it a % number (lets say -15%). With the original code, is its bringing to me shape the number -0.15215
 
Upvote 0
Re: VBA - How to select/change shapes with the same name (HELP PLEASE!!)

Try the following code.

Rich (BB code):
Dim st as String

For Each Shape In ActiveSheet.Shapes
If Shape.Name Like "TextCol" Then
st = Left(Range("F69"), InStr(Range("F69"), ".") + 2) & "%"
shape.TextFrame.Characters.Text = st
End If
Next Shape
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,175
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top