mercredi 19 juillet 2017

Big CheckBoxes to Check Smallest Ones

I am looking for a solution to my problem. I have this huge excel sheet (what is important here is that it is made of more than 700 rows), and I would like to make what I called "big checkboxes" to check "little checkboxes" click here to see a picture which explains it better.

The fact is that I have a huge amount of data and I can't know exactly how many little checkboxes will be linked to one big checkbox, so I have to make a macro to do the link for each checkbox. I have already written a macro which automatically create big and little checkboxes, so now I have just to create the link.

I have created the "link" macro, and it works on a small spreadsheet. But on my 700-rows spreadsheet, it takes between 20 and 30 seconds for the program when I check a big checkbox to check the little checkboxes which are linked to it.

I think that I am not doing it the right way: for exemple, to distinguish big checkboxes from little ones, I've named them CBX_"range of cell where checkbox is" instead of cbx__"range of cell where checkbox is".

Please can you help me to make a faster program, really it shouldn't be complicated, it is just that I'm not really comfortable with the use of checkboxes.

Sub HideLinkedChkV3()
Application.ScreenUpdating = False
Dim bigchk As CheckBox
Dim chk As CheckBox
Dim lenrange As Integer
Dim diff As Long
Dim diff1 As Integer
Dim diff2 As Integer
Dim rgr_start As Integer
Dim rgr_end As Integer
'rgr start and end are the rows where begin and end the little checkboxes
Dim rgc As Integer
'rgc is the column where begin the little checkboxes
rgr_start = 7
rgr_end = ActiveSheet.UsedRange.Rows(ActiveSheet.UsedRange.Rows.Count).Row
rgc = 2
lenrange = Range(Cells(rgr_start, rgc), Cells(rgr_end, rgc)).Rows.Count
For Each bigchk In ActiveSheet.CheckBoxes
'bigchk is a checkbox which checks multiple little checkboxes
If Left(bigchk.Name, 3) = "CBX" Then
'here we're making sure that the checkbox is a big check
    If bigchk.Value = Checked Then
        For Each chk In ActiveSheet.CheckBoxes
            diff1 = CInt(Right(chk.Name, Len(chk.Name) - 5))
            diff2 = CInt(Right(bigchk.Name, Len(bigchk.Name) - 5))
            diff = diff1 - diff2
            If Left(chk.Name, 3) = "cbx" And diff < lenrange And diff >= 0 Then
            'here we are making sure that the checkbox is a little checkbox and that it belongs to the big checkbox on its left
                chk.Value = Checked
            End If
        Next chk
    Else
        For Each chk In ActiveSheet.CheckBoxes
            diff1 = CInt(Right(chk.Name, Len(chk.Name) - 5))
            diff2 = CInt(Right(bigchk.Name, Len(bigchk.Name) - 5))
            diff = diff1 - diff2
            If Left(chk.Name, 3) = "cbx" And diff < lenrange And diff >= 0 Then
                chk.Value = Unchecked
            End If
        Next chk
    End If
End If
Next bigchk
Application.ScreenUpdating = True
End Sub



Aucun commentaire:

Enregistrer un commentaire