Userform slow to open

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
3,655
Office Version
  1. 2007
Platform
  1. Windows
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.

Code:
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

Code:
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

Code:
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
997
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,575
Office Version
  1. 365
Platform
  1. Windows
I can see you're using loop here:
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,655
Office Version
  1. 2007
Platform
  1. Windows
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
Code:
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,655
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,

I have changed this

Code:
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

Code:
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

Well-known Member
Joined
Mar 15, 2013
Messages
997
Office Version
  1. 2010
Platform
  1. Windows
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

Well-known Member
Joined
Nov 16, 2010
Messages
3,655
Office Version
  1. 2007
Platform
  1. Windows

ADVERTISEMENT

Hi,
In the Userform Initialize code if i remove this code below its still the same.
Code:
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
Code:
 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

Well-known Member
Joined
Jan 24, 2012
Messages
2,708
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

Well-known Member
Joined
Mar 15, 2013
Messages
997
Office Version
  1. 2010
Platform
  1. Windows
@ ipbr21054
In your words please tell me what the UserForm_Activate sub is supposed to do.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,522
Messages
5,529,330
Members
409,863
Latest member
stacy09
Top