取消选中 VBA 中的复选框和相应数据

Unchecking Check boxes and corresponding data in VBA

提问by user2956485

I am trying to program a VBA code that will uncheck all my check boxes on a worksheet in Excel and also delete the data from the cell to the left of the check box (it's a date that populates from another macro when the checkbox is checked).

我正在尝试编写一个 VBA 代码，该代码将取消选中 Excel 中工作表上的所有复选框，并删除复选框左侧单元格中的数据（这是选中复选框时从另一个宏填充的日期） .

My checkboxes are from the forms toolbar. I can get the boxes to uncheck, but as soon as I insert the code to delete the date I get

我的复选框来自表单工具栏。我可以取消选中这些框，但是一旦我插入代码以删除我得到的日期

run-time error '1004' "unable to get the checkboxes property of the worksheet class".

运行时错误“1004”“无法获取工作表类的复选框属性”。

Here's the code I'm trying to work with:

这是我正在尝试使用的代码：

Sub ClearAllCheckboxes()
Dim Answer As String
Dim MyNote As String
Dim CB As Object
Dim LRange As String
Dim cBox As CheckBox

LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)
MyNote = "This will Clear all CheckBoxes Proceed? "
Answer = MsgBox(MyNote, vbQuestion + vbYesNo, "???")
LRange = "F" & CStr(LRow)

If Answer = vbNo Then
    Exit Sub
Else
    For Each CB In ActiveSheet.CheckBoxes
        CB.Value = xlOff
        If cBox.Value = 0 Then
            ActiveSheet.Range(LRange).Value = Null
        End If
    Next CB
End If

End Sub

Here's the code used in the checkboxes to add the date and record the information in another worksheet:

这是复选框中用于添加日期并在另一个工作表中记录信息的代码：

Sub Process_CheckBox()

Dim cBox As CheckBox
Dim LRow As Integer
Dim LColumn As Integer
Dim RRow As Integer
Dim LRange As String
Dim RRange As String
Dim ERange As String
Dim FRange As String



LName = Application.Caller
Set cBox = ActiveSheet.CheckBoxes(LName)

'Find row that checkbox resides in
LRow = cBox.TopLeftCell.Row
LRange = "F" & CStr(LRow)
RRow = cBox.TopLeftCell.Row
RRange = "B" & CStr(RRow)
ERange = "E" & CStr(RRow)
FRange = "F" & CStr(RRow)


'Change date in column B, if checkbox is checked
If cBox.Value > 0 Then
ActiveSheet.Range(LRange).Value = Date
ActiveSheet.Range(RRange).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Log").Select
Range("A" & ActiveSheet.Rows.Count). _
End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False
Columns("A:A").EntireColumn.AutoFit

Sheets("Daily").Select
ActiveSheet.Range(ERange).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Log").Select
Range("D" & ActiveSheet.Rows.Count). _
End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

Sheets("Daily").Select
ActiveSheet.Range(FRange).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Log").Select
Range("C" & ActiveSheet.Rows.Count). _
End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
    xlNone, SkipBlanks:=False, Transpose:=False

'Clear date in column B, if checkbox is unchecked
Else
  ActiveSheet.Range(LRange).Value = Null
End If

End Sub

采纳答案by usncahill

Based on your description this should do the job.
A few points:

根据您的描述，这应该可以完成工作。
几点：

• Avoid use of ActiveSheet if possible. Refer directly to the sheet in question or use Me.if the code is on the sheet. e.g. For Each cBox In Me.CheckBoxes.
• You didn't add LRow to the ClearAllCheckBoxes function, which is where it was lacking. You will not need it if you refer directly to the cBox.TopLeftCell.Rowand .Columnvalues.
• You don't need to save every function call to a variable.
  • Instead of: LName = Application.Caller, Set cBox = ActiveSheet.CheckBoxes(LName)
  • Do: Set cBox = ActiveSheet.CheckBoxes(Application.Caller)
• Similarly, you can use Msgboxdirectly in the Ifstatement if you aren't using the answer again later.
• You usually don't need to refer to an object's Valueproperty, just the object, when attempting to set and get it's value.
• You can dimension more than one variable on a line. Just make sure you put a as <type>next to each variable name, or the ones without it will be dimensioned as Variants.
• You don't need to put the variable you are looping after the Nextstatement (unless you have a bunch in a row and they look confusing).
• The colOffset variable below can be replaced by an integer. I don't know how far away your date cells are so I put it in. In my test sheet, it was one cell to the right of the box.

• 如果可能，请避免使用 ActiveSheet。直接参考有问题的工作表，Me.如果代码在工作表上，请使用。例如For Each cBox In Me.CheckBoxes。
• 您没有将 LRow 添加到 ClearAllCheckBoxes 函数中，而这正是它所缺乏的。如果直接引用cBox.TopLeftCell.Row和.Column值，则不需要它。
• 您不需要将每个函数调用都保存到一个变量中。
  • 代替： LName = Application.Caller, Set cBox = ActiveSheet.CheckBoxes(LName)
  • 做： Set cBox = ActiveSheet.CheckBoxes(Application.Caller)
• 同样，如果您以后不再使用答案，则可以Msgbox直接在If语句中使用。
• Value在尝试设置和获取它的值时，您通常不需要引用对象的属性，只需引用该对象。
• 您可以在一条线上标注多个变量的尺寸。只需确保as <type>在每个变量名称旁边放一个，否则没有它的将被标注为变体。
• 您不需要将要循环的变量放在Next语句之后（除非您连续有一堆并且它们看起来很混乱）。
• 下面的 colOffset 变量可以替换为整数。我不知道你的日期单元格离你有多远，所以我把它放进去了。在我的测试表中，它是框右边的一个单元格。

Sub ClearAllCheckboxes()
    Dim cBox As CheckBox, colOffset as Integer

    colOffset= 1 'You need to set this to however far away the date cell is from your checkbox
    If MsgBox("This will Clear all CheckBoxes Proceed? ", vbQuestion + vbYesNo, "???") = vbYes Then
        For Each cBox In ActiveSheet.CheckBoxes
           cBox = xlOff

           ActiveSheet.Cells(cBox.TopLeftCell.Row,cBox.TopLeftCell.Column + colOffset) = Null
        Next
    End If
End Sub

回答by David Zemens

If you were to debug this, you would probably find that LNamehas a value of Error 2023. When I step through this code using F8in debug mode, this is what happens for me, which raises the error in the next line Set cBox = ...

如果您要调试它，您可能会发现它LName的值为Error 2023。当我F8在调试模式下逐步执行此代码时，这就是我发生的情况，这会在下一行中引发错误Set cBox = ...

LName = Application.Caller   
Set cBox = ActiveSheet.CheckBoxes(LName)

I am unclear on what you're doing here or how this procedure is being called. When you step through it, what is the value of LNameand what do you expectit to be?

我不清楚您在这里做什么或如何调用此程序。当你逐步完成它时，它的价值是LName什么，你期望它是什么？

Note that this line requires either a string (name) or an integer/index value from the CheckBoxescollection:

请注意，此行需要来自CheckBoxes集合的字符串（名称）或整数/索引值：

Set cBox = ActiveSheet.CheckBoxes(LName)

If you ensure that the value passed to Lnameis not an error valueAND that it correctly refers to a CheckBoxon the ActiveSheet, your code should work as expected.

如果您确保传递给值Lname是不是错误的值，它正确地是指CheckBox上ActiveSheet，按照预期的代码应工作。