Moteur de recherche via textbox + mise en forme conditionnelle et Tableau structuré - Excel VBA

Version imprimable

06/10/2023, 18h19
Kalagane75

1 pièce(s) jointe(s)

Moteur de recherche via textbox + mise en forme conditionnelle et Tableau structuré - Excel VBA

Bonjour,

J'ai essayé plusieurs solutions mais je n'arrive pas à créer un moteur de recherche qui me permette de mettre en lumière les cellules de ma table contenant la valeur saisie.

Objectif :
J'ai un textbox1 qui permet à l'utilisateur d'indiquer l'élément de recherche. Par exemple le chiffre 15.
Alors toutes les cellules contenant 15 seront misent en lumière via une mefc de couleur bleu. Cette mefc doit être valable pour toute la table peu importe son format

Cela marche plutôt bien pour du texte, le problème sur lequel je m'arrache les cheveux : c'est la gestion des dates.
Si je reprend mon exemple :
Lorsque je tape 15, il me met en couleur la cellule 30/08/2019 car sa valeur est égale à 43707,4625115741 alors que je voudrais qu'il me sélectionne 15/01/2023 car cette date contient 15

J'espère avoir été claire, n'hésitez pas à me demander si besoin.

J'ai mis en copie le document reprenant la textbox et la table structurée.
Je vise une approche VBA pour mettre en application la mefc.

Si vous êtes plus calé en formule, pas de soucis, ça m'intéresse aussi car je traduirai la formule en VBA.

Merci d'avance pour votre lecture

Cordialement

Cordialement

Bonsoir, voici mon approche, je fais une recherche de texte, y compris sur les nombres et les dates. En effet, ce qui est affiché dans la cellule peut différer de la valeur réelle, notamment pour les dates.

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
Private Sub CommandButton1_Click()
    Dim recherche As String
    Dim cellule As Range
    Dim plage As Range
 
    ' Récupérer la valeur saisie dans le textbox (remplacez "TextBox1" par le nom de votre contrôle)
    recherche = TextBox1.Value
 
    ' Définir la plage utilisée dans la feuille active
    Set plage = ActiveSheet.UsedRange
 
    ' Parcourir chaque cellule dans la plage
    For Each cellule In plage
        ' Si la cellule contient le texte recherché
        If InStr(1, cellule.Text, recherche, vbTextCompare) > 0 Then
            ' Mettre en évidence la cellule en changeant sa couleur de fond (par exemple, en bleu)
            cellule.Interior.Color = RGB(0, 0, 255)
        End If
    Next cellule
 
    MsgBox "Traitement terminé!", vbInformation
 
End Sub

06/10/2023, 20h06
rolandtsf

=SIERREUR(CHERCHE("15";"'"&A1);0)>0

