XL 2016 alimentation Combobox et liaison textbox très lente

Bulr6 · 24 Février 2021

Bonjour à tous.
Je ne suis déjà pas très sur d'avoir bien expliqué mon problème. Veuilllez m'en excuser par avance.
Avant de poser une question j'essaie d'avancer le plus possible par moi même mais en grand débutant mais ce n'est pas forcément évident.

Mon problème :
J'ai dans un Userform un combobox (nom) que j'alimente avec toutes les occurrences présentes à partir de F2 (qui évolue) sur 4 feuilles différentes (Boulogne, calais, dunkerque, saint omer) et que je trie. Voici le code :

VB:

Sub UserForm_Initialize()

Set boul = Sheets("Boulogne")
Set cal = Sheets("Calais")
Set dunk = Sheets("Dunkerque")
Set so = Sheets("Saint-Omer")

For Each c In boul.Range("F2:F" & boul.[F65000].End(xlUp).Row)
  Me.Nom.AddItem c
Next c
For Each c In cal.Range("F2:F" & cal.[F65000].End(xlUp).Row)
  Me.Nom.AddItem c
Next c
For Each c In dunk.Range("F2:F" & dunk.[F65000].End(xlUp).Row)
  Me.Nom.AddItem c
Next c
For Each c In so.Range("F2:F" & so.[F65000].End(xlUp).Row)
  Me.Nom.AddItem c
Next c

 With Nom
        For i = 0 To .ListCount - 1
            For j = 0 To .ListCount - 1
                If .List(i) < .List(j) Then
                    StrTemp = .List(i)
                    .List(i) = .List(j)
                    .List(j) = StrTemp
                End If
            Next j
        Next i
    End With

End Sub

Jusque là cela va encore mais ensuite une fois sélectionnée une valeur dans la combobox (nom) je lui demande d'alimenter plusieurs textbox dont les valeurs correspondent à la valeur de la combobox.... Et c'est là que ça se complique c'est très trsè long alors que je n'en suis qu'à un critère :
Exemple avec un textbox "modele" présent en colonne K de chacune de ces feuilles

Code:

Sub Nom_Change()

 For j = 2 To 65536
        If Sheets("Boulogne").Cells(j, 6) = Nom Then
           modele = Sheets("Boulogne").Cells(j, 11).Value
        ElseIf Sheets("Calais").Cells(j, 6) = Nom Then
           modele = Sheets("Calais").Cells(j, 11).Value
        ElseIf Sheets("Dunkerque").Cells(j, 6) = Nom Then
           modele = Sheets("Dunkerque").Cells(j, 11).Value
        ElseIf Sheets("Saint-Omer").Cells(j, 6) = Nom Then
           modele = Sheets("Saint-Omer").Cells(j, 11).Value
          
        End If
Next j

End Sub

ET je compte comme cela alimenter 5-6 textbox (matériel/fabricant/adressemail/site/date) ... ces textbox me servent par la suite de champ pour remplir des champs pour des mails automatique.

Merci d'avance à ceux qui pourront m'aider à trouver une solution plus fluide/rapide

JM27 · 24 Février 2021

bonjour
1/ si tu trouves la valeur , pourquoi ne pas mettre un exit for.
sinon tu boucles sur 65536 lignes ???

Sub Nom_Change()

For j = 2 To 65536
If Sheets("Boulogne").Cells(j, 6) = Nom Then
modele = Sheets("Boulogne").Cells(j, 11).Value
exit for
ElseIf Sheets("Calais").Cells(j, 6) = Nom Then
modele = Sheets("Calais").Cells(j, 11).Value
exit for
ElseIf Sheets("Dunkerque").Cells(j, 6) = Nom Then
modele = Sheets("Dunkerque").Cells(j, 11).Value
exit for
ElseIf Sheets("Saint-Omer").Cells(j, 6) = Nom Then
modele = Sheets("Saint-Omer").Cells(j, 11).Value
exit for

End If
Next j

End Sub

mais le mieux serait de ne pas faire de boucle
en utilisant la fonction match qui te donnera le numéro de la ligne
mettre une gestion d'erreur dans le cas ou la valeur nom ne se trouve pas dans la feuille testée

VB:

Sub FindFirst()
    MyVar = Application.WorksheetFunction _
        .Match(Nom, Sheets("Boulogne").Range("F1:F65536"), 0)
    MsgBox MyVar
End Sub

idem pour les autres feuilles

