Userform lent à charger

Bonjour chers tous

Mon userform est lent à s'afficher lorsqu'on le lance.

Il contient un listbox qui doit afficher une plage de données.

Je remarque que plus la plage est énorme le userform s'ouvre lentement.

Voici le code dans mon userform

Option Explicit
Option Compare Text
Dim tbl
Dim ls, dl, xl As Integer, Loc, i As Long, sh As Worksheet, t, x, w As String, P, p1 As Range

Private Sub UserForm_Initialize()

Sheets("Base").Activate
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False
Me.startupposition = 0
Me.top = 0
Me.Left = Application.UsableWidth - Me.Width
If Sheets("Exp").Range("A5") <= 0 Then
TextBox5.Enabled = False
End If
Label46.Caption = [TB!B8] & " _ " & UCase(Format([TB!B11], " mmmm yyyy"))
TextBox5.SetFocus
Enregistrer.Locked = True
Annuler_Saisie.Locked = True
Worksheets("Base").Unprotect "2580"
Application.ScreenUpdating = False
Dim dl As Long
dl = Sheets("Base").Range("A" & Rows.Count).End(xlUp).Row
With Sheets("Base")
        .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row + 1).Font.Size = 12
        entetes.Column = Application.Transpose(.[A1].Resize(1, 11).Value)
        If .Range("A" & Rows.Count).End(xlUp).Row = 1 Then
        tbl = .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row + 1).Value
        ListBox2.List = tbl
        Else
        tbl = .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row).Value
        ListBox2.List = tbl
        For i = 0 To ListBox2.ListCount - 1
        ListBox2.List(i, 1) = Format(CDate(ListBox2.List(i, 1)), "mmm-yy")
        Next i
        End If
End With
Application.ScreenUpdating = False
With Worksheets("Base")

If .Columns("N:AQ").EntireColumn.Hidden = False Then .Columns("N:AQ").EntireColumn.Hidden = True

        Dim col As Range
For Each col In .Columns("A:M")
    col.AutoFit
    If col.ColumnWidth < 12 Then col.ColumnWidth = 12
Next

.Range("A2:A" & .Range("A" & Rows.Count).End(xlUp).Row + 1).NumberFormat = "@"
.Range("K2:K" & .Range("K" & Rows.Count).End(xlUp).Row).NumberFormat = "dd-mmm-yy"
End With
Application.ScreenUpdating = False
TextBox5 = [TB!D7]
ComboBox10 = 1
ComboBox8 = "Oui"
ComboBox7 = "Non_Stable"
ComboBox6 = "Non"
ActiveSheet.Protect "2580"
Sheets("Ages").Unprotect "2580"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Bonjour,

A quoi tous ces "Application.ScreenUpdating = False", un seul suffit en début de macro.(mais ce n'est pas ça qui joue sur le temps d'exécution).

Je pense que votre problème se situe ici

            tbl = .Range("A2:K" & .Range("A" & Rows.Count).End(xlUp).Row).Value
            ListBox2.List = tbl
            For i = 0 To ListBox2.ListCount - 1
                ListBox2.List(i, 1) = Format(CDate(ListBox2.List(i, 1)), "mmm-yy")
            Next i
        End If

Ne vous est-il pas possible d'appliquer le format directement sur la plage "A2:K & dernière ligne"? ainsi la boucle qui suit devient inutile.

Cdlt

Merci!!!

je revois mon code