VBA: Trigger an Event in Form Control

Question

I am working on a WBS in Excel with checkboxes.

I have the following:

[checkbox1] Level A
---------[checkBox2] item 1
---------[checkBox3] item 2
[checkbox4] Level B
---------[checkBox5] item 3

When I untick checkbox2 it will put an X in the cell next to item 1 If I tick checkbox2, it will remove the X.

If I untick checkbox1, it will untick checkbox2 and checkbox3, but it does not put an X in the cell next to the item 1 and 2. It just unticks the two checkboxes without triggering the event. How do I link that event to checkBox1?

If it is not possible to trigger that kind of event in Form Control, my other questions would be to know how to know the row and column where the checkbox is in ActiveX Control?

In Form Control we can use sheets("sheet1").checkboxes(application.caller), but this does not work in ActiveX Control.

The code for checkbox2 or checkbox3:

Sub CheckBoxLine()
Dim ws As Worksheet
Dim chk As CheckBox
Dim lColD, myCol As Long
Dim lColChk As Long
Dim lRow As Long
Dim rngD As Range
lColD = 1 'number of columns to the right
Set ws = ActiveSheet
Set chk = ws.CheckBoxes(Application.Caller)
lRow = chk.TopLeftCell.Row
lColChk = chk.TopLeftCell.Column
Set rngD = ws.Cells(lRow, lColChk + lColD)

Select Case chk.Value
Case 1   'box is checked
  rngD.Value = "X"
Case Else   'box is not checked
  rngD.Value = "X"

End Select

End Sub

The code for checkbox1:

Select Case chk.Value
Case 1   'box is checked
  For Each cb In ws.CheckBoxes
    If cb.Name = "Check box 2" Then
        cb.Value = 1
    End If

    If cb.Name = "Check box 3" Then
        cb.Value = 1
    End If
  Next cb

Case Else   'box is not checked
  For Each cb In ws.CheckBoxes
    If cb.Name = "Check box 2" Then
        cb.Value = 0
    End If

    If cb.Name = "Check box 3" Then
        cb.Value = 0
    End If
  Next cb
End Select

Answer 1

Answer changed after clarifications.

I think there are no events for form controls. And AFAIK getting the cell where ActiveX control would be is a bit complicated. I have it done in one of my workbooks, but it required some code in an additional Class module and I do not recall how to implement it.

Since you use Form control checkboxes, I think it would be easier to use the following code, instead of employing events from newly created ActiveX checkboxes. I hope it would work as you need.

Option Explicit
Dim ws As Worksheet

Sub CheckBoxLine(Optional strChkName As String)
    Dim chk As CheckBox
    Dim lColD, myCol As Long
    Dim lColChk As Long
    Dim lRow As Long
    Dim rngD As Range

    lColD = 1 'number of columns to the right
    If ws Is Nothing Then Set ws = ActiveSheet
    If strChkName = vbNullString Then
        Set chk = ws.CheckBoxes(Application.Caller)
    Else
        Set chk = ws.CheckBoxes(strChkName)
    End If
    lRow = chk.TopLeftCell.Row
    lColChk = chk.TopLeftCell.Column
    Set rngD = ws.Cells(lRow, lColChk + lColD)

    Select Case chk.Value
    Case 1   'box is checked
      rngD.Value = vbNullString
    Case Else   'box is not checked
      rngD.Value = "X"
    End Select

    Set chk = Nothing
    Set ws = Nothing
End Sub

Sub Code_for_Checkbox1()
    Set ws = ActiveSheet
    ws.CheckBoxes("Check Box 2").Value = ws.CheckBoxes(Application.Caller).Value
    ws.CheckBoxes("Check Box 3").Value = ws.CheckBoxes(Application.Caller).Value
    Call CheckBoxLine("Check Box 2")
    Call CheckBoxLine("Check Box 3")
End Sub