You can use check boxes on a worksheet, and link the results to a cell. If the box is checked, the cell shows TRUE, and if it’s not checked, the cell shows FALSE or the cell is empty. This makes it easy for someone to give a quick answer to a question, or select an option. You can even link check boxes to cells with a macro, so something happens automatically when the box is clicked.

您可以使用工作表上的复选框，并将结果链接到单元格。 如果选中该框，则该单元格显示TRUE，如果未选中，则该单元格显示FALSE或该单元格为空。 这使某人可以轻松地快速回答问题或选择一个选项。 您甚至可以使用宏将复选框链接到单元格，因此单击该复选框时，某些操作会自动发生。

两部分教程 (Two Part Tutorial)

This will be a two part tutorial on how to link check boxes to a macro.

这将是一个分为两部分的教程，介绍如何将复选框链接到宏。

1. First, we’ll set up the check boxes, and link them to cells. You’ll see how to do this manually, and with a macro.
   首先，我们将设置复选框，并将其链接到单元格。 您将看到如何使用宏手动执行此操作。
2. In the next part, we’ll link check boxes to cells with a macro.
   在下一部分中，我们将复选框链接到带有宏的单元格。

创建待办事项清单 (Creating a To Do List)

To see how the check boxes can be used, we’ll set up a To Do list, with a check box for each item. Here is the list, ready for the check boxes.

要查看如何使用复选框，我们将设置一个“待办事项”列表，其中每个项目都有一个复选框。 这是清单，准备使用复选框。

插入复选框 (Insert a Check Box)

To insert a check box, follow these steps:

要插入一个复选框，请按照下列步骤操作：

• Click the Developer tab on the Ribbon. If you don’t see the Developer tab, follow the instructions here, to show it.

  单击功能区上的“开发人员”选项卡。 如果您没有看到“开发人员”标签，请按照此处的说明进行显示。

• In the Controls group, click the Insert command
  在控件组中，单击插入命令
• There are two types of check boxes – Form Control and ActiveX Control.
  复选框有两种类型：窗体控件和ActiveX控件。

• For this example, we’ll use the check box from the Form Controls, so click that.
  对于此示例，我们将使用“表单控件”中的复选框，因此单击它。
• On the worksheet, click near the top left corner of the cell where you want to add the check box -- cell B4 in this example.
  在工作表上，单击要添加复选框的单元格左上角附近的位置－在此示例中为单元格B4。
• The top left border of the check box frame should be inside cell B4. If necessary, move it down or right, so it’s inside the cell.
  复选框框架的左上边框应在单元格B4内。 如有必要，向下或向右移动它，使其在单元格内。

• Click inside the check box frame, then select all the text, and delete it.
  在复选框框架内单击，然后选择所有文本，然后将其删除。
• Make the check box frame narrower, so it just fits the box.
  使复选框框架更窄，使其恰好适合该复选框。

复制复选框 (Copy the Check Box)

Now that you’ve created and formatted one check box, you can copy it, and paste copies into the other cells.

现在，您已经创建并格式化了一个复选框，您可以复制它，并将副本粘贴到其他单元格中。

NOTE: For a quicker way to add check boxes, you can use the macro from the Excel VBA - Check Boxes page on my Contextures site.

注意：为了更快地添加复选框，可以使用Contextures网站上“ Excel VBA-复选框”页面中的宏。

• Press Ctrl, and click on the check box, if it is not already selected.
  按Ctrl，然后单击复选框(如果尚未选中)。

• On the Ribbon’s Home tab, click Copy (or use the Ctrl + C shortcut)

  在功能区的“主页”选项卡上，单击“复制”(或使用Ctrl + C快捷键)

• Select cell B5, and paste the check box, then paste into cells B6 and B7
  选择单元格B5，并粘贴复选框，然后粘贴到单元格B6和B7

将复选框链接到单元格 (Link the Check Boxes to Cells)

