lundi 28 décembre 2015

VBA: Trigger an Event in Form Control

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




Aucun commentaire:

Enregistrer un commentaire