How to make checkbox react to adjacent checkbox in Excel

Question

I have 2 columns of checkboxes (from "Form Controls") in Excel (2010) that will indicate whether one of two tasks are completed. The 2nd task cannot be completed until the 1st one is done, but there could be instances where both tasks in a given row are completed in the same day. To avoid any extra clicks, I would like to make it so that if the 2nd task is completed (and checked) then the first task becomes automatically checked.

How do I do this?

For example:

I want to be able to manually check cell B5, but if cell C5 is checked, I want cell B5 to be automatically checked.

• However, I don't want:

  • Unchecking C5 to impact B5
  • Checking/unchecking B5 to impact C5

---

I know I could "cell link" the second checkbox column to a 3rd column to create an output, but I don't know how to make the first column checkbox reactive to that...

• However, ideally, I'd like to forego additional columns if possible.

---

FINAL Solution:

I used @GaryStudent's answer to inform my final generalized solution:

1. Select all columns/rows of desired checkboxes

2. Run WhatIsName macro to change name of each check box to the underlying cell it's within:

   • Note: make sure checkbox is ENTIRELY within each given cell, or it won't work right!!

     Sub WhatsInaName()
       Dim bx As CheckBox
       For Each bx In ActiveSheet.CheckBoxes
           bx.Name = bx.BottomRightCell.Address(0, 0)
       Next bx
     End Sub
     

3. Next, select last row of right checkbox column, right click, assign macro "CheckBoxSimplifier"

   Sub CheckBoxSimplifier()
   it. Applies to all rows above it for some reason
       Dim MySelf As CheckBox, OtherBox As CheckBox
   
       Set MySelf = ActiveSheet.CheckBoxes(Application.Caller)
       Col = Mid(Application.Caller, 1, 1)
       Col2 = Chr(Asc(Col) - 1)
       Set OtherBox = ActiveSheet.CheckBoxes(Col2 & Mid(Application.Caller, 2))
   
       If MySelf.Value = 1 Then OtherBox.Value = 1
   End Sub
   

   • For some reason, the code only works if you apply the macro to the final row of check boxes.

   • Also, I found that I could onyl run the WhatsInaaName macro once per file. If I tried running it a 2nd time (e.g., after adding more rows), it failed to work correctly (and simply copied the final row from the previous time it was run.

Answer 1

This little trick relies on the naming of the CheckBoxes.

You can assign Names to all the checkboxes in the sheet. Say we name them after their associated cells like:

Sub NameThem()
    ActiveSheet.CheckBoxes(1).Name = "C5"
    ActiveSheet.CheckBoxes(2).Name = "B5"
End Sub

Then we assign a little handler to all the C-type checkboxes like:

Sub MACC5()
    Dim C5 As CheckBox, B5 As CheckBox
    Set C5 = ActiveSheet.CheckBoxes("C5")
    Set B5 = ActiveSheet.CheckBoxes("B5")
    If C5.Value = 1 Then B5.Value = 1
End Sub

Each handler sub would be customized to refer to the proper B-type box.

You can also make a general handler for all the C-types.

EDIT1:

Here is a general routine that you can use for all the C's:

Sub GeneralHandler()
    Dim MySelf As CheckBox, OtherBox As CheckBox

    Set MySelf = ActiveSheet.CheckBoxes(Application.Caller)
    Set OtherBox = ActiveSheet.CheckBoxes("B" & Mid(Application.Caller, 2))

    If MySelf.Value = 1 Then OtherBox.Value = 1
End Sub

EDIT#2:

This short sub will rename all the checkboxes in the sheet to the address of their enclosing cell:

Sub WhatsInaName()
    Dim bx As CheckBox
    For Each bx In ActiveSheet.CheckBoxes
        bx.Name = bx.BottomRightCell.Address(0, 0)
    Next bx
End Sub

Just be sure the cell is big enough to completely enclose the box.

Answer 2

Option 1:

Insert two activeX checkboxes to the two column you need them to be in. Checkbox in B5 will be CheckBox1 and C5 will be CheckBox2 then double click CheckBox2 in c5 and enter this code:

    If CheckBox2.Value = True Then
        If CheckBox1.Value = False Then
           CheckBox1.Value = True
        End If
    End If

Option 2:

create two form checkbox controls right click the checkbox in C5 and assign macro. Make sure you add this procedure into your sheet1 code on the editor and assign it to your checkbox:

Sub CheckColumnBCheckBox()
    Dim cb, cb2 As Shape

    Set cb = ActiveSheet.Shapes("Check Box 1")
    Set cb2 = ActiveSheet.Shapes("Check Box 2")

    If cb2.OLEFormat.Object.Value = 1 Then
        If cb.OLEFormat.Object.Value <> 1 Then
            cb.OLEFormat.Object.Value = True
        End If
    End If
End Sub

Option 3:

Use worksheet change event instead of checkbox. Have user enter "X" or "x" in the column B5 and C5 to mimic the same as checkbox: put this code in the sheet1 editor:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Application.Intersect(Target, Range("C5:C500")) Is Nothing Then
        If Target.Value = "X" Or Target.Value = "x" Then
            If Target.Offset(0, -1).Value <> "X" Or Target.Offset(0, -1).Value <> "x" Then
                Target.Offset(0, -1).Value = "X"
            End If
        End If
    End If
End Sub