Chargement de plus en plus lent d'un userform

Bonjour tous,

Dans le cadre d'une application excel, j'ai besoin à plusieurs endroits de charger un userform qui m'affiche les clients "actifs".

J'ai remarqué un très grand ralentissement dans le chargement de cet userform passant de 2/3 secondes à maintenant plus de 20 secondes.

J'appele cet userform par la fonction Call

Quelqu'un aurait-il une idée?

Amicalement

Joseph

Salut ke forum

Joseph, sans voir le traitement de ton code dur de te donner des explications.

As-tu plus de données à traiter ? Si oui une optimisation de tes codes seraient une

première solution.

A te relire

Mytå

Bonjour à tous,

Bonjour Mytå,

Joseph, sans voir le traitement de ton code dur de te donner des explications.

As-tu plus de données à traiter ? Si oui une optimisation de tes codes seraient une

première solution.

Je joins le code actuel avec des Select.

Je l'avais entièrement refait sans Select, mais celà n'apportait aucune amélioration.

Je n'aie pas plus de données à traiter, mais j'ai créé plusieurs Userform qui font appel au module qui contient le code, et c'est là que j'ai remarqué un très net ralentissement.

Sub LDProtegesActifs()
Dim compteur As Long
Dim compteur1 As Long
Dim nblignes As Variant
'CETTE PROCEDURE AFFICHE LA LISTE DEROULANTE CONCERNANT UNIQUEMENT LES PROTEGES ACTIFS (LDProtegesActifs)
'Effacement et initialisation des données de la feuille "Actifs"
    Sheets("Actifs").Select
    Sheets("Actifs").Unprotect ("ElPasoTx67110")
    Range("A6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("B6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("C6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("D6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
    Range("E6").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.ClearContents
'Selection et tri par date de sortie de la feuille ("Proteges")
    Sheets("Proteges").Select
    Sheets("Proteges").Unprotect ("ElPasoTx67110")
    'Range("A5").Select
    Range("A6:V405").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort key1:=Range("Q6"), order1:=xlAscending, key2:=Range("A6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
'Détermination du nombre de lignes dans la feuille ("Proteges")
    nblignes = Range("A6").End(xlDown).Address
    'nblignes = Cells(Rows.Count, 1).End(xlUp).Address
    nblignes = Range(nblignes).Row
    nblignes = nblignes - 5

    Range("A6").Select
    compteur = 0
    compteur1 = 6

'Début de la boucle
    Do
    compteur = compteur + 1
    If ActiveCell.Value <> 0 Then
        If ActiveCell.Offset(0, 15).Value = Empty Then
        nuprotege = ActiveCell.Value
        intprotege = ActiveCell.Offset(0, 1).Value
        noprotege = ActiveCell.Offset(0, 2).Value
        prprotege = ActiveCell.Offset(0, 3).Value
        Sheets("Actifs").Select
        Range("A5").Select
            If Range("A6").Value = Empty Then
            Range("A6").Select
            Range("A6").Value = nuprotege
            Range("B6").Value = intprotege
            Range("C6").Value = noprotege
            Range("D6").Value = prprotege
            Range("E6").Value = noprotege & " " & prprotege
            Else
            compteur1 = compteur1 + 1
            Range(("A") & (compteur1)).Value = nuprotege
            Range(("B") & (compteur1)).Value = intprotege
            Range(("C") & (compteur1)).Value = noprotege
            Range(("D") & (compteur1)).Value = prprotege
            Range(("E") & (compteur1)).Value = noprotege & " " & prprotege
            End If
        End If
    End If
    Sheets("Proteges").Select
    ActiveCell.Offset(1, 0).Select
    Loop Until compteur = nblignes
'Tri de la feuille("Proteges") par N° R.G.
    Sheets("Proteges").Select
    'Range("A5").Select
    Range("A6:V405").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
    Range("A6").Select
'Tri de la feuille("Actifs") par nom puis prénom
    Sheets("Actifs").Select
    'Range("A5").Select
    Range("A5:E405").Select
    'Range(Selection, Selection.End(xlDown)).Select
    'Range(Selection, Selection.End(xlToRight)).Select
    Selection.Sort key1:=Range("C6"), order1:=xlAscending, key2:=Range("D6"), order2:=xlAscending, Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
    Range("A6").Select

End Sub

J'ai encore laissé certaines modifications en commentaire (précédé par ')

J'ai également refait le tour de tous les objets, feuilles et modules pour voir si je n'utilisais pas les mêmes variables autre part, j'ai mis ce code dans un autre module, mais rien n'y fait.

Merci de t'intéresser à ma question.

Amicalement

Joseph

Bonjour,

Oulàlà, normal que cela rame là

En vitesse voici déjà une modification du début de ta macro :

Sub LDProtegesActifs()
Dim compteur As Long
Dim compteur1 As Long
Dim nblignes As Variant

'Effacement et initialisation des données de la feuille "Actifs"
With Sheets("Actifs")
 .Unprotect ("ElPasoTx67110")
 .Range("A6:E65536").ClearContents
End With
With Sheets("Proteges")
 .Select
 .Unprotect ("ElPasoTx67110")
 .Range("A6:V405").Sort key1:=Range("Q6"), order1:=xlAscending, key2:=Range("A6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
End With
'Détermination du nombre de lignes dans la feuille ("Proteges")

ensuite dans les 3 instructions suivantes "nlignes ...", tu cherches à faire quoi en fait ??

A te relire

Dan

Bonjour Dan,

Oulàlà, normal que cela rame là

et ce n'est pas peu dire...

J'ai déjà appliqué tes modifications, et je gagne déjà environ 5 à 6 secondes pour le chargement de la liste des "clients" actifs.

'Détermination du nombre de lignes dans la feuille ("Proteges")

ensuite dans les 3 instructions suivantes "nlignes ...", tu cherches à faire quoi en fait ??

Ceci est en fait une portion de code trouvée sur internet il y a quelques années (eh oui, je ne connaissais pas encore excel-pratique!) et que j'avais laissé tel quel ne sachant pas très bien comment celà fonctionnait, mais comme çà fonctionne, ma foi, ben j'ai continué à l'utiliser.

Je cherche à connaître le nombre d'enregistrements de la feuille ("Proteges") puis pour chaque ligne dans cette feuille rechercher les enregistrements correspondants au critère de la colonne 15.

Le ActiveCell.Offset, j'ai trouvé ça dans un livre sur excel.

Il s'agit en fait de regarder dans la feuille ("Proteges") selon le critère de la colonne 15 (s'il y a une date de sortie ou non) et s'il n'y a pas de date de sortie, copier le n°, intitulé, nom et prénom dans la feuille ("Actifs").

Note toutefois que je n'ai rien contre (je dirais même que j'ai tout pour) une amélioration de ce code !!!!

Dans l'attente de ta réponse

Amicalement

Joseph

Oups...

J'oubliais : MERCI.

Voilà ce que c'est que d'envoyer sans se relire auparavant.

Joseph

re,

Dans ton code, je ne vois pas à quoi sert le "nblignes", tu ne l'utilises pas ailleurs dans ton code.

Il s'agit en fait de regarder dans la feuille ("Proteges") selon le critère de la colonne 15 (s'il y a une date de sortie ou non) et s'il n'y a pas de date de sortie, copier le n°, intitulé, nom et prénom dans la feuille ("Actifs").

