How to check a checkbox in VBA and sorting-CodePudding

I wrote these lines for selecting lines which are crossed with checkboxes (sorry for my English)

I'd like to take data only on "memoire/registre" with a true checkbox. But I don't know how to check the state of a checkbox in VBA.

The sheet look like this:

Sub tri()

    Dim MaFeuille As Worksheet
    

  ' Definition des Colonnes'
    Selection_Column = 1
    Type_Column = Selection_Column   1
    Adresse_Colum = Name_Column   1
    Data_Column = Adresse_Colum   1
    
    'Definition des valeur de Type_Column'
    Type_Registre = "REGISTRE"                      ' Definit la zone dans laquelle les Registres vont être définis'
    Type_Memoire = "MEMOIRE"
    
    'Init de la feuille active
    Set MaFeuille = Sheets("Pilotage")
    
    
    nb_ligne = Cells.Find("*", [A1], , , , xlPrevious).Row

    
    i = 2
    
    While i <= nb_ligne
            
        If MaFeuille.Cells(i, Type_Column) = Type_Registre And MaFeuille.Cells(i, Selection_Column) = True Then
    
        Debug.Print MaFeuille.Cells(i, Data_Column)
          
        End If
        
        i = i   1
        
    Wend

    
End Sub

But it's not working

I set CheckBoxes with this code

Sub ListeCase_Cliquer()

 Dim CB As Excel.CheckBox
 Dim R As Range
 Dim i As Integer, NbreLigne As Integer, NbChb As Integer
 
 
    'Init de la feuille active
    Set MaFeuille = Sheets("Pilotage")
  
    'Sélection de la page active
    Sheets("Pilotage").Select

    With ActiveSheet

         If .CheckBoxes.Count > 0 Then
            .CheckBoxes.Delete
         End If

         NbreLigne = .Cells.Find("*", [A1], , , , xlPrevious).Row
         NbChb = 1
         For i = 2 To NbreLigne
             Set R = .Range("A" & i)
             Set CB = .CheckBoxes.Add(R.Left, R.Top, R.Width, R.Height)
             With CB
                 .Name = "CheckBox" & Format(NbChb, "")
                 .Text = ""
                 NbChb = NbChb   1
             End With
             Set R = Nothing: Set CB = Nothing
        Next i
    End With

 End Sub

CodePudding user response：

I'm not sure if this is what you are trying to do, but here we go:

Sub tri()

    Dim MaFeuille As Worksheet

  ' Definition des Colonnes'
    Selection_Column = 1
    Type_Column = Selection_Column   1

   ' I think this is wrong:
   ' Adresse_Colum = Name_Column   1 

   'I think it should be:
    Adresse_Colum = Type_Column   1

    Data_Column = Adresse_Colum   1
    
    'Definition des valeur de Type_Column'
    Type_Registre = "REGISTRE"                      ' Definit la zone dans laquelle les Registres vont être définis'
    Type_Memoire = "MEMOIRE"
    
    'Init de la feuille active
    Set MaFeuille = Sheets("Pilotage")
    
    nb_ligne = Cells.Find("*", [A1], , , , xlPrevious).Row

    i = 2
    
    While i <= nb_ligne
       ' add this line:
       Set ctrCheckBox = MaFeuille.CheckBoxes("CheckBox" & (i - 1))
    
       'change this line:
       If ((MaFeuille.Cells(i, Type_Column) = Type_Registre) And (ctrCheckBox.Value = 1)) Then
    
          Debug.Print MaFeuille.Cells(i, Data_Column)

       End If
        
        i = i   1
        
    Wend
    
End Sub