excel - 选中复选框时写入复选框旁边的单元格

Question

我在excel中有一个待办事项列表。选中复选框时，将运行一个宏，该宏选择特定单元格并将值添加到该单元格的偏移量。问题是我有 600 个复选框，它们都需要自己的代码来引用正确的单元格。

private sub checkbox1_click ()
Range ("I2").offset(0,3).value= "hello world"
Sub end 

我想要这样的东西:

Range ("location of checkbox I just checked").offset(0,3).value= "hello world"

Answer 1

如果您使用 ActiveX 控件复选框而不是表单控件，这会更容易。

使用 ActiveX 控件复选框，您可以将对象作为 Me 的一部分引用。 ，它指向工作表本身并使用如下内容:

Private Sub CheckBox1_Click()

    If Me.CheckBox1.Value = True Then

        Me.CheckBox1.TopLeftCell.Offset(0, 3).Value = "hello world"

    End If

End Sub

如果您不能使用 ActiveX 控件，请告诉我，我可以调整我的答案。请注意，您还可以通过查看 this question 的答案来查看有关如何使复选框引用自身的更多信息。 .

在 PeterT 的回答中，您还可以看到如何使用类模块来避免每个复选框有一个宏的问题。在这里，我正在复制答案的相关部分:

[...] Create a class module that you can instantiate for any number of CheckBoxes.

Code for Class module MyCheckBoxClass

Dim WithEvents cbControl As MSForms.CheckBox

Private controlName As String

Public Sub cbControl_Click()
    Debug.Print controlName & " is now " & cbControl.Value
    If cbControl.Value = True Then
        Range("CheckBoxCount") = Range("CheckBoxCount") + 1  'choose to store on the sheet
        groupCheckBoxCount = groupCheckBoxCount + 1          'or in a variable
    Else
        Range("CheckBoxCount") = Range("CheckBoxCount") - 1
        groupCheckBoxCount = groupCheckBoxCount - 1
    End If
End Sub

Public Sub Attach(newCB As MSForms.CheckBox, newName As String)
    Set cbControl = newCB
    controlName = newName
End Sub

Private Sub Class_Initialize()
    controlName = ""
End Sub

Code in a regular code module:

Public groupClickCount As Integer
Private cbCollection As Collection

Public Sub SetUpControlsOnce()
    Dim thisCB As MyCheckBoxClass
    Dim ctl As OLEObject
    Dim cbControl As MSForms.CheckBox

    If cbCollection Is Nothing Then
        Set cbCollection = New Collection
    End If

    For Each ctl In ThisWorkbook.Sheets("Sheet1").OLEObjects
        If TypeName(ctl.Object) = "CheckBox" Then
            '--- this is an ActiveX CheckBox
            Set thisCB = New MyCheckBoxClass
            thisCB.Attach ctl.Object, ctl.name
            cbCollection.Add thisCB
        End If
    Next ctl
End Sub

当然，您必须将“Sheet1”替换为您的工作表的适当名称，并且

If cbControl.Value = True Then
    Range("CheckBoxCount") = Range("CheckBoxCount") + 1  'choose to store on the sheet
    groupCheckBoxCount = groupCheckBoxCount + 1          'or in a variable
Else
    Range("CheckBoxCount") = Range("CheckBoxCount") - 1
    groupCheckBoxCount = groupCheckBoxCount - 1
End If

与

If cbControl.Value = True Then

    cbControl.TopLeftCell.Offset(0, 3).Value = "hello world"

End If

最后，我建议运行 SetUpControlsOnce通过将工作簿包含在工作簿对象 (Thisworkbook) 的打开事件中来打开工作簿时的宏。 IE。:

Private Sub Workbook_Open()
    Call SetUpControlsOnce
End Sub