En gros, ce que je comprends c'est que tu veux voir en colonne O depuis la cellule O6 s'il y a une date ou pas et selon ce choix transférer des infos dans la feuille Actifs. Tu confirmes ?

A te relire

Dan

re,

Dans ton code, je ne vois pas à quoi sert le "nblignes", tu ne l'utilises pas ailleurs dans ton code.

Comme dit, c'est librement interprèté de ma part à partir d'un code pompé sur internet.

La variable "nblignes" sert à stocker le nombre de lignes de la feuille ("Proteges")La variable "compteur1 est initialisée à 6 parce que avec le titre et les entêtes, la première ligne comprenant un enregistrement et la ligne 6, et je boucle jusqu'à ce que compteur1 est égal à nblignes.

Sinon,

En gros, ce que je comprends c'est que tu veux voir en colonne O depuis la cellule O6 s'il y a une date ou pas et selon ce choix transférer des infos dans la feuille Actifs. Tu confirmes ?

tu as totalement résumé la situation.

Si dans la feuille ("Proteges") il y a une date en colonne O et ceci de la cellule O6 jusqu'à la dernière ligne pour chaque ligne s'il n'y a pas de date (en fait si le "client" est toujours actif), je veux tranférer son numéro, sa civilité, ses noms et prénom dans la feuille ("Actifs").