If you add a check box to a worksheet, it isn’t automatically linked to a cell. If you want to capture the check box result, you’ll have to link it to a specific cell.

如果在工作表中添加复选框，则不会自动链接到单元格。 如果要捕获复选框结果，则必须将其链接到特定的单元格。

If there are only a few check boxes, you can link them manually, by following these steps:

如果只有几个复选框，则可以按照以下步骤手动链接它们：

• To select the check box in cell B4, press the Ctrl key, and click on the check box
  要选择单元格B4中的复选框，请按Ctrl键，然后单击复选框
• Click in the Formula Bar, and type an equal sign =
  单击编辑栏中的，然后键入等号=
• Click on the cell that you want to link to, and press Enter
  单击要链接的单元格，然后按Enter

You can see the formula in the screen shot below:  =$D$4

您可以在下面的屏幕快照中看到该公式： = $ D $ 4

NOTE: You can also link to a cell by going into the check box’s properties, but that takes longer.

注意：您也可以通过进入复选框的属性链接到单元格，但这需要更长的时间。

使用编程创建单元格链接 (Use Programming to Create Cell Links)

In an ideal world, you would be able to use a relative reference to a cell as the check box link. Then, if you moved or copied the cell to a different location, it would automatically adjust to refer to the cell that in the same row, 20 columns to the right.

在理想情况下，您将能够使用对单元格的相对引用作为复选框链接。 然后，如果将单元格移动或复制到其他位置，它将自动进行调整，以引用同一行(向右20列)中的单元格。

Unfortunately, that doesn’t work, and you have to link each check box individually. The good news is that you can link check boxes to cells with a macro, if you have lots of check boxes to link.

不幸的是，这是行不通的，您必须单独链接每个复选框。 好消息是，如果要链接的复选框很多，则可以将复选框链接到带有宏的单元格。

Instead of manually linking the remaining check boxes, we’ll use the following macro. To add this code to your own workbook, copy it to a regular code module.

代替手动链接其余复选框，我们将使用以下宏。 要将此代码添加到您自己的工作簿中， 请将其复制到常规代码模块中 。

Sub LinkCheckBoxes()
Dim chk As CheckBox
Dim lCol As Long
lCol = 2 'number of columns to the right for link
For Each chk In ActiveSheet.CheckBoxes
   With chk
      .LinkedCell = _
         .TopLeftCell.Offset(0, lCol).Address
   End With
Next chk
End Sub

运行宏 (Run the Macro)

Then, with the To Do list worksheet active, run the macro:

然后，在“待办事项”列表工作表处于活动状态时，运行宏：

• Click the Ribbon’s View tab, and at the far right, click Macros, then click View Macros, if a drop down list appears.
  单击功能区的“视图”选项卡，然后在最右侧单击“宏”，然后单击“查看宏”(如果出现下拉列表)。
• In the list, click LinkCheckBoxes, and click Run.
  在列表中，单击LinkCheckBoxes，然后单击“运行”。

You won’t see anything happen, but each check box will be linked to the cell two columns to its right.

您什么都不会发生，但是每个复选框都将链接到该单元格右侧的两列。

测试复选框 (Test the Check Boxes)

To test the check boxes, click on each one.

要测试复选框，请单击每个复选框。

• If you add a check mark, the cell that’s two columns to the right should show TRUE.
  如果添加复选标记，则右边两列的单元格应显示TRUE。
• If you clear a check box, the linked cell should show FALSE.
  如果清除复选框，则链接的单元格应显示FALSE。
• If you clear the linked cell, the check box will also be cleared.
  如果清除链接的单元格，该复选框也将被清除。

下一步–将宏添加到复选框 (Next – Adding Macros to Check Boxes)

In my next post, on Thursday, we’ll add a macro to each check box.

在下周四的下一篇文章中，我们将为每个复选框添加一个宏。

I’ll also have the completed sample file that you can download, to see how it all works. ____________

我还将提供完整的示例文件，您可以下载该文件，以查看其工作原理。 ____________