Excel function to check for check box marked?

Question

I'm new to working in excel. Basically I'm trying to print some statements in a result box if all relevant check boxes are checked in a given column. Its laid out like the following for a given column:

A                     B
Number of checkboxes: 8
Overall status:       ["Pass" if all 8 checkboxes are checked below, else "fail"]
Checkboxs:            checkbox 1 []
                      checkbox 2 []
                       ...
                      checkbox 8 [X]

I'm using Form Control check boxes, How can i refer to each of the 8 checkboxes in cell b2?

Do I have to use a macro? I wanna this to evaluate as a function so that I don't have to necessarily click on a cell, or do some manual actions for it to evaluate — jerryh91, Aug 19 '14 at 15:06
The problem is that for a given cell I need to have a description next to the checkbox that's specific to this cell. If I use plain text, then I would have to check for existence of X, which would complicate things if there's an X in the description. Or can I check for only the last character of the cell? — jerryh91, Aug 19 '14 at 15:18

Matt Cremeens · Answer 1 · 2014-08-20T12:58:05.467

1

You can right click on the check box, go to format control and have it link to a cell. That cell will then have True or False in it, depending on if the box is checked or not. Then you can have an if formula (e.g., =if(A1=TRUE, ...) determine if the cell is true. If it's true, then the box is checked. Since you have 8 checkboxes, you might need something like

=if(AND(A1=TRUE, A2=TRUE, A3=TRUE, ..., A8=TRUE), "Pass", "Fail")

If you do find you are interested in a macro that automatically links to a cell right below the checkbox, you could assign each checkbox to a macro such as this one

Private Sub CheckBox1_Click()
  thisLink = ActiveSheet.Shapes("Check Box 1").TopLeftCell.Offset(2).Address()
  ActiveSheet.Shapes.Range(Array("Check Box 1")).Select
  With Selection
    .LinkedCell = thisLink
  End With
  ActiveCell.Select
End Sub

edited Aug 20 '14 at 12:58

answered Aug 19 '14 at 15:42

Matt Cremeens

4,951
7
38
67

How do I select cell under the currently checked or unchecked checkbox? – jerryh91 Aug 19 '14 at 17:00
When you right click on your check box, select format control, and in Cell link, put the cell reference there where you want the True or False to show (e.g., `C3`). Do for each checkbox you have. – Matt Cremeens Aug 19 '14 at 17:09
Is there a more scalable approach to this, I simply want the cell underneath the checkbox to be marked T/F as appropriate. I don't want to hard code a particular cell – jerryh91 Aug 19 '14 at 18:00
I would like to know how to do this in the macro linked to the checkbox. Thanks – jerryh91 Aug 19 '14 at 18:01
There is no hardcoding with my approach. The cell you link to will automatically populate with true or false depending on whether or not the box is checked. – Matt Cremeens Aug 19 '14 at 18:36
I'm thinking more on the lines of copy-paste. I link 1 checkbox to a cell, if I make multiple copies of that checkbox, it would fill the cell below that checkbox with T or F independent of the values of the other checkboxes – jerryh91 Aug 19 '14 at 18:41
Well, you would need to repeat the process for all 8 checkboxes, making a different link cell for each. – Matt Cremeens Aug 19 '14 at 19:03
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/59605/discussion-between-jerryh91-and-matt-cremeens). – jerryh91 Aug 19 '14 at 19:05
I wish I could, but it is blocked on my computer here. – Matt Cremeens Aug 19 '14 at 19:10
Thanks for your help so far :) Is there any way that I could write a function of some sort to insert into cell link under Format Control > Control. So no matter where I copy a given checkbox the cell underneath that checkbox is always T or F depedent on the checkbox status – jerryh91 Aug 19 '14 at 20:18
If you want to write vba to automate this, then you might as well look deeper into the solution provided by @ejlj. – Matt Cremeens Aug 20 '14 at 12:55

score 0 · Answer 2 · answered Aug 19 '14 at 15:07

0

You will need VBA.

Sub ckBoxChecked()
    Dim allChecked As Boolean
    Dim ckbx As Variant
    For Each ckbx In ActiveSheet.CheckBoxes
        If ckbx.Value > 0 Then
            allChecked = True
        Else
            allChecked = False
            Exit For
        End If
    Next
End Sub

answered Aug 19 '14 at 15:07

ejlj

175
3

Do I have to use a macro? I wanna this to evaluate as a function so that I don't have to necessarily click on a cell, or do some manual actions for it to evaluate – jerryh91 Aug 19 '14 at 15:09

Excel function to check for check box marked?

2 Answers2