Userform slow to open

ipbr21054

Well-known Member
Joined
Nov 16, 2010
Messages
5,199
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:
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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 ?
 
Upvote 0
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?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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 ?
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
@ ipbr21054
In your words please tell me what the UserForm_Activate sub is supposed to do.
 
Upvote 0

Forum statistics

Threads
1,213,515
Messages
6,114,080
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top