VBA - define variables for shape names & use in a loop function

[yandy]

hello i am new to vba.

Would like to ask how I can define variables for shape names & use in a loop function.

For example, I have drawn 31 different shapes and I named them SShape1, SShape2, SShape3 until SShape31.

And I would like to control the shapes to be changed to different colors when they hit a certain conditions.
I figured out the vba code to change to different colors under different conditions for one shape by the selecting the shape name i defined, and I would like to repeat the same code for the 2nd, 3rd, until the 31st shape, how can I convert the shape name into the loop function?

My original code to select SShape1 to change to a color:

ActiveSheet.Shapes.Range(Array("SShape1")).Select
Selection.ShapeRange.Fill.ForeColor.RGB = RGB(255, 51, 51)


I was trying to:


Dim i As Integer
For i = 1 to 31

But I feel like some connections are missing for i with shape name. Do i need to define like below?


Shapes.Name = "SShape" + i

And how can I convert SShape i to my code?

[georgiboy]

Welcome to the forum yandy,

Here is a method to loop through the shapes:

Sub test()
Dim x As Long

For x = 1 To 6
Sheet1.Shapes("SShape" & x).Fill.ForeColor.RGB = RGB(255, 51, 51)
Next x
End Sub

Here is a method to change them all at once:

Sub test2()
Dim arShapes() As Variant
Dim objRange As Object

arShapes = Array("SShape1", "SShape2", "SShape3", "SShape4", "SShape5", "SShape6")
Set objRange = ActiveSheet.Shapes.Range(arShapes)
objRange.Fill.ForeColor.RGB = RGB(255, 51, 51)
End Sub

Hope this helps

[yandy]

Wow, thanks for the insight :) I'll try it out

[yandy]

It seems to be working for the loop of shapes. I tried to add another variable into it, but it turns out to be issue with i variable. Any idea?



Sub test()
Dim i As Long, x As Long
'set i to the rows of B column, x as variables of shapes

For i = 5 To 35

For x = 1 To 31

If Sheet1.Cells(i, 2) = "No" Then
Sheet2.Shapes("SShape" & x).Fill.ForeColor.RGB = RGB(102, 204, 0)
Else
If Sheet1.Cells(i, 2) = "Yes" Then
Sheet2.Shapes("SShape" & x).Fill.ForeColor.RGB = RGB(255, 51, 51)
Else



End If
End If

Next i, x
End Sub


Invalid next control variable reference.

[georgiboy]

With a few adjustments that would change every shape colour for every row you test, i don't think that is what you want to do although, it is difficult to know what you want to do without seeing your workbook.

I am going to make a guess that you want to change a single shape per row depending on the value in that row, see below for my guess at the VBA you need:


Sub test()
Dim i As Long

For i = 5 To 35
If Sheet1.Cells(i, 2) = "No" Then
Sheet2.Shapes("SShape" & i - 4).Fill.ForeColor.RGB = RGB(102, 204, 0)
ElseIf Sheet1.Cells(i, 2) = "Yes" Then
Sheet2.Shapes("SShape" & i - 4).Fill.ForeColor.RGB = RGB(255, 51, 51)
End If
Next i
End Sub

[yandy]

Yes actually I would like to change every shape color for every row I test, so the input of each row shall affect the color of each shape.
e.g. input on cell B5 shall affect the color of SShape1, B6 shall affect the color of SShape2, and so on.

[georgiboy]

That is what post 5 is doing, have you tested it?

[yandy]

:):):) it works now, exactly the results I want. Thank you so much for sharing your expertise :) It really gives me a new insight of VBA.

[georgiboy]

You're welcome, delighted to assist.

I started my journey into VBA learning right here on VBAX, it influenced the direction of my career.

If this is now solved could you:
Goto thread tools dropdown and select "Mark as solved"