jeudi 27 septembre 2018

Looping on CheckBoxes to sum values corresponding to CheckBoxes

I try to loop on my checkboxes in order to get the sum of data corresponding to those checkboxes, on this base :

exemple

My problem is I have like 200 checkboxes, so I really cant rely on single events. I ve been looking for solutions, and i heard about OLEObject.

Here is my try, please let me know what is wrong and how to write it properly so I can create the adequate loop to my checkboxes (describing the rows corresponding) :

 Sub Sommer()

Dim i As Byte
For i = 7 To 15 
If ThisWorkbook.Worksheets(2).OLEObject("CheckBox" & i).Value = True And ThisWorkbook.Worksheets(2).Cells(i, 4) = "" Then

For Each j In Array(7, 8, 9, 10, 13, 14, 15)
ThisWorkbook.Worksheets(2).Cells(6, j).Value = ThisWorkbook.Worksheets(2).Cells(6, j).Value + ThisWorkbook.Worksheets(2).Cells(i, j).Value
ThisWorkbook.Worksheets(2).Cells(i, 4).Value = "Sélectionné"
Next j

ElseIf ThisWorkbook.Worksheets(2).OLEObject("CheckBox" & i).Object.Value = False And ThisWorkbook.Worksheets(2).Cells(i, 4) = "Sélectionné" Then

For Each j In Array(7, 8, 9, 10, 13, 14, 15)
ThisWorkbook.Worksheets(2).Cells(6, j).Value = ThisWorkbook.Worksheets(2).Cells(6, j).Value - ThisWorkbook.Worksheets(2).Cells(i, j).Value
ThisWorkbook.Worksheets(2).Cells(i, 4).Value = ""
Next j

End If
Next i

End Sub




Aucun commentaire:

Enregistrer un commentaire