ChTi160 · 24 Février 2021

Bonsoir Bulr6
Bonsoir le Fil , le Forum
je vois que personne n'a de boule de cristal !
vous me direz le fichier joint n'est pas très explicit Lol
question quelle forme ont tes tableaux Source ?
il n'y a que deux Colonnes "F" et "K" ?
jean marie

ChTi160 · 24 Février 2021

Re
un fichier exemple de ce que j'ai compris Lol
jean marie

patricktoulon · 24 Février 2021

re
bonsoir
c'est lent ? et bien tout simplement parce que ce n'est pas la bonne méthode
quand on fait un listage dans listbox ou combo et que l'on tri dans un ordre croi.. ou decroi...
on perd la notion de correspondance de ligne
que faut il faire?
et bien on met une colonne cachée avec les indexs de lignes dans le controls
et le tri les déplace avec les colonnes voisine dans le control
ainsi on a l'index de ligne a dispo tout de suite sans avoir besoins de rechercher a nouveau l'index correspondant dans la feuille
voila a méditer c'est pas compliqué juste une question de bon sens

patricktoulon · 24 Février 2021

re
tiens je prends 5 minutes et voila le raisonnement pour le coup comme on a une 3 eme donnée (le bom de feuille différente on prend 2 colonnes masquées

VB:

Private Sub nom_Change()
    With nom
        If .ListIndex > -1 Then MsgBox "cet item se trouve dans " & .List(.ListIndex, 1) & "  à la ligne " & .List(.ListIndex, 2)
    End With
End Sub

Private Sub UserForm_Activate()
    Dim Boul, Cal, So, Dunk, c, strTemp0$, strTemp1$, strTemp2$
    Set Boul = Sheets("Boulogne")
    Set Cal = Sheets("Calais")
    Set Dunk = Sheets("Dunkerque")
    Set So = Sheets("Saint-Omer")
    With nom
        .ColumnCount = 3
        .ColumnWidths = "100;0;0"

        For Each c In Boul.Range("F2:F" & Boul.[F65000].End(xlUp).Row)
            .AddItem c: nom.List(nom.ListCount - 1, 1) = Boul.Name: nom.List(nom.ListCount - 1, 2) = c.Row
        Next c

        For Each c In Cal.Range("F2:F" & Cal.[F65000].End(xlUp).Row)
            .AddItem c: nom.List(nom.ListCount - 1, 1) = Cal.Name: nom.List(nom.ListCount - 1, 2) = c.Row
        Next c

        For Each c In Dunk.Range("F2:F" & Dunk.[F65000].End(xlUp).Row)
            .AddItem c: nom.List(nom.ListCount - 1, 1) = Dunk.Name: nom.List(nom.ListCount - 1, 2) = c.Row
        Next c

        For Each c In So.Range("F2:F" & So.[F65000].End(xlUp).Row)
            .AddItem c: nom.List(nom.ListCount - 1, 1) = So.Name: nom.List(nom.ListCount - 1, 2) = c.Row
        Next c

        For i = 0 To .ListCount - 1
            For j = 0 To .ListCount - 1
                If .List(i) < .List(j) Then
                    strTemp0 = .List(i, 0): strTemp1 = .List(i, 1): strTemp2 = .List(i, 2)
                    .List(i, 0) = .List(j, 0): .List(i, 1) = .List(j, 1): .List(i, 2) = .List(j, 2)
                    .List(j, 0) = strTemp0: .List(j, 1) = strTemp1: .List(j, 2) = strTemp2
                End If
            Next j
        Next i
    End With



End Sub

et voila la réponse est immédiate au change de la combo
j'avoue le tri dans la combo c'est moyen perso je l'aurais fait dans une variable tableau, mais bon digère ça et on verra

ChTi160 · 24 Février 2021

Re
Bonsoir Patrick
Je n'ai pas trouvé nécessaire de lister Les Colonnes de plusieurs feuilles si l'on doit ne sélectionner que dans une feuille.
Sans fichier pas évident de comprendre lol
Je n'ai vu aucune allusion au tri ou ligne source lol
Jean marie

patricktoulon · 24 Février 2021

ChTi160 à dit:
Re
Bonsoir Patrick
Je n'ai pas trouvé nécessaire de lister Les Colonnes de plusieurs feuilles si l'on doit ne sélectionner que dans une feuille.
Sans fichier pas évident de comprendre lol
Je n'ai vu aucune allusion au tri ou ligne source lol
Jean marie

Bonsoir ChTi160 tu a lu en diagonale alors

j'ai juste repris son code et fait en sorte que ça réponde a la demande

JM27 · 24 Février 2021

bonsoir
d'après ce qu'il a écrit , ce n'est pas le chargement et le tri qui le pénalise , c'est la recherche du nom dans les quatre feuilles.

patricktoulon · 24 Février 2021

bonsoir @JM27 avec ma méthode C'est un problème qui n'existe pas
puisque l'on a pas besoins de chercher
au change tu connais le nom de la feuille et la ligne ou se trouve l'item que tu viens de cliquer

ChTi160 · 24 Février 2021

re effectivement si on doit rechercher dans les Quatre feuilles Lol
j'avais rien compris excuses !
va falloir que je revienne lorsque le fichier exemple sera plus explicite Lol
jean marie

patricktoulon · 24 Février 2021

re
perso son code je le trouve explicite
il est évident que si il rempli une combo avec 4 colonnes de 4 feuilles différentes l'ors du click il faut identifier la feuille ou le retrouver et chercher dans les lignes
ou faire une recherche sur les 4 feuilles jusqu'à ce que l'item soit trouvé
hors ce que je propose c'est
1° ajouter dans la combo une colonne nomsheetset on y met le nom du sheets
2°ajouter dans la combo une colonne ligne on lui met le c.row dans la boucle
3° on masque les colonnes 1 et 2 de la combo
et cela pour les quatre boucles
il a désormais l'item en col 0 la feuille en col 1 et la ligne en col 2(dans la combo )
quand il filtre il déplace les lignes entières dans la combo

au click reste plus qu'a se servir de listindex et colonne 0 ou 1 ou 2 de la combo pour les données feuilles et lignes
en fait on cherche rien on a déjà
pas compliqué
en fait dans la combo il a ça

mais en masquant les colonnes et en mettant la largeur de combo a 1 colonne il a ça

et quand il change d'item (sélectionne)

ChTi160 · 24 Février 2021

Re
Merci Patrick
j'ai très bien compris tes explications dès #5

je pensais qu'il savait où chercher , il a des feuilles Ville (4 dans l'exemple) mais c'est pas par ce biais qu'il recherche mais via les X mots présent dans les x Feuille Lol
merci
jean marie