Amicalement

Joseph.

re,

essaie ce code :

Sub LDProtegesActifs()
Dim plage As Range, cel As Range
Dim dlg As Integer
'Effacement et initialisation des données de la feuille "Actifs"
With Sheets("Actifs")
    .Unprotect ("ElPasoTx67110")
    .Range("A6:E65536").ClearContents
End With
With Sheets("Proteges")
    .Select
    .Unprotect ("ElPasoTx67110")
    .Range("A6:V405").Sort _
        key1:=Range("Q6"), order1:=xlAscending, _
        key2:=Range("A6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
End With
With Sheets("Proteges")
Set plage = .Range("O6:O" & Range("O65536").End(xlUp).Row)
dlg = Sheets("Actifs").Range("A65536").End(xlUp).Row
For Each cel In plage
If IsEmpty(cel) Then
    .Range("A" & cel.Row & ":D" & cel.Row).Copy Sheets("Actifs").Range("A" & dlg + 1)
    Sheets("Actifs").Range("E" & dlg + 1) = .Range("A" & cel.Row) & " " & .Range("D" & cel.Row)
dlg = dlg + 1
End If
Next
End With
With Sheets("Actifs")
    .Select
    .Range("A5:E405").Sort _
        key1:=Range("C6"), order1:=xlAscending, _
        key2:=Range("D6"), order2:=xlAscending, Header:=xlGuess, ordercustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
End With
End Sub

Je ne vois pas trop bien l'intérêt du tri avant exécution.

Le tri entre les cellules A5 et A405 dans la feuille Actifs, me parait aussi bizarre. Pourquoi 405 ?

A te relire

Dan

Bonsoir Dan,

Tout d'abord, merci pour le mal que tu te donnes.

Je ne vois pas trop bien l'intérêt du tri avant exécution.

Le tri entre les cellules A5 et A405 dans la feuille Actifs, me parait aussi bizarre. Pourquoi 405 ?

En fait, j'ai déterminé le nombre maximal de "Proteges" à 400. Donc de la ligne 5 à 405 = 400 enregistrements possibles.

J'ai testé ta macro, mais le fichier "Actifs" reste vide et ne se remplit pas.

Je me retrouve donc en l'occurence avec un userform vide.

Pourrais-je encore abuser et te demander de revoir?

Je vais essayer de mon côté de comprendre ta macro et voir où ça peut coincer.

Amicalement

Joseph.

PS : je suis de nouveau demain en journée complète au boulot (de 3h à 17h environ)

Je ne pourrais peut-être pas te répondre demain, mais je suis impatient de te relire!

A bientôt.

Bonsoir,

As-tu vraiment besoin d'un UseForm ?

Je t'avais proposé à l'époque une solution qui marchait, le la remet ici:

https://www.excel-pratique.com/~files/doc2/62ERhJoseph67_2.xls

à toi de voir !

Amicalement

Claude.

Rebonsoir Dan,

Voici un fichier simplifié, avec un userform (EP4) et ta macro en module 1

Ce que je souhaite, c'est que dans la liste déroulante s'affiche le nom et prénom du protégé, ceci par ordre alphabétique; et seulement pour ceux qui ne sont pas définitement sortis (colonneP).

Je pense que cela sera plus compréhensible avec ce bout de fichier.

Mais maintenant, dodo...... Le réveil sonne à 2h!

Merci pour tout.

Amicalement

Joseph

https://www.excel-pratique.com/~files/doc2/JosephProtegesetActifs.xls

Bonsoir Claude,

Je suis vraiment content de t'avoir à nouveau en ligne.

As-tu vraiment besoin d'un UseForm ?

Je t'avais proposé à l'époque une solution qui marchait, le la remet ici

Oui, j'en ai vraiment besoin.

En outre, et ce n'est pas très glorifiant, mais j'ai vainement essayé de comprendre ta solution et il me faut admettre que je n'ai pas encore le niveau requis.

Je dis bien pas encore, car il parait que c'est en forgeant qu'on devient forgeron!!!

Encore merci Claude

Amicalement

Joseph.

re,

J'ai testé ta macro, mais le fichier "Actifs" reste vide et ne se remplit pas.

Non, la feuille "actifs" se remplit bien.

Place toi sur la feuille Protège puis exécute la macro Sub LDProtegesActifs() sans t'occuper de ton USF.

Je me retrouve donc en l'occurence avec un userform vide.

normal si tu démarres depuis le code Private Sub UserForm_activate() se trouvant dans l'USF. Le code n'est pas correct.

Ce que tu veux c'est qu'une fois placé dans la feuille "actifs", l'USF apparait avec les noms ?

Est-ce la feuille "actifs" sert à autre chose que afficher les noms dans l'USF ?

A te relire

Dan

edit Dan : Dans l'USF remplace le code Private Sub UserForm_activate() par les deux ci-dessous :

Private Sub Annuler_Click()
Unload EP4
End Sub

Private Sub UserForm_Initialize()
'Macro Dan pour Joseph67 21/10/09
Dim i As Integer
LDProtegesActifs
For i = 6 To Worksheets("Actifs").Range("E65536").End(xlUp).Row
    Choixprotege.AddItem Worksheets("Actifs").Cells(i, 5)
Next i
End Sub

Ensuite exécute le code Private Sub UserForm_Initialize().

A te relire

Dan

Bonjour Dan,

Je suis tout exprès rentré à la maison pendant la pause pour voir s'il y avait du nouveau, et je t'en remercie.

J'ai essayé tes modifications dans le fichier test que je t'avais envoyé.

C'est excellent. Le temps pour l'USF de se remplir est divisé par 4!!!!

Ce que tu veux c'est qu'une fois placé dans la feuille "actifs", l'USF apparait avec les noms ?

Ce que je souhaite c'est avoir le nom ET le prénom des actifs, le prénom me servant à les différencier si j'ai deux fois le même nom.

L'USF me sert à sélectionner l'actif grâce à Index (chaque protégé ayant un numéro unique (n° RG)) sous lequel j'enregistre leur décompte de gestion. (Un classeur par an et par actif)

Ci dessous le début du code de l'USF en cas de validation.

Private Sub Valider_Click()
 'Détermination du protégé choisi
Index = Choixprotege.ListIndex
numindex = Index
Choixprotege = Choixprotege.List(Index)
numeropro = Worksheets("Actifs").Range(("A") & (6 + numindex))
EP4.Hide

Choixprotege étant le nom que j'ai donné à la partie de l'userform dans laquelle s'affichent les actifs.

Est-ce la feuille "actifs" sert à autre chose que afficher les noms dans l'USF ?

Non cette feuille ne sert qu'a ça.

Enfin pour répondre à ta question concernant le tri de la feuille ("Proteges"), dans chaque classeur de décompte de gestion, je recherche des informations qui se trouvent dans le classeur qui contient la feuille("Proteges") comme par exemple la date d'entrée, le type de mesure, etc. grâce a des formules du type :

 =RECHERCHE(A70;[Directeur.xlsm]Proteges!$A$6:$A$405;[Directeur.xlsm]Proteges!$L$6:$L$405)

Il faut donc que la feuille ("Proteges") soit toujours triée par n°RG du plus petit au plus grand. (C'est en tout cas ce que j'ai lu dans mes livres).

Voilà, il faut que j'y retourne. Je ne manquerais pas de regarder si tu as envoyé une réponse dès mon retour ce soir

Merci beaucoup.

Joseph

Bonsoir à tous,

Bonsoir Dan,

En attendant de te lire, je joins le fichier modifié.

Quelle est la différence entre Hide et Unload. D'après le terme anglais hide doit cacher et unload décharger. Je suppose qu'il vaut mieux décharger afin d'éviter une surcharge de la mémoire. C'est en définitive sûrement ce qui m'est arrivé: en effet le ralentissement est devenu notoire après que j'ai créé plusieurs userform faisant appel à la liste des actifs.

Ce qui ne change rien à mon code lamentable.

J'ai essayé de faire un peu mieux grâce à votre aide (cf fichier joint)

Egalement quand faut-il faire appel à Sub Initialize et Sub activate. Je ne vois pas trop à mon niveau lequel choisir.

https://www.excel-pratique.com/~files/doc2/JosephProtegesetActifs2.xls

Merci

Amicalement

Joseph

PS je ne sais pas si Claude suis cette discussion : merci à toi également pour ton intervention.

Décidément, je trouve ce forum vraiment très sympathique, ainsi que les personnes que j'y retrouve régulièrement.

Bonsoir Joseph, à tous,

Je ne pourrais pas t'aider davantage sur ce fil,

car je n'y connait rien aux USF, il faudra que je m'y mette !

Amicalement

Claude.

Bonsoir à tous, bonsoir Dan,

Voilà le code que j'ai mis dans mon application "réelle".

Le temps d'affichage est passé de 25/28 secondes à environ 14/15

J'ai essayé de mettre ton code en initialize, mais rien ne se passe. L'USF s'affiche, et c'est tout.

J'ai donc mis ton code en Activate(), et cela fonctionne.

Voici le nouveau code pour LDProtegesActifs()

Sub LDProtegesActifs()

Dim LastRow As Long, i As Long, k As Byte, cpt As Long
'Tri de la feuille ("Proteges")par nom puis prénom pour affichage par ordre alphabétique
With Sheets("Proteges")
    .Select
    .Unprotect ("ElPasoTx67110")
    .Range("A6:V405").Sort _
        key1:=Range("C6"), order1:=xlAscending, _
        key2:=Range("D6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
End With
'Effacement et initialisation des données de la feuille "Actifs"
With Sheets("Actifs")
    .Unprotect ("ElPasoTx67110")
    .Range("A6:B405").ClearContents
End With
Application.ScreenUpdating = False
'Détermination de la dernière ligne dans la feuille ("Proteges")
    LastRow = Sheets("Proteges").Range("A6").End(xlDown).Row
'Initialisation du compteur
cpt = 6
'Depuis la dernière ligne jusqu'à la ligne 6
For i = LastRow To 6 Step -1
  'Si la condition est vrai Sheets("Proteges")
  If IsEmpty(Sheets("Proteges").Cells(i, 16)) Then
        'Insérer une ligne Sheets("Actifs")
        Sheets("Actifs").Rows(6).Insert
        'et copier le n°RG, + concaténer Nom et Prénom
       With Sheets("Actifs")
      .Cells(6, 1) = Sheets("Proteges").Cells(i, 1)
      .Cells(6, 2) = Sheets("Proteges").Cells(i, 3) & " " & Sheets("Proteges").Cells(i, 4)
       End With
  cpt = cpt + 1
  End If
Next
'Tri de la feuille ("Proteges")par n°RG
With Sheets("Proteges")
    .Select
    '.Unprotect ("ElPasoTx67110")
    .Range("A6:V405").Sort _
        key1:=Range("A6"), order1:=xlAscending, Header:=xlGuess, ordercustom:=1, _
        MatchCase:=False, Orientation:=xlTopToBottom, dataoption1:=xlSortNormal
    .Protect ("ElPasoTx67110")
End With
End Sub

Donnes moi ton sentiment, et si tu as une amélioration....

Ne se pourrait-il pas que le temps de chargement est aussi dû au fait que j'ai différents USF qui appelent LDProtegesActifs ?

Dans l'attente de te lire

Amicalement

Joseph

re,

J'ai essayé de mettre ton code en initialize, mais rien ne se passe.

Normal ce n'est pas là qu'il doit être

Donnes moi ton sentiment, et si tu as une amélioration....

Joseph je t'ai proposé un code qui fonctionne parfaitement.

Dans ton code tu nommes une variable Cp qui peut être évitée. La variable k ne sert pas non plus. Bref pourquoi ne pas continuer sur le code que je te propose.

Le pb est que tu n'expliques pas comment tu fonctionnes avec ton fichier.

Sur base du dernier, je pense comprendre que c'est ton bouton qui commande l'Userform.

Voici ce que je te propose :

• Remets le code que je t'ai proposé Sub LDProtegesActifs()
• Mets le code ci-après dans le module. Là où se trouve Sub LDProtegesActifs()

Sub demarre()
EP4.Show
End sub

- Associe ton bouton à la macro sub demarre()

Quelle est la différence entre Hide et Unload.

Hide cache l'userform

Unload décharge l'userform

A te relire

Dan

Bonjour Dan,

Tout d'abord, merci infiniment pour le mal que tu te donnes.

Joseph je t'ai proposé un code qui fonctionne parfaitement.

Dans ton code tu nommes une variable Cp qui peut être évitée. La variable k ne sert pas non plus. Bref pourquoi ne pas continuer sur le code que je te propose.

Ce n'est absolument pas une marque d'irrespect de ma part, mais en fait, je veux copier dans la feuille ("Actifs") le n°RG et concaténer le nom et prénom qui seront affichés dans l'USF (mais pas le n°RG); et comme je n'ai pas réussi avec ton code (tout simplement parce que je n'ai pas encore la compétence pour le comprendre), j'ai donc essayé avec ce que je savais faire. Dans la feuille ("Actifs") je n'ai besoin que du n°RG et de la concaténation nom et prénom.

Pour répondre à ta précédente question, la feuille ("Actifs") ne sert à rien d'autre.

En résumé, je ne demande qu'à reprendre ton code, mais je n'arrive pas à le modifier pour que dans l'USF ne s'affiche que le nom et prénom.

Le pb est que tu n'expliques pas comment tu fonctionnes avec ton fichier

D'un côté j'ai LDProtegesActifs dans un module, car c'est une procédure que j'appele plusieurs fois au sein de mon programme. Donc au lieu de l'écrire plusieurs fois, je l'ai mise dans un module. (C'est tout récent, je viens de le découvrir)

D'autre part, j'ai plusieurs USF dont EP4 dans laquelle j'ai besoin d'afficher les "Actifs".

En général, l'utilisateur doit encore saisir une année, ou une date d'entrée et de sortie, etc.

Soit je m'en sert pour afficher des données précises d'un fichier, soit je m'en sert pour piloter d'autres classeurs (ouverture, fermeture, enregistrement, etc.) en me servant du n°RG.

Voici ce que je te propose :

• Remets le code que je t'ai proposé Sub LDProtegesActifs()
• Mets le code ci-après dans le module. Là où se trouve Sub LDProtegesActifs()

Code:

Sub demarre()

EP4.Show

End sub

OK. Mais il faudrait que tu m'aides pour la modification évoquée plus haut.

- Associe ton bouton à la macro sub demarre()

Là je ne suis plus. Mes USF ont un bouton Valider et Annuler.

Quel bouton faut-il associer à cettte macro?

Je commence à être gêné de t'embêter avec tout çà!!!!

Merci de ta patience.

A te lire

Amicalement

Joseph