Looping through multiple Command Buttons to change their properties based on cell values

Question

I have an interactive table filled with over 100 different command buttons, each of which assign values to variables then use a custom function to calculate their output. They reference a table on the second sheet to get the values they assign. Is there a way to, based on whether a cell contains content or not, change the color of each button? So far, here's what I have (non-functional, of course).

Sub Auto_Open()
Dim n As Integer
n = 2
Do Until n = 114
    If Sheet2.Cells(n, 4) = vbNullString Or Sheet2.Cells(n, 5) = vbNullString Or Sheet2.Cells(n, 8) = vbNullString Or Sheet2.Cells(n, 9) = vbNullString Or Sheet2.Cells(n, 10) = vbNullString Or Sheet2.Cells(n, 11) = vbNullString Then
        ActiveSheet.Shapes.Range(Array("CommandButton" & (n - 1))).Select
        Range.Array(Selection).BackColor = 500
    Else
        ActiveSheet.Shapes.Range(Array("CommandButton" & (n - 1))).Select
        Range.Array(Selection).BackColor = 300
    End If
n = n + 1
Loop

End Sub

EDIT: I can't explicitly state the color for each command button without having to write in over 100 different cases. I have 112 different command buttons; I'd have to write 112 seperate IF statements.

I need to change the color of the command buttons placed over the cells. Since they cover the cells completely, changing the cell color doesn't have any visible effect. That was my first plan, but I moved to this. — KuCoder, Aug 12 '14 at 14:15

score 1 · Accepted Answer · edited May 23 '17 at 12:14

Example for one command button:

Dim cb As CommandButton
Set cb = Sheet1.CommandButton1
With Sheet2.Range("A1")
    If .Value = "Red" Then
        cb.BackColor = RGB(255, 0, 0)
    ElseIf .Value = "Green" Then
        cb.BackColor = RGB(0, 255, 0)
    Else
        cb.BackColor = RGB(155, 155, 155) ' gray
    End If
End With

If you want to loop through many command buttons, you can do as follows. In this example, I look at cells A1:A5 on Sheet2, and set the colors of Sheet1's commandbuttons 1 through 5 accordingly.

Dim cb As CommandButton
Dim i As Long

For i = 1 To 5
Set cb = Sheet1.Shapes("CommandButton" & i).OLEFormat.Object.Object ' Ouch!
    With Sheet2.Range("A1").Cells(i, 1)
        If .Value = "Red" Then
            cb.BackColor = RGB(255, 0, 0)
        ElseIf .Value = "Green" Then
            cb.BackColor = RGB(0, 255, 0)
        Else
            cb.BackColor = RGB(155, 155, 155) ' gray
        End If
    End With
Next i

The .Object.Object trick I got from here.

Looping through multiple Command Buttons to change their properties based on cell values

1 Answers1

Linked