VBA 灰色复选框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17654379/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
VBA Grey Checkboxes
提问by steventnorris
I would like to grey out my checkboxes in Excel VBA. When using Checkbox.Enabled = False, the checkbox is not editable, but it is also not grey. How do I get the greyed out effect?
我想将 Excel VBA 中的复选框灰显。使用 时Checkbox.Enabled = False，复选框不可编辑，但也不是灰色的。我如何获得灰色效果？
Using form controls with Excel 2010. Inserted via developer tab directly into excel worksheet. Not used in a VBA userform.
在 Excel 2010 中使用表单控件。通过开发人员选项卡直接插入到 Excel 工作表中。未在 VBA 用户窗体中使用。
Thanks!
谢谢！
回答by Floris
Whenever anyone says "it is impossible", it hits my stubborn streak. So may I present to you: "The Impossible".
每当有人说“这是不可能的”时，它就会击中我的固执倾向。所以我可以向你展示：“不可能的”。
"Visible" and enabled checkbox:
“可见”并启用复选框：
"Disabled" checkbox (you can tweak the degree of visibility by changing values in the code for both color and transparency of the cover shape):
“禁用”复选框（您可以通过更改代码中封面形状的颜色和透明度的值来调整可见度）：
Basic idea: you place a semi transparent shape over the checkbox, and assign a dummy macro to it. Now you can't change the value of the checkbox. The "toggle" button is there to change the state - either place the shapes, or remove them. It uses a global variable to track the current state.
基本思想：在复选框上放置一个半透明形状，并为其分配一个虚拟宏。现在您无法更改复选框的值。“切换”按钮用于更改状态 - 放置形状或删除它们。它使用全局变量来跟踪当前状态。
Finally - note that you can't use For Eachwhen you delete (or add) shapes as you should not modify the collection you are iterating over. I circumvented that with a simple "count shapes, then iterate backwards by numerical index".
最后 - 请注意，For Each在删除（或添加）形状时不能使用，因为您不应修改正在迭代的集合。我用一个简单的“计数形状，然后按数字索引向后迭代”来规避它。
Is it a hack? You bet! Does it do what you asked? Yes!
这是一个黑客吗？你打赌！它做你问的吗？是的！
Dim checkBoxesVisible As Boolean
Option Explicit
Sub toggleIt()
' macro assigned to "Toggle visibility" button
checkBoxesVisible = Not checkBoxesVisible
toggleCheckboxes checkBoxesVisible
End Sub
Sub grayOut(cb)
' put a "cover" shape over a checkbox
' change the color and transparency to adjust the appearance
Dim cover As Shape
Set cover = ActiveSheet.Shapes.AddShape(msoShapeRectangle, cb.Left, cb.Top, cb.Width, cb.Height)
With cover
.Line.Visible = msoFalse
With .Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 255, 255)
.Transparency = 0.4
.Solid
End With
End With
cover.Name = "cover"
cover.OnAction = "doNothing"
End Sub
Sub doNothing()
' dummy macro to assign to cover shapes
End Sub
Sub unGray(cb)
' find the cover shape for the checkbox passed as the argument
' and delete it
' "correct shape" has the name "cover" and is properly aligned with top left
Dim sh As Shape
For Each sh In ActiveSheet.Shapes
If sh.Name = "cover" And sh.Left = cb.Left And sh.Top = cb.Top Then
sh.Delete
Exit For
End If
Next sh
End Sub
Sub toggleCheckboxes(onOff)
Dim s As Shape
Dim n As Integer, ii As Integer
n = ActiveSheet.Shapes.Count
' loop backwards over shapes: if you do a "For Each" you get in trouble
' when you delete things!
For ii = n To 1 Step -1
Set s = ActiveSheet.Shapes(ii)
If s.Type = msoFormControl Then
If s.FormControlType = xlCheckBox Then
If onOff Then
unGray s
Else
grayOut s
End If
End If
End If
Next ii
End Sub
回答by Floris
A slight hack - but the following does work. I created a simple userform with two controls - a regular checkbox (CheckBox1), and a button I called "DisableButton" with the following code:
一个小技巧 - 但以下确实有效。我创建了一个带有两个控件的简单用户窗体 - 一个常规复选框 ( CheckBox1) 和一个名为“DisableButton”的按钮，代码如下：
Private Sub DisableButton_Click()
CheckBox1.Enabled = Not (CheckBox1.Enabled)
If CheckBox1.Enabled Then
CheckBox1.ForeColor = RGB(0, 0, 0)
Else
CheckBox1.ForeColor = RGB(128, 128, 128)
End If
End Sub
When I clicked the button, the checkbox was grayed out and unavailable. Clicking it again "brought it back to life". I think this is the effect you were looking for. If it's not - that's what comments are for.
当我单击按钮时，复选框变灰且不可用。再次单击它“让它恢复生机”。我想这就是你要找的效果。如果不是 - 这就是评论的目的。
Here is what it looks like:
这是它的样子：
回答by Floris
回答by Alex
Maybe this is what you want.
也许这就是你想要的。
Private Sub CheckBox1_Click()
私有子 CheckBox1_Click()
If CheckBox1.Value = True Then
CheckBox2.Value = False
CheckBox2.Enabled = False
CheckBox2.ForeColor = rgbBlue
Else
CheckBox2.Visible = True
CheckBox2.ForeColor = rgbAntiqueWhite
CheckBox2.Enabled = True
End If
Code tels that when checkbox1 is checked, checkbox2 is disabled; unchecked and the forecollor changes. the colors can be what you want. Did this with the checkboxes directly in the excel worksheet.
Code tels 当checkbox1 被选中时，checkbox2 被禁用；未选中并且前任发生变化。颜色可以是你想要的。直接使用 Excel 工作表中的复选框执行此操作。
回答by robotik
Based on Floris' idea.
基于弗洛里斯的想法。
The code assumes all the controls are on ActiveSheet and they are called CheckBox1 and CheckBox2, if not, change it accordingly.
代码假定所有控件都在 ActiveSheet 上，它们被称为 CheckBox1 和 CheckBox2，如果不是，则相应地更改它。
You can call this when you click on CheckBox1 or you can call it from another sub, with an optional ticked status (True/False) to check or uncheck CheckBox1.
您可以在单击 CheckBox1 时调用它，也可以从另一个子调用它，并使用可选的勾选状态 (True/False) 来选中或取消选中 CheckBox1。
Draw an object on top of CheckBox2 and name it "mask" (you can name it anything else but then you have to change the code accordingly)
在 CheckBox2 之上绘制一个对象并将其命名为“mask”（您可以将其命名为其他任何名称，但您必须相应地更改代码）
Give mask the same fill color as your background color and opacity of around 50%.
为蒙版提供与背景颜色相同的填充颜色和大约 50% 的不透明度。
Public Sub CheckBox1_Click(Optional ticked As Variant)
Application.ScreenUpdating = False
ActiveSheet.Unprotect
If Not IsMissing(ticked) Then
If ticked = True Then ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = 1 Else ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = -4146
End If
If ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value > 0 Then
ActiveSheet.Shapes("mask").OLEFormat.Object.ShapeRange.ZOrder msoSendToBack
Else
ActiveSheet.Shapes("mask").OLEFormat.Object.ShapeRange.ZOrder msoBringToFront
End If
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
Application.ScreenUpdating = True
End Sub
Now each time you tick CheckBox1, mask comes to front to hide CheckBox2 and when you untick it, mask goes to back to unhide it. Since it is opaque, it gives you the greyed out effect and you don't even have to worry about enable/disable either.
现在，每次您勾选 CheckBox1 时，掩码都会出现在前面以隐藏 CheckBox2，当您取消勾选它时，掩码会返回以取消隐藏它。因为它是不透明的，所以它会给你灰色的效果，你甚至不必担心启用/禁用。
Worksheet should be protected so that user can't accidentally move or edit mask, but should be unprotected for SendToBack/BringToFrontto work, so the code does that. Please check the protection settings at the Application.Protectpart.
工作表应该受到保护，这样用户就不会意外移动或编辑掩码，但应该不受保护SendToBack/BringToFront才能工作，因此代码会这样做。请检查该Application.Protect部分的保护设置。
