Unchecking Check boxes and corresponding data in VBA

Question

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).

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".

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

Answer 1

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.Row and .Column values.
• 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 Msgbox directly in the If statement if you aren't using the answer again later.
• You usually don't need to refer to an object's Value property, 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 Next statement (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.

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

Answer 2

If you were to debug this, you would probably find that LName has a value of Error 2023. When I step through this code using F8 in debug mode, this is what happens for me, which raises the error in the next line 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 LName and what do you expect it to be?

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

Set cBox = ActiveSheet.CheckBoxes(LName)

If you ensure that the value passed to Lname is not an error value AND that it correctly refers to a CheckBox on the ActiveSheet, your code should work as expected.