VBA - 从单元格地址引用 CheckBox
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41896374/
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 - Refer to CheckBox from the cell address
提问by Trenera
I am looping through few rows and I need to know if the CheckBox in each row is "Checked" or not, but I don't know the name of the CheckBox. The below code is just to illustrate the problem:
我正在遍历几行，我需要知道每行中的 CheckBox 是否为“已选中”，但我不知道 CheckBox 的名称。下面的代码只是为了说明问题：
Sub Checkboxes()
Dim ws As Worksheet
Set ws = Sheets("Input Data")
Dim Switch As Boolean
For i = 4 To 8
Switch = ws.Cells(i, 11).CheckboxValue
MsgBox Switch
Next i
End Sub
To create the checkboxes I did the following:
要创建复选框，我执行了以下操作：
- Create a CheckBox
- Place it in a cell
- Copy below in the same column
- 创建复选框
- 把它放在一个单元格中
- 在同一栏中复制以下内容
I assume the code should be the exact opposite of:
我认为代码应该与以下完全相反：
CheckBox1.LinkedCell
回答by Trenera
This is a good workaround. The code links all CheckBoxes to the cell that they're in and gives them Boolean value (TRUE/FALSE). For visual appearance, I have used "Number Formating" that makes the text "TRUE/FALSE" invisible. All you need to do is call the function with the Worksheet (where the CheckBoxes are) as input. The idea came from Aeneas
这是一个很好的解决方法。该代码将所有 CheckBox 链接到它们所在的单元格，并为它们提供布尔值 (TRUE/FALSE)。对于视觉外观，我使用了“数字格式”，使文本“真/假”不可见。您需要做的就是使用工作表（复选框所在的位置）作为输入调用该函数。这个想法来自埃涅阿斯
Public Function Link_Checkboxes_To_Cells(ws As Worksheet)
'This function is linking the checkboxes to the cells that they are in, so that the value of the cell becomes TRUE/FALSE when using the checkbox within.
'Meanwhile, I have manually made the text invisible in the cells with checkboxes, using the following method:
' https://support.office.com/en-us/article/Hide-or-display-cell-values-c94b3493-7762-4a53-8461-fb5cd9f05c33#bm1
' Number Type ---> Custom --> Then type ";;;" (without the quotes) and OK
Dim chk As CheckBox
For Each chk In ws.Checkboxes
With chk
.LinkedCell = _
.TopLeftCell.Offset(0, 0).Address
End With
Next chk
End Function
回答by Aeneas
Try....
尝试....
Sub Checkboxes()
Dim ws As Worksheet
Set ws = Sheets("Input Data")
Dim Switch As Boolean
For Each cb In ws.Checkboxes
If cb.Value = 1 Then
Switch = True
Else
Switch = False
End If
MsgBox cb.Name & " Value= " & Switch
Next cb
End Sub
