通过多个命令按钮循环，以根据单元格值更改其属性

本文介绍了通过多个命令按钮循环，以根据单元格值更改其属性的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个交互式表，填充了超过100个不同的命令按钮，每个命令按钮都将值分配给变量，然后使用自定义函数来计算其输出。他们引用第二张表上的表以获取它们分配的值。有没有办法，根据单元格是否包含内容，更改每个按钮的颜色？到目前为止，这是我所拥有的（当然不是功能）。

  Sub Auto_Open（）
 Dim n As Integer 
n = 2 
 Do Until n = 114 
如果Sheet2.Cells（n，4）= vbNullString或Sheet2.Cells（n，5）= vbNullString或Sheet2.Cells（n， 8）= vbNullString或Sheet2.Cells（n，9）= vbNullString或Sheet2.Cells（n，10）= vbNullString或Sheet2.Cells（n，11）= vbNullString Then 
 ActiveSheet.Shapes.Range CommandButton&（n-1）））。选择
 Range.Array（Selection）.BackColor = 500 
 Else 
 ActiveSheet.Shapes.Range（Array（CommandButton& （n-1）））。选择
 Range.Array（Selection）.BackColor = 300 
 End If 
n = n + 1 
循环
 
 End Sub

编辑：
我无法显式指定每个命令按钮的颜色，不得不写在100多种不同的情况下。我有112个不同的命令按钮;我必须编写112个单独的IF语句。

解决方案

一个命令按钮的示例：

 code> Dim cb As CommandButton 
 Set cb = Sheet1.CommandButton1 
 With Sheet2.Range（A1）
如果.Value =Red然后
 cb。 BackColor = RGB（255，0，0）
 ElseIf .Value =绿色然后
 cb.BackColor = RGB（0，255，0）
 Else 
 cb.BackColor = RGB（155，155，155）'gray 
 End If 
 End With

如果要循环浏览许多命令按钮，可以执行以下操作。在这个例子中，我看到Sheet2上的单元格A1：A5，并相应地设置Sheet1的命令按钮1到5的颜色。

  Dim cb As CommandButton 
 Dim i As Long 
 
 For i = 1 To 5 
 Set cb = Sheet1.Shapes（CommandButton& i）.OLEFormat.Object.Object 哦！ 
使用Sheet2.Range（A1）。单元格（i，1）
如果.Value =Red然后
 cb.BackColor = RGB（255，0，0）
 ElseIf .Value =Green然后
 cb.BackColor = RGB（0，255，0）
 Else 
 cb.BackColor = RGB（155，155，155）'gray 
结束如果
结束
下一步我

.Object.Object 我从。

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.

解决方案

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.

这篇关于通过多个命令按钮循环，以根据单元格值更改其属性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！