如何在 Excel VBA 2010 的 IF-THEN 语句中使用复选框？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11991308/
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
How do I use checkboxes in an IF-THEN statement in Excel VBA 2010?
提问by TheTreeMan
I need to use the value of checkboxes for an IF-THEN statement. Based on what the user checks, the way I have to calculate things changes. However, I can't figure out how to use the checkbox values, or how to detect them. Here is the code I have so far:
我需要将复选框的值用于 IF-THEN 语句。根据用户检查的内容，我计算事物的方式发生了变化。但是，我无法弄清楚如何使用复选框值，或如何检测它们。这是我到目前为止的代码：
Private Sub Workbook_Open()
Range("E1:F7,A1:A4,B1:B4,C1:C3").Select
With Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
With Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.ColorIndex = 0
.TintAndShade = 0
.Weight = xlThin
End With
Range("A1").Select
Range("A1") = "Time"
Range("B1") = "Specimen Shape"
Range("C1") = "Data Type"
Range("A1:C1").Font.Bold = True
Range("E1") = "Owner"
Range("E2") = "Experiment Date"
Range("E3") = "Specimen ID"
Range("E4") = "Contaminant"
Range("E5") = "Leachant"
Range("E6") = "Temperature"
Range("E7") = "Regression Title"
Range("E1:E7").Font.Bold = True
Columns("A:E").EntireColumn.EntireColumn.Autofit
'Formatting Column A
Columns("A").EntireColumn.ColumnWidth = 9.71
ActiveSheet.CheckBoxes.Add(4, 14.5, 72, 17.25).Select
Selection.Characters.Text = "Days"
Range("A6").Select
ActiveSheet.CheckBoxes.Add(4, 30.5, 73.5, 17.25).Select
Selection.Characters.Text = "Hours"
ActiveSheet.CheckBoxes.Add(4, 45.75, 52.5, 17.25).Select
Selection.Characters.Text = "Minutes"
'Formatting Column B
ActiveSheet.CheckBoxes.Add(58, 14.5, 72, 17.25).Select
Selection.Characters.Text = "Cylinder"
ActiveSheet.CheckBoxes.Add(58, 30.5, 73.5, 17.25).Select
Selection.Characters.Text = "Wafer"
ActiveSheet.CheckBoxes.Add(58, 45.75, 52.5, 17.25).Select
Selection.Characters.Text = "Irregular"
'Formatting Column C
Columns("C").EntireColumn.ColumnWidth = 12.71
ActiveSheet.CheckBoxes.Add(140.5, 14.5, 72, 17.25).Select
Selection.Characters.Text = "Incremental"
ActiveSheet.CheckBoxes.Add(140.5, 30.5, 72, 17.25).Select
Selection.Characters.Text = "Cumulative"
Columns("F").EntireColumn.ColumnWidth = 60
Range("A1:C1").HorizontalAlignment = xlCenter
Range("F1").Select
Dim btn As Button
Dim rng As Range
With Worksheets("Sheet1")
Set rng = .Range("A9:C9")
Set btn = .Buttons.Add(rng.Left, rng.Top, rng.Width, rng.Height)
With btn
.Caption = "After making your selections above, click this button to continue."
.AutoSize = True
.OnAction = "DataInput"
End With
End With
End Sub
What I want it to do, just as a test, is if the "Time" checkbox is checked, and then the button is pressed to continue, I want it to say something like "YAY", using an IF-THEN statement. If the "Time" checkbox is not checked and you press continue, I'd like it to say "AWW...".
我想让它做的，就像一个测试，如果“时间”复选框被选中，然后按下按钮继续，我希望它使用 IF-THEN 语句说“YAY”之类的东西。如果未选中“时间”复选框并且您按继续，我希望它说“AWW...”。
This is what I tried to make that happen, and it isn't working.
这就是我试图实现的，但它不起作用。
Sub DataInput()
If ActiveSheet.Shapes.Range(Array("Check Box 1")).Value = True Then
MsgBox ("Yay")
Else: MsgBox ("Aww")
End If
End Sub
What am I doing wrong?
我究竟做错了什么？
回答by Siddharth Rout
If Sheets("Sheet1").OLEObjects("CheckBox1").Object.Value = True Then
I believe Tim is right. You have a Form Control. For that you have to use this
我相信蒂姆是对的。你有一个表单控件。为此，您必须使用它
If ActiveSheet.Shapes("Check Box 1").ControlFormat.Value = 1 Then
回答by Tim Williams
You can try something like this....
你可以试试这样的......
Dim cbTime
Set cbTime = ActiveSheet.CheckBoxes.Add(100, 100, 50, 15)
With cbTime
.Name = "cbTime"
.Characters.Text = "Time"
End With
If ActiveSheet.CheckBoxes("cbTime").Value = 1 Then 'or just cbTime.Value
'checked
Else
'unchecked
End If
回答by Jon Peltier
A checkbox has a linked cell, which contains the True/False representing the state of the checkbox. It is much easier to reference this cell's value than the value of the embedded object which is the checkbox.
复选框有一个链接的单元格，其中包含表示复选框状态的 True/False。引用此单元格的值比引用复选框的嵌入对象的值要容易得多。
Manually: Right click on the checkbox, choose Format, click in the Linked Cell box, and select the cell to contain the checkbox value.
手动：右键单击复选框，选择格式，单击链接单元格框，然后选择包含复选框值的单元格。
In code:
在代码中：
Set cbTime = ActiveSheet.CheckBoxes.Add(100, 100, 50, 15)
With cbTime
.Value = xlOff ' = unchecked xlOn = checked
.LinkedCell = "$A"
End With
回答by Zach
Sub Datainput()
'Checkbox values are 0 (false), 1 (true), 2 (changed or grey)
If activesheet.CheckBoxes("Check Box 1").value = 1 then
Msgbox ("Yay")
Else: Msgbox("Aww")
End if
End Sub
回答by Giso
I found that I could access the checkbox directly using Worksheets("SheetName").CB_Checkboxname.valuedirectly without relating to additional objects.
我发现我可以直接使用Worksheets("SheetName").CB_Checkboxname.value直接访问复选框而无需与其他对象相关。
回答by Allan F
It seems that in VBA macro code for an ActiveX checkbox control you use
似乎在您使用的 ActiveX 复选框控件的 VBA 宏代码中
If (ActiveSheet.OLEObjects("CheckBox1").Object.Value = True)
如果 (ActiveSheet.OLEObjects("CheckBox1").Object.Value = True)
and for a Form checkbox control you use
对于您使用的表单复选框控件
If (ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = 1)
如果 (ActiveSheet.Shapes("CheckBox1").OLEFormat.Object.Value = 1)
