XL 2016 alimentation Combobox et liaison textbox très lente

[Bulr6]

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 :

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

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]

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

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

idem pour les autres feuilles

 

[ChTi160]

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]

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

 

[patricktoulon]

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]

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

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]

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]

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]

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]

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]

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]

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]

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]

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]

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