Userform slow to open

[ipbr21054]

Evening,

I have a button which when pressed just opens a userform.

I press the button & straight away i see the outer border etc of the userform quite some seconds etc before the button,form color etc etc is even shown.
Im not sure what code you need to look at so i have supplied some below if you could advise please.

Private Sub UserForm_Initialize()'Modified  10/3/2018  5:51:42 AM  EDT
Application.ScreenUpdating = False
Dim LastRow As Long
LastRow = Sheets("POSTAGE").Cells(Rows.Count, "B").End(xlUp).Row
Dim Lastrowa As Long
Sheets("POSTAGE").Cells(8, 2).Resize(LastRow - 7).Copy Sheets("POSTAGE").Cells(1, 12)
Lastrowa = Sheets("POSTAGE").Cells(Rows.Count, "L").End(xlUp).Row
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo
CustomerSearchBox.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
ListBox1.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value
Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Clear
Application.ScreenUpdating = True
'USERNAME COMBOBOX


TextBox1.Value = Format(CDbl(Date), "dd/mm/yyyy")
TextBox2.SetFocus
End Sub

Private Sub UserForm_Activate()        'Dantes code
    'ListBox1.RowSource = "B:B" & Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long, j As Long, ws As Worksheet
    Set ws = Sheets("POSTAGE")
    For i = 8 To ws.Range("B" & Rows.Count).End(xlUp).Row
        added = False
        For j = 0 To ListBox1.ListCount - 1
            Select Case StrComp(ListBox1.List(j), ws.Cells(i, "B").Value, vbTextCompare)
                Case 0: added = True: Exit For
                Case 1: added = True: ListBox1.AddItem ws.Cells(i, "B").Value, j
                Exit For
            End Select
        Next
        If added = False Then ListBox1.AddItem ws.Cells(i, "B").Value
    Next
End Sub

 

[NoSparks]

If you bring the form up, not by clicking the button,
but by going to the VBA environment and putting the cursor anywhere within the UserForm_Initialize sub
then using the F8 key to step through things a line at a time, is there any point where it's a long time before the next line gets highlighted yellow ready to be executed ?

 

[Akuini]

I can see you're using loop here:

For i = 8 To ws.Range("B" & Rows.Count).End(xlUp).Row
For j = 0 To ListBox1.ListCount - 1

How big is your data? Thousands of rows?

 

[ipbr21054]

If you bring the form up, not by clicking the button,
but by going to the VBA environment and putting the cursor anywhere within the UserForm_Initialize sub
then using the F8 key to step through things a line at a time, is there any point where it's a long time before the next line gets highlighted yellow ready to be executed ?

I start to F8 through the Initialize code,its leaves this codes & goes to another code but then comes back and continues down each line.

So at present this is yellow

Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Sort key1:=Cells(1, 12).Resize(Lastrowa), order1:=xlAscending, Header:=xlNo

I then press F8 and see a error message 1004
The sort reference is not valid,make sure its within the data you want to sort,and the first sort by box isnt the same or blank.

My range to sort is column B8 onwards.
B7 is a header then names start from B8 downwards.

My current range is B8:B748

 

[ipbr21054]

Hi,

I have changed this

Private Sub UserForm_Activate()
          'ListBox1.RowSource = "B:B" & Range("B" & Rows.Count).End(xlUp).Row
    Dim i As Long, j As Long, ws As Worksheet
    Set ws = Sheets("POSTAGE")
    For i = 8 To ws.Range("B" & Rows.Count).End(xlUp).Row
        added = False
        For j = 0 To ListBox1.ListCount - 1
            Select Case StrComp(ListBox1.List(j), ws.Cells(i, "B").Value, vbTextCompare)
                Case 0: added = True: Exit For
                Case 1: added = True: ListBox1.AddItem ws.Cells(i, "B").Value, j
                Exit For
            End Select
        Next
        If added = False Then ListBox1.AddItem ws.Cells(i, "B").Value
    Next
End Sub

For this

Private Sub UserForm_Activate()    ListBox1.RowSource = "B2:B" & Range("B" & Rows.Count).End(xlUp).Row
End Sub

And then it opens within a blink of an eye like it is supposed to.

But with the one line code the names in ListBox1 are all mixed up.

With the multiple line code the names are then sorted & shown as A-Z

 

[NoSparks]

I've looked at the first post again.

In the Initialize code, you are loading ListBox1 with the same sorted list that's going into the CustomerSearchBox.
What's the purpose of UserForm_Activate ?

 

[ipbr21054]

Hi,
In the Userform Initialize code if i remove this code below its still the same.

ListBox1.List = Sheets("POSTAGE").Cells(1, 12).Resize(Lastrowa).Value

In the Userform Activate code if i remove the multiple line code & just have the code below then the list is all over the place and not A-Z

 ListBox1.RowSource = "B2:B" & Range("B" & Rows.Count).End(xlUp).Row

With both the list is shown & in order of A-Z but slow to open.

I put that there to show you what was what

 

[Kyle123]

AddItem is the bottleneck, it’s terribly slow. You’d be better writing to a temporary array in your loop and then using that to populate the list box using the .item property

 

[NoSparks]

@ ipbr21054
In your words please tell me what the UserForm_Activate sub is supposed to do.

 

[ipbr21054]

@ ipbr21054
In your words please tell me what the UserForm_Activate sub is supposed to do.

Sort the names A-Z and show them in the Listbox1 on the userform.