Remplissage d'un ComboBox avec filtre

Bonjour,
Je remplis un ComboBox à partir d'un tableau qui a 5 colonnes. Il s'agit d'un tableau dans une feuille et non une variable Tableau.
J'utilise donc la propriété List de la ComboBox et l'objet Range du tableau.
La colonne N°5 de mon tableau contient des valeurs OUI ou NON. Je voudrais donc au moment de remplir la Combo filtrer les données pour ne prendre que les lignes avec la valeur OUI de la 5è colonne.
Ma question est de savoir si dans la procédure ci-dessous je peux intégrer ce filtre ou je suis obligé de passer par une boucle for sur la table pour ne prendre que les lignes souhaitées.

'cette instruction affiche toutes les données dans la Combo (Seule la colonne 2 est visible)
Set FL1 = ThisWorkbook.Sheets("CATEGORY")    
With cboCategory
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "0;200;0;0;0"
    .List = FL1.Range("A2:E" & FL1.Range("A" & Rows.Count).End(xlUp).Row).Value
End With

Merci d'avance pour votre aide

Cordialement

Bonjour

Cela aurait été plus facile avec votre fichier mais vous pouvez toujours essayer comme ceci

Dim i As Integer
Set Fl1 = ThisWorkbook.Sheets("CATEGORY")
With cboCategory
    .Clear
    .ColumnCount = 5
    .ColumnWidths = "0;200;0;0;0"
    For i = 2 To Fl1.Range("A2:E" & Fl1.Range("A" & Rows.Count).End(xlUp).Row)
        If UCase(Fl1.Range("E" & i)) = "OUI" Then
            .AddItem Fl1.Range("A" & i & ":E" & i).Value
        End With
    Next i
End With

Si Ok et terminé pensez à

Crdlt

Bonsoir Dan,

Merci beaucoup pour votre proposition. Malheureusement elle ne fonctionne pas à deux niveaux :

1. Erreur d'exécution "13- Type de données incompatible sur l'instruction For suivante :

For i = 2 To Fl1.Range("A2:E" & Fl1.Range("A" & Rows.Count).End(xlUp).Row)

J'ai essayé cette instruction qui marche mais un autre problème survient (cf. point 2):

For i = 2 To  Fl1.Range("A" & Rows.Count).End(xlUp).Row

2. L'instruction AddItem ne marche pas non plu

.AddItem Fl1.Range("A" & i & ":E" & i).Value

En principe, pour un comboBox AddItem attend 2 arguments : l'indice et la valeur. Ici on essaye d'ajouter une ligne entière du tableau dans la liste. Est-ce que VBA sait ajouter dans une liste de 5 colonnes une rangée de 5 cellules?

J'ajoute un exemple de fichier. peut-être que cela pourrait aider mieux.
Pour tester il faut mettre la boucle For en commentaire et décommenter la ligne ci-dessous qui affiche le contenu de toute la table.

.List = FL1.Range("A2:E" & FL1.Range("A" & Rows.Count).End(xlUp).Row).Value

bonjour cheickna, Dan,

Private Sub UserForm_Initialize()
     Dim i     As Integer, c

     Application.ScreenUpdating = False
     With Range("TAB_CATEGORY").ListObject.Range     'vos données
          .AutoFilter
          .AutoFilter 5, "oui"
          i = .Columns(1).SpecialCells(xlVisible).Count     'nombre de lignes "Oui" (inclusif entête)

          Set c = .Cells(.Rows.Count + 10, 1).Resize(i - 1, 5)     'plage auxiliaire 10 lignes en dessous le tableau
          .Offset(1).Copy c                  'copier données visible
          .AutoFilter
     End With

     With cboCategory
          .Clear
          .ColumnCount = 5
          .ColumnWidths = "0;200;0;0;0"
          If i > 1 Then .List = c.Value      'données filtrés (=visible)
          c.ClearContents                    'RAZ plage auxiliaire
     End With
     Application.ScreenUpdating = True

