VBA 更改表单控件背景颜色

VBA Change form control background color

提问by T. Shaffner

I'm trying to change the background color of a form control checkbox via VBA code. I've tried every variation of code I can find on the internet and am still getting failures.

我正在尝试通过 VBA 代码更改表单控件复选框的背景颜色。我已经尝试了可以​​在互联网上找到的所有代码变体，但仍然失败。

The line I have currently is below, and is the only one I've found so far that doesn't give me compiler errors. When I run it though I get a "Run-time error '438': Object doesn't support this property or method" error on executing this line. This is true whether I set it = to xlBlack, RGB(255,255,255) or "11398133" (not black I know, but I was just trying to see if any color would work).

我目前拥有的行在下面，并且是迄今为止我发现的唯一没有给我编译器错误的行。当我运行它时，虽然我在执行此行时收到“运行时错误‘438’：对象不支持此属性或方法”错误。无论我将它设置为 xlBlack、RGB(255,255,255) 还是“11398133”（我知道不是黑色，但我只是想看看是否有任何颜色可以工作），这都是正确的。

Anyone know what's going on and how I can actually do this?

任何人都知道发生了什么以及我如何实际做到这一点？

    Sheets("Controls").Shapes.Range(Array("Check Box 8")).BackColor = "11398133"  

Answer
I found the answer. For some reason none of the responses worked, but Johnny's answer did help me get closer to it by loading the right object in memory and I could then use the Locals window to track down the property I wanted.

答案
我找到了答案。出于某种原因，没有任何响应起作用，但约翰尼的回答确实通过在内存中加载正确的对象帮助我接近它，然后我可以使用本地窗口来跟踪我想要的属性。

In the end it was identifying the object as Johnny suggested and then just cb.Interior.Color = xlBlack I was looking for. No .Fill and no .DrawingObject. Not sure what makes this different than others that would make that work that way, but there you go.

最后，它按照约翰尼的建议识别了对象，然后只是我正在寻找的 cb.Interior.Color = xlBlack。没有 .Fill 也没有 .DrawingObject。不知道是什么让这与其他使这种工作方式不同的东西不同，但是你去了。

So, for any others who come looking, the code that ended up working for me was the simple addition of the below, and you can find out what the Excel name of the object is (Check Box 8 in my case) by selecting it while recording macros.

因此，对于任何其他人来看，最终对我来说有效的代码是下面的简单添加，您可以通过选择它来找出对象的 Excel 名称（在我的例子中是复选框 8）录制宏。

    For Each cb In Sheets("Controls").CheckBoxes
        If cb.Name = "Check Box 8" Then
            cb.Interior.Color = xlNone
            Exit Sub
        End If
    Next

回答by Johnny Bones

This should do it for you. Follow these steps:

这应该为你做。按着这些次序：

1. Make some form check boxes on a sheet
2. Copy the below code into a module (alt F11, insert, module)
3. run SetMacro
4. Save and test

1. 在工作表上制作一些表单复选框
2. 将下面的代码复制到一个模块中（alt F11，插入，模块）
3. 运行 SetMacro
4. 保存并测试

code:

代码：

Sub SetMacro()
    Dim cb, ws
    For Each ws In ThisWorkbook.Sheets
        For Each cb In ws.CheckBoxes
            If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
        Next cb
    Next ws
End Sub

Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 2
        End If
    End With

If you're only looking to do it on the active sheet, use this block instead:

如果您只想在活动工作表上执行此操作，请改用此块：

Sub SetMacro()
    Dim cb
    For Each cb In ActiveSheet.CheckBoxes
        If cb.OnAction = "" Then cb.OnAction = "CheckedUnchecked"
    Next cb
End Sub

Sub CheckedUnchecked()
    With ActiveSheet.Shapes(Application.Caller).DrawingObject
        If .Value = 1 Then
            .Interior.ColorIndex = 4
        Else
            .Interior.ColorIndex = 2
        End If
    End With
End Sub

回答by Sam

Another possibility is that you want to set the ForeColor not the BackColor.

另一种可能性是您想要设置 ForeColor 而不是 BackColor。

Very simply:

很简单：

Sub changegColor()

   Dim wb As Workbook
   Dim ws As Worksheet
   Dim cb As Object
   Dim rng As Range

   Set wb = ActiveWorkbook
   Set ws = wb.ActiveSheet
   Set cb = ws.Shapes.Range(1)

   With cb.Fill
       .Solid
       .ForeColor.RGB = RGB(0, 255, 0)
   End With   

End Sub

子 changegColor()

   Dim wb As Workbook
   Dim ws As Worksheet
   Dim cb As Object
   Dim rng As Range

   Set wb = ActiveWorkbook
   Set ws = wb.ActiveSheet
   Set cb = ws.Shapes.Range(1)

   With cb.Fill
       .Solid
       .ForeColor.RGB = RGB(0, 255, 0)
   End With   

结束子