Excel VBA: Control multiple pass/fail checkboxes with one macro

Question

I have a test document that will have a lot of pass/fail checkboxes on it. The checkboxes are ActiveX and when clicked, I need them to print "Pass" or "Fail" to a cell that they are located in within the Sheet. I need them printed to the sheet because Excel's track changes doesn't record when the checkbox is clicked. These checkboxes are not part of a userform.

In my example below, Checkbox7 and Checkbox8 are located in cell C14. I have over 50 groups of the pass/fail checkboxes and I am trying to figure out a way that all of the checkboxes be handled by 1-2 Subs instead of having one per each checkbox.

Private Sub CheckBox7_Click()
If CheckBox7 = True Then
    Range("C14").Value = "Pass"
Else
    Range("C14").Value = ""
End If
End Sub

Private Sub CheckBox8_Click()

If CheckBox8 = True Then
    Range("C14").Value = "Fail"
Else
    Range("C14").Value = ""

End If

End Sub

I don't think I can use the same approach found in this solution since I'm not using a userform. Any suggestions/help would be much appreciated

You miss-read that A you linked. It is _exactly_ what you want. — chris neilsen, Apr 20 '21 at 23:44

score 0 · Answer 1 · answered Apr 21 '21 at 00:05

You can do this without code.

In each Checkbox's properties, set the LinkedCell property to the cell that is covered by the checkbox. Just enter the address, like F2. This cell will now have the value of TRUE or FALSE depending upon the status of the checkbox.
In the cell where you want the comment, enter this formula (referring to the cell you linked to the checkbox), =IF(F2 = TRUE, "Failed", "")

Excel VBA: Control multiple pass/fail checkboxes with one macro

1 Answers1