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