End Sub

Bonjour Bart,

Merci beaucoup pour ce code qui marche très bien. C'est effectivement ce que je voulais mais je vous avoue que j'ai un peu de mal à comprendre le fonctionnement. Je suppose que l'instruction .AutoFilter 5, "OUI" filtre les données pour ne garder que les lignes avec une valeur "OUI" dans la 5ème colonne. En revanche, pour calculer le nombre de lignes "Oui" vous faites référence à la 1ère colonne (.Columns(1)). Ensuite, vous l'obtention de la valeur de c en utilisant 10 plages dit auxiliaires en dessous dans le tableau alors que ce tableau ne contient que 5 lignes (combiné à la méthode Resize (i-1,5) me complique encore d'avantage la compréhension. Pourquoi (i-1)? est-ce parce qu'il y a un "NON" dans le tableau?
Bref! Si vous pouvez m'expliquer une peu le code, cela facilitera la maintenance de ce code si je venais à y toucher plusieurs années après ou si quelqu'un d'autre devait le toucher.
Merci d'avance.

Cordialement

re,

On utilise le "range" du tableau structuré "TAB_CATEGORY" (>>>With Range("TAB_CATEGORY").ListObject.Range), donc c'est tout = l'entête+les données. La ligne suivante, on désactive tous les filtres actives et réactive un filtre sur les valeurs "Oui" de la 5ième colonne "Active". Tout ce qui est visible pour le moment, c'est ce que vous voulez dans votre combobox (sauf l'entête). La plus facile méthode de récupérer ces données, c'est les copier tout ce qu'il est visible (plusieurs plages non-contiguës) et coller comme une plage contiguë quelle part temporaire, par exemple, soit une nouvelle feuille qu'on supprime directement, soit une feuille invisible qu'on utilise que pour cela, soit comme ici, on utilise une plage en dessous (ici 10 lignes) le tableau (s'il n'y a pas d'autre choses là !!!). Puis on désactive de nouveau le filtre. Maintenant on n'a qu'à utiliser cette plage dans votre combobox et dès qu'on a fait cela, vider cette plage.

La macro ici dessous contient un message et s'arrête à ce moment, donc vous pouvez voir cette plage.

PS. Si vous voulez un autre endroit comme plage temporaire, vous devez dire où exactement

PS2. le "i" c'est le nombre de cellules "visibles" de la première colonne "ID" (avec entête, donc il faut soustraire 1), ou une autre colonne, donc i-1 = nombre de lignes de la plage "c"

Private Sub UserForm_Initialize()
     Dim i     As Integer, c

     'Application.ScreenUpdating = False
     With Range("TAB_CATEGORY").ListObject.Range     'vos données
          .AutoFilter
          .AutoFilter 5, "oui"
          i = .Columns(1).SpecialCells(xlVisible).Count     'nombre de lignes "Oui" (inclusif entête)

          Set c = .Cells(.Rows.Count + 10, 1).Resize(i - 1, 5)     'plage auxiliaire 10 lignes en dessous le tableau
          .Offset(1).Copy c                  'copier données visible
          MsgBox "on a copié la plage visible (+1 ligne supplémentaire en dessous le tableau mais cela n'est pa grave)" & vbLf & " de " & .Offset(1).Address(0, 0) & " vers " & c.Address(0, 0)
          .AutoFilter
     End With

     With cboCategory
          .Clear
          .ColumnCount = 5
          .ColumnWidths = "0;200;0;0;0"
          If i > 1 Then .List = c.Value      'données filtrés (=visible)
          c.ClearContents                    'RAZ plage auxiliaire
     End With
     Application.ScreenUpdating = True

End Sub

Bonjour cheickna , BsAlv ,

Une autre méthode (soustractive et non additive ) :

1. Filtrez le tableau (sur un ou plusieurs champs quelconques )
2. cliquez sur le bouton Hop !

Le code :

Private Sub UserForm_Initialize()
Dim n&
   With Sheets("CATEGORY").ListObjects("TAB_CATEGORY").ListColumns(1).DataBodyRange
      cboCategory.List = .Value
      For n = .Count To 1 Step -1
         If .Cells(n, 1).EntireRow.Hidden Then cboCategory.RemoveItem n - 1
      Next n
   End With
End Sub

@mafraise, bonne solution et elle est moins envahissante

Bonjour BsAlv et mafraise

Je vous remercie beaucoup (BsAlv : pour les explications détaillées du code qui m'ont apprises quelque chose de nouveau, mafraise : pour la seconde solution).
En fait la solution que j'avais utilisée avant de poster la question ressemble à celle de mafraise à la différence près que ma boucle For est en dehors de l'instruction With. Mais je me suis posé la question de l'optimisation du code afin d'éviter les deux blocs de code que j'avais utilisés. Voici ce code pour information

    Set FL1 = ThisWorkbook.Sheets("CATEGORY")
    With cboCategory
        .Clear
        .ColumnCount = 5
        .ColumnWidths = "0;200;0;0;0"
        .List = FL1.Range("A2:E" & FL1.Range("A" & Rows.Count).End(xlUp).Row).Value  'Toutes les données sont copiées dans la ComboBox sans filtre
    End With
    'On va afficher uniquement les catégories "Oui" donc on retire de la comboBox tout ce qui est "non"
    For i = cboCategory.ListCount - 1 To 0 Step -1
        If cboCategory.List(i, 4) = "NON" Then
           cboCategory.RemoveItem (i)
        End If
    Next i

Encore une fois merci beaucoup pour le temps que vous avez pris à répondre à ma question.

Cordialement
Cheickna

re,

si c'était pour optimaliser, vous pouvez encore faire ceci

Set FL1 = ThisWorkbook.Sheets("CATEGORY")
With cboCategory
     .Clear
     .ColumnCount = 5
     .ColumnWidths = "0;200;0;0;0"
     .List = FL1.Range("A2:E" & FL1.Range("A" & Rows.Count).End(xlUp).Row).Value     'Toutes les données sont copiées dans la ComboBox sans filtre

     'On va afficher uniquement les catégories "Oui" donc on retire de la comboBox tout ce qui est "non"
     For i = .ListCount - 1 To 0 Step -1
          If .List(i, 4) = "NON" Then cboCategory.RemoveItem (i)
     Next i
End With

Bonsoir Bart,

Oui c'est effectivement ce que j'ai finalement fait après avoir vu la solution proposée par mafraise. Mais dans la démarche initiale je voulais éviter la boucle For car je pensais qu'avec des propriétés telles que Filter il y avait une possibilité de filtrer des données (via des critères) directement dans le With.

Merci et bonne soirée

Bonjour,

Désolé de ne pas vous avoir répondu plus tôt mais je vois que d'autres se sont chargés de s'en occuper
Donc inutile que je vous donne réponse à votre message https://forum.excel-pratique.com/s/goto/1177458

Au vu de votre fichier et du tableau structuré, il fallait plutôt utiliser un code plus simple et je me pose la question du pourquoi remplir une combo plutôt qu'une listbox sachant que vous voulez voir toutes les colonnes de votre tableau. Bizarre...

Bonne journée

Bonsoir Dan,
Merci pour votre message. Tout est une question de contexte. Le ComboBox est plus adapaté dans mon projet qu'une ListBox que j'utilise par ailleurs dans d'autres parties du programme. La combox affiche effectivement tout le tableau mais une seule colonne est visible qui permet à l'utilisateur de faire un choix dans cette liste. Ce choix conditionne aussi un autre affichage dans un autre (sous) ComboBox puis les résultats de la recherche sont affichés dans ce contexte. Les autres colonnes masquées me permettent d'effectuer d'autres traitements comme les modifications, suppressions, etc. par comparaison de valeurs. Le contexte nécessite donc une ComboBox.

Cordialement