Pierre Fauconnier

Salut.

Continuons l'exploration des possibilités offertes par les nouvelles fonctions Excel 365.

Aujourd'hui, nous souhaitons filtrer une listbox au fur et à mesure de la saisie dans un textbox. L'idée est bien entendu de réduire la liste au fur et à mesure de la saisie, et donc de permettre une "saisie intuitive".




Avec Excel 365, c'est très simple, puisque cette version expose les fonctions TRIER et FILTRE.

Voici un userform muni de deux contrôles, tboCapture pour la saisie intuitive, et lboContacts qui contiendra la liste des contacts qui correspondent à la saisie. L'exemple exposé ici permet de filtrer la liste sur les prénoms qui contiennent la saisie, où que cette saisie se trouve dans le prénom.

Filtre renvoie une plage dynamique dans Excel et un array dans VBA. Voici l'exemple d'une plage dynamique renvoyée par Filtre



En utilisant cette formule dans Evaluate, on peut récupérer le tableau correspondant.


A l'initialisation du userform, on remplira la liste avec le tableau complet trié. Evaluate permet de restituer la valeur évaluée de l'argument passé. Si l'on passe une formule à Evaluate, cette dernière renvoie donc le résultat de la formule.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
Private Sub UserForm_Initialize()
  lboContacts.List = Evaluate("sort(tableau1[Prénom] & "" "" & tableau1[Nom],1)")
End Sub

Sur l'évènement Change de tboCapture, on utilisera Evaluate pour recevoir l'array trié grâce à la formule évaluée. il faudra simplement tester que la saisie renvoie bien un array. Dans le cas contraire, c'est-à-dire lorsque la saisie ne correspond à aucun prénom, on videra simplement la liste.

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
Private Sub tboCapture_Change()
  Dim l
 
  l = Evaluate("SORT(FILTER(Tableau1[Prénom] & "" "" & Tableau1[Nom],IFERROR(SEARCH(""" & tboCapture & """,Tableau1[Prénom])>0,FALSE)))")
  If IsError(l) Then
    lboContacts.Clear
  Else
    lboContacts.List = l
  End If
End Sub

Et voilà. Avec Excel 365, on se simplifie la vie en Excel, mais on se simplifie également la vie en VBA.

Variante: Réduire la liste à ce qui commence par la saisie


Il suffit, pour cette variante, de modifier la formule utilisée dans l'évènement Change de tboCapture

Code vba :

Sélectionner tout - Visualiser dans une fenêtre à part

l = Evaluate("SORT(FILTER(Tableau1[Prénom] & "" "" & Tableau1[Nom],LEFT(Tableau1[Prénom],LEN(D1))=D1))")

Etes-vous séduit par cette approche de code?
Travaillez-vous avec Excel 365? Si oui, connaissez-vous ces nouvelles fonctions?