vba 使用VBA系统地定义一组复选框的链接单元格（excel）
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16532877/
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
Using VBA to systematically define the linked cell for a group of check boxes (excel)
提问by Gideon
In a spreadsheet I have a large number of pre-existing check-boxes, setting the linked cell of each manually would be a tedious task.
在电子表格中，我有大量预先存在的复选框，手动设置每个的链接单元格将是一项乏味的任务。
I hope to group large numbers of them, and then write VBA code that achieves:
我希望将其中的大量分组，然后编写VBA代码，实现：
i = 1
n = number of checkboxes in group
While i < n
Loop
For checkbox i in 'group X', assign linked cell to cell (range A1-->Z1)
End loop
Obviously this isn't VBA, but I'm not familiar with the syntax, does anyone know a) if it possible to execute such a function (i.e. by grouped elements + assigning linked cells) b) the commands / syntax I need to lookup to write it.
显然这不是 VBA，但我不熟悉语法，有没有人知道 a）是否可以执行这样的函数（即通过分组元素+分配链接单元格）b）我需要查找的命令/语法写它。
Many thanks
非常感谢
回答by Floris
This code will do what you want:
此代码将执行您想要的操作：
Sub linkFromGroup()
Dim g ' we put groups in this variable
Dim gc As Integer ' group count - number of elements in group
Dim r As Range ' points to cell we will link to
Set r = Range("A1") ' initially point to cell A1 - this could be anything
' we will know something is a group when we can count the objects in the group
' if we try to count objects that don't exist we will get an error.
' we will trap that with the following line:
On Error Resume Next
' turn off screen updating while macro runs - or it will flicker
Application.ScreenUpdating = False
' loop over all the "shapes" in sheet1. A group is a special kind of shape
For Each g In Sheets("Sheet1").Shapes
' set count to zero
gc = 0
' see if we get a value other than zero
gc = g.GroupItems.Count ' on error we go to the next line and gc will still be zero
If gc > 0 Then
For ii = 1 To gc
g.GroupItems.Item(ii).Select
Selection.LinkedCell = r.Address ' right now I am assuming only check boxes in groups...
Selection.Caption = "linked to " & r.Address ' not necessary - but shows which box was linked to what.
Set r = r.Offset(1, 0) ' next check box will be linked with the next cell down from r
Next ii
End If
Next g
Application.ScreenUpdating = True ' turn on normal operation again
End Sub
Example of what my test sheet looks like after running this (there were two groups and a single check box):
运行此测试表后的示例（有两组和一个复选框）：
Single check box wasn't touched - the groups were. I never clicked box $A$8 so its value doesn't show up as either TRUE or FALSE.
没有触及单个复选框 - 组。我从未点击过 $A$8 框，因此它的值不会显示为 TRUE 或 FALSE。
You need to open the VBA editor (Alt-F11), insert a module, and paste in the above code. You can then run it using (Alt-F8) and picking the macro from the list that is shown. There are many other ways you can do this. It sounds from your question that you can adapt the code from here. Make sure you do this on a copy of your spreadsheet first - until you are sure this is working as you want it to!
需要打开VBA编辑器（Alt-F11），插入一个模块，粘贴上面的代码。然后您可以使用 (Alt-F8) 运行它并从显示的列表中选择宏。有许多其他方法可以做到这一点。从您的问题中可以看出，您可以从此处调整代码。确保首先在电子表格的副本上执行此操作 - 直到您确定它按您的意愿工作！
回答by Santosh
Is it what you are looking for ?
这是您要找的吗？
Below code checks for each checkbox on sheet1 and sets the linked cell property starting from cell A1 & so on.
下面的代码检查 sheet1 上的每个复选框，并设置从单元格 A1 等开始的链接单元格属性。
Lets keep it simple.
让我们保持简单。
Sub sample()
Dim i As Integer
Dim chk As Variant
i = 1
With Sheets("Sheet1")
For Each chk In .OLEObjects
If TypeName(chk.Object) = "CheckBox" Then
chk.LinkedCell = .Range("A" & i).Address
i = i + 1
End If
Next
End With
End Sub