Bonjour,
Merci pour ce retour toutefois je voulais éviter les boucle et passer par une mefc car comme tu as pu le voir il y a des centaines de cellules.
Il faut aussi savoir, que vous avez la version épurée car dans la versions il y a 41 tableau de ce type avec autant de textbox (1 pour chaque table)
là où je m'en rapproche le plus c'est via une mefc qui recherche dans le contenu la valeur recherchée. Toutefois pour les colonnes à date ça ne marche pas :(
Citation:
Envoyé par Franc
Bonsoir, voici mon approche, je fais une recherche de texte, y compris sur les nombres et les dates. En effet, ce qui est affiché dans la cellule peut différer de la valeur réelle, notamment pour les dates.
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 Private Sub CommandButton1_Click() Dim recherche As String Dim cellule As Range Dim plage As Range ' Récupérer la valeur saisie dans le textbox (remplacez "TextBox1" par le nom de votre contrôle) recherche = TextBox1.Value ' Définir la plage utilisée dans la feuille active Set plage = ActiveSheet.UsedRange ' Parcourir chaque cellule dans la plage For Each cellule In plage ' Si la cellule contient le texte recherché If InStr(1, cellule.Text, recherche, vbTextCompare) > 0 Then ' Mettre en évidence la cellule en changeant sa couleur de fond (par exemple, en bleu) cellule.Interior.Color = RGB(0, 0, 255) End If Next cellule MsgBox "Traitement terminé!", vbInformation End Sub

07/10/2023, 09h46
Kalagane75

Citation:

Envoyé par rolandtsf

=SIERREUR(CHERCHE("15";"'"&A1);0)>0

Bonjour,
Merci pour ton intervention, mais ça n'a pas l'air de fonctionner.
Je veux bien le fichier avec la formule intégrée que je puisse la tester et l'intégrer à mon fichier d'origine.
Cordialement
07/10/2023, 11h12
Franc

Citation:

Envoyé par Kalagane75

Bonjour,
Merci pour ce retour toutefois je voulais éviter les boucle et passer par une mefc car comme tu as pu le voir il y a des centaines de cellules.
Il faut aussi savoir, que vous avez la version épurée car dans la versions il y a 41 tableau de ce type avec autant de textbox (1 pour chaque table)
là où je m'en rapproche le plus c'est via une mefc qui recherche dans le contenu la valeur recherchée. Toutefois pour les colonnes à date ça ne marche pas :(

Je ne comprends pas où est le problème entre une boucle et des centaines de cellules. J'ai fait un test sur ton tableau et j'obtiens le résultat en une fraction de seconde, de plus ma méthode fonctionne avec les dates.

Bonjour,s
Pour info, le code proposé par Franc, que je salue au passage, dans ce fil fonctionne parfaitement
Il faudrait juste prévoir un else dans la boucle car si on relance une recherche la mise en forme précédente est conservée et peut être choisir une couleur (jaune pâle par exemple) qui permette de mieux visualiser le texte mais ça c'est un détail.
Code:

1 2 3 4 If InStr(1, cellule.Text, recherche, vbTextCompare) > 0 Then ' Mettre en évidence la cellule en changeant sa couleur de fond (par exemple, en bleu) cellule.Interior.Color = RGB(0, 0, 255) End If
[EDIT]
Du fait de l'instruction Set plage = ActiveSheet.UsedRange, la plage de recherche englobe évidemment toutes les cellules utilisées de la feuille active y compris les titres et autres cellules dont on ne souhaiterait pas voir être mise en forme. Personnellement j'utilise toujours la propriété CurrentRegion avec une correction éventuelle avec les propriétés Offset et Resize ce qui implique évidemment de connaître la cellule débutant la table à traiter

Comme c'est un tableau structuré, on pourrait limiter la recherche à la propriété DataBodyRange du ListObject ou par le Range(Nomdelatable)

07/10/2023, 13h17
Kalagane75

Tu as raison et je t'en remercie. Ce qu'il y a est que dans la version complète mon fichier contient des mefc. Les mefc supplante la couleur de la cellule, c-a-d que la couleur obtenu via vba (bleu par exemple) ne se verra pas car une mefc indiquera la couleur rouge par exemple... d'où ma recherche d'une mefc qu'elle soit apporté via vba ou formule d'ailleurs.
07/10/2023, 14h05
Philippe Tulliez

Bonjour,
Effectivement, la MFC est prioritaire par rapport à la mise en forme faite manuellement ou par VBA.
Le problème c'est la recherche d'un texte dans une date. La fonction CHERCHE ou TROUVE proposée en formule par rolandtsf ne fonctionnera pas non plus pour une date ou plutôt mettra en évidence une cellule qui contient 15 dans la partie décimale de l'heure.
Dans la mise en forme conditionnelle on devrait pouvoir faire une distinction entre les dates et les autres types mais malheureusement en Excel n'a pas de fonction native permettant de vérifier si une cellule contient une date.Même la fonction CELLULE avec argument "type" ou "format" a un comportement qui n'est pas fiable
Il faut donc faire une mise en forme conditionnelle distincte entre les colonnes contenant des dates et les autres. Je ne vois pas d'autres voies tout au moins au niveau de mes connaissances
Si de plus ll y a déjà des mfc dans le tableau, il faudra certainement gérer les priorités et choix des formats pour éviter les conflits

[EDIT]
Je ne connais pas la finalité de votre demande car faire une recherche d'une sous-chaines dans une chaîne de caractères me semble évidente je ne saisi pas trop le mélange Texte, Nombre et date.
Une piste en utilisant Power Query qui pourrait exporter tous les éléments contenant cette chaine après avoir converti les dates en texte et là appliquer une mise en forme conditionnelle
Ce sera plus simple et plus rapide à mettre en place, je crois.
07/10/2023, 15h07
Kalagane75

Effectivement. "Il faut donc faire une mise en forme conditionnelle distincte entre les colonnes contenant des dates et les autre". J'ai la possibilité de réaliser via un code vba (union et databodyrange...) de définir 2 champs d'action. La première reprendra les cellules contenant du texte et nombre, dans ce cas là, la mefc est simple (si la valeur dans textbox est contenu dans la cellule alors elle se colorie en bleu) et dans le second seulement les colonnes avec les dates. C'est dans cette seconde partie que je n'arrive pas à trouver la formule Excel de la mefc ou la version vba (.formatconditions.add...)
07/10/2023, 15h17
Philippe Tulliez

Bonjour,

Citation:

C'est dans cette seconde partie que je n'arrive pas à trouver la formule Excel de la mefc ou la version vba (.formatconditions.add...)

Une proposition : En vérifiant avec un OU si l'une des parties de la date est égale à la valeur cherchée
Exemple ou 7 est la valeur cherchée (à remplacer par la valeur contenue dans une cellule ou un nom et B3 la cellule testée

Code:

=OU((ANNEE(B3)-2000)=7;MOIS(B3)=7;JOUR(B3)=7)
07/10/2023, 15h21
Kalagane75

Je m'en vais essayer cette solution et te ferais un retour dans l'heure. En tout cas, l'approche est intéressante, j'y suis dessus depuis un moment et je n'y avais pas pensé. Merci
07/10/2023, 15h25
Philippe Tulliez

Bonjour,
Autre solution en gardant la notion de texte où $D$1 contient le texte à chercher

Code:

=NON(ESTERR(CHERCHE($D$1;TEXTE(B3;"aammjj"))))

ou si l'on veut absolument séparer chacune des parties de la date

Code:

=NON(ESTERR(CHERCHE($D$1;TEXTE(B3;"aa\/mm\/jj"))))

Sans omettre qu'en VBA il faut traduire la fonction en anglais ET le format
07/10/2023, 16h32
Kalagane75

Bonjour,
Tu as réussi, merci bcp.
La formule : =NON(ESTERR(CHERCHE($D$1;TEXTE(B3;"aammjj")))) fonctionne bien en remplaçant $D$1 par $F$5 et B3 par P8 pour coller à mon tableau.
Pour ma culture général, le fait d'utiliser "aa\/mm\/jj", en quoi cela change la donne ?
Merci encore
07/10/2023, 16h50
Philippe Tulliez

Bonjour,

Citation:

Pour ma culture général, le fait d'utiliser "aa\/mm\/jj", en quoi cela change la donne ?

Le caractère \ est un caractère d'échappement qui permet d'insérer le caractère / dans le format de date. En VBA, le caractère / dans une chaîne de formatage de date est considéré comme un séparateur de date qui sera remplacé par le séparateur de date défini dans les paramètres régionaux de l'ordinateur. Si l'on veut s'assurer que le séparateur est toujours /, peu importe les paramètres régionaux, on peut utiliser le caractère d'échappement \.

Ce n'est sans doute pas indispensable dans ce cas précis mais cela ne nuit pas non plus