Bulr6 · 25 Février 2021

Bonjour à tous.
Pour commencer un grand merci à vous de vous être pencher sur ma question.
En effet, j'aurais pu mettre un fichier. Comme le question (je le pensais) était assez précise je ne souhaitais surcharger le post avec un fichier qui vous aurez selon moi pris davantage de temps.
Je suis content de mettre tromper!
@JM27 vos conseils m'ont tout de suite permis de grandement accélérer ma recherche. ça suffit amplement vu le peu de ligne amener à exister (environ 20/feuilles)
@Chti60 désolé de n'avoir pas été assez précis dans ma demande je vous ai fait perdre du temps, cela ne correspond pas vraiment
@patricktoulon depuis hier soir j'étudie votre code. Et même si la solution de JM27 j'essaie d'appliquer votre méthode afin de la comprendre. D'ou mon absence de réponse immédiate j'essaie de me documenter sur cette façon de faire. Un grand merci. Je serais surement plus long à finir mon fichier mais j'aurais appris quelque chose et ça c'est un grand plus

Je joins tout de même un fichier

Soit j'arrive à appliquer la méthode de patricktoulon et je le mettrais à jour et mettrais le sujet en résolu soit je n'y arrive pas et cela permettra de demander conseils ...mais j'y crois ;-)

ChTi160 · 25 Février 2021

Bonjour bulr6
j'espère que les données de ton fichier son anonymisées(pas de données confidentielles) Noms etc etc
jean marie

XL 2016 alimentation Combobox et liaison textbox très lente

Bulr6

XLDnaute Nouveau

JM27

XLDnaute Barbatruc

ChTi160

XLDnaute Barbatruc

ChTi160

XLDnaute Barbatruc

Pièces jointes

patricktoulon

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

Pièces jointes

ChTi160

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

JM27

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

ChTi160

XLDnaute Barbatruc

patricktoulon

XLDnaute Barbatruc

ChTi160

XLDnaute Barbatruc

Bulr6

XLDnaute Nouveau

Pièces jointes

ChTi160

XLDnaute Barbatruc

Discussions similaires