vba 在每个单元格中插入一个复选框并将其分配给该单元格
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12804899/
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
Insert a Checkbox into every cell and assign it to that cell
提问by Clinton Warren
I need to add a checkbox to every cell which is linked to that cell. When checked it will return true, when unchecked it returns false into the cell it is assigned to.
我需要为链接到该单元格的每个单元格添加一个复选框。选中时它将返回 true，未选中时它将返回 false 到它分配给的单元格中。
The worksheet has thousands of cells, and as I'm going through inserting them manually, I realize there has to be a better solution.
工作表有数千个单元格，当我手动插入它们时，我意识到必须有更好的解决方案。
The sheet I am working on looks like this:
我正在处理的工作表如下所示：
回答by Scott Holtzman
Here you go, Clinton.
给你，克林顿。
Sub AddCheckBoxes()
Dim cb As CheckBox
Dim myRange As Range, cel As Range
Dim wks As Worksheet
Set wks = Sheets("mySheet") 'adjust sheet to your needs
Set myRange = wks.Range("A1:A10") ' adjust range to your needs
For Each cel In myRange
Set cb = wks.CheckBoxes.Add(cel.Left, cel.Top, 30, 6) 'you can adjust left, top, height, width to your needs
With cb
.Caption = ""
.LinkedCell = cel.Address
End With
Next
End Sub
回答by Slai
Here is generic VBA macro that I used to add centered checkboxes to all selected cells:
这是我用来向所有选定单元格添加居中复选框的通用 VBA 宏：
'ActiveSheet.DrawingObjects.Delete ' optional to delete all shapes when testing
Dim c As Range, cb As CheckBox
For Each c In Selection
Set cb = c.Worksheet.CheckBoxes.Add(c.Left + c.Width / 2 - 8.25, _
c.Top + c.Height / 2 - 8.25, 0, 0) ' 8.25 is cb.Height / 2
cb.Text = vbNullString ' to clear Caption
cb.LinkedCell = c.Address(0, 0) ' Example A1 instead of $A
cb.Name = "cb" & cb.LinkedCell ' optional
Next
Selection.NumberFormat = ";;;" ' optional to hide the cell values
Selection = True ' optional to check all at once (or 'selection = [#N/A]' for all xlMixed)
