将复选框插入每个单元格并将其分配给Excel中的该单元格的脚本

I am working on a project where I need to add a checkbox to every cell which is linked to that cell. When clicked it will return true, when unchecked it returns false into the cell it is assigned to. 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:

我正在开发一个项目，我需要为每个链接到该单元格的单元格添加一个复选框。单击时，它将返回true，当取消选中时，它将false返回到分配给它的单元格中。工作表有数千个单元格，当我手动插入它们时，我意识到必须有一个更好的解决方案。我正在处理的工作表看起来像这样：

Excel Worksheet in Need of Checkboxes

需要复选框的Excel工作表

Please let me know if I should be running a script/macro or something - I greatly appreciate your help!

如果我应该运行脚本/宏或其他东西，请告诉我 - 我非常感谢您的帮助！

2 个解决方案

#1

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

#2

Here is more generic VBA macro that I used 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$1
    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)

#1