Macro Treeview très lente !!!!

[josef140]

Bonjour,

Je suis entrain de construire une arborescence avec un userform treeview le problème c'est que avant quand j'avais que 3 sections la macro s'executait très vite maintenant j'ai 6 sections dans un premier temps j'avais une erreur dépassement de capacité j'ai changé les integer par long et l'erreur a disparu par contre la macro prend 2 minute pour s'executer . s'ils vous plait j'attend votre aide .

Option Explicit
Private Sub CommandButton1_Click()
Unload Me
End Sub

Private Sub TreeView1_BeforeLabelEdit(Cancel As Integer)

End Sub

Private Sub UserForm_Initialize()
Dim data As Collection
Dim i, x As Long
Dim j As Long
Dim c As Range
Dim ModeRecalcul As Long

ModeRecalcul = Application.Calculation
' Réglage du recalcul sur mode manuel
Application.Calculation = xlCalculationManual

Set data = New Collection

On Error Resume Next
For Each c In Range("a2:a" & Range("a65536").End(xlUp).Row)
    data.Add c.Text, c.Text
Next c
On Error GoTo 0

For i = 1 To data.Count
    TreeView1.Nodes.Add , , "C" & i, data.Item(i)
Next i


For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("a2:a" & Range("a65536").End(xlUp).Row)
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("b2:b" & Range("b65536").End(xlUp).Row)
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("c2:c" & Range("c65536").End(xlUp).Row)
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("d2:d" & Range("d65536").End(xlUp).Row)
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("e2:e" & Range("e65536").End(xlUp).Row)
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
    Next c
Next i

Application.Calculation = ModeRecalcul

End Sub

 

[fhoest]

Re : Macro Treeview très lente !!!!

Bonjour,
peux tu joindre svp le fichier pour ne pas devoir retaper toutes les lignes a tester,pour essayer d'améliorer la macro,
sans données confidentiel si possible.
Merci.

 

[josef140]

Re : Macro Treeview très lente !!!!

Bonjour,
Vous trouverez ci-joint le fichier.
Merci.Regarde la pièce jointe Réseau2.xlsm

 

[fhoest]

Re : Macro Treeview très lente !!!!

Bonjour,

Private Sub UserForm_Initialize()
Dim data As Collection
Dim i, x As Long
Dim j As Long
Dim c As Range
Dim ModeRecalcul As Long

Dim drla, drlb, drlc, drld, drle As Long
drla = Range("a65536").End(xlUp).Row
drlb = Range("b65536").End(xlUp).Row
drlc = Range("c65536").End(xlUp).Row
drld = Range("d65536").End(xlUp).Row
drle = Range("e65536").End(xlUp).Row

Set data = New Collection

On Error Resume Next
For Each c In Range("a2:a" & drla)
    data.Add c.Text, c.Text
Next c
On Error GoTo 0




For i = 1 To data.Count
    TreeView1.Nodes.Add , , "C" & i, data.Item(i)
Next i


For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("a2:a" & drla)
        If c = "" Then GoTo ici
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1

        End If
ici:
Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    
    For Each c In Range("b2:b" & drlb)
        If c = "" Then GoTo ici1
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
ici1:
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
   For Each c In Range("c2:c" & drlc)
       If c = "" Then GoTo ici2
       If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
               TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
               x = x + 1
       End If
ici2:
   Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    
    For Each c In Range("d2:d" & drld)
       If c = "" Then GoTo ici3
       If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
ici3:
    Next c
Next i

For i = 1 To TreeView1.Nodes.Count
    For Each c In Range("e2:e" & drle)
        If c = "" Then GoTo ici4
        If Me.TreeView1.Nodes.Item(i).Text = c.Value Then
                TreeView1.Nodes.Add Me.TreeView1.Nodes.Item(i).Key, tvwChild, "Cc" & x, c.Offset(0, 1)
                x = x + 1
        End If
ici4:
    Next c
Next i



End Sub

essaie ceci.
A+

 

[josef140]

Re : Macro Treeview très lente !!!!

Bonjour,
Merci beaucoup ça marche très bien A+