Speed Up ListBox Getting Updated Filled Very Slow

[Tony]

Hi everyone.

I'm working on a script for a while know. Everything works fine except that the updating goes very slow.
I use the code below. I have a excel sheet with a range from A8:H282. When i use the update button it takes about 2 second before the new value has been listed and updated.

The other thing is that when the listbox has been loaded the scrollbox is very very long. So it displays the range but i can scroll a lot of white space after the listed range.

Private Sub UserForm_Initialize()
    Dim x As Long
    Dim k As Long
    Dim j As Long
    Dim rng As Range
   Set rng = Sheets("INPUT WV LISTBOX").Range("A8:H282") 
    Rx1 = 8 
    With ListBox1
    .RowSource = ""
    For k = 1 To rng.Rows.Count 'rows
        For j = 1 To rng.Columns.Count
        .AddItem
        .List(k - 1, j - 1) = rng.Cells(k, j).Text 'columns
        Next j, k
    End With
    For j = 1 To 8
        Me.Controls("TextBox" & j).Text = ""
    Next j
End Sub

Update button

Private Sub CommandButton2_Click()
    Dim j As Long
    Dim x As Long

    If Me.ListBox1.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If

    If Me.TextBox1.Value = "" Then Exit Sub

    Rx1 = Rx1 + Me.ListBox1.ListIndex    'row index
    With Sheets("INPUT WV LISTBOX")
        For j = 3 To 8  'columns Start in Column 4 but you wish to NOT change
            ' columns 4 through 7 so start in Column 8
            .Cells(Rx1, j).Value = Me.Controls("TextBox" & j - 0).Text   
        Next j
    End With
    Call UserForm_Initialize
End Sub

[Excel Fox]

First off, you placed the AddItem in the wrong loop. It should be

   For k = 1 To rng.Rows.Count 'rows
       .AddItem
       For j = 1 To rng.Columns.Count
       .List(k - 1, j - 1) = rng.Cells(k, j).Text 'columns
       Next j
    Next k

Second of all, you DON'T need all that loop to populate the listbox. Instead, use

    With ListBox1        
.RowSource = ""
        .List = Application.Transpose(Application.Transpose(rng))
    End With

[Tony]

Hi,

I adjusted the code and when i initizalize the listbox it goes a lot faster split second. But as soon i click on the update button it somehow goes again through al the 2000+ cells and shows the complete worksheet instead of only the range.

Why i use is the loop is because of the cell property's i use. When i use your first code it keeps the cell property's eg. % etc. but when i use your second code it wont.

Regards.

[Excel Fox]

Just add

ListBox1.Clear

before the loop....
If you want the TEXT property of cell, then I'd suggest you using the loop... if not, the LIST method is fine

[Tony]

Funny i just added the .clear and removed the

.RowSource=""

populating the listbox goes now in split sec

Still it takes 3 sec to update the content. I tested it and the 3 sec is in the

 .Cells(Rx1, j).Value = Me.Controls("TextBox" & j - 0).Text

code of the update button. But i cant findout why.

[Excel Fox]

You mean, just to fill 6 cells, it is taking too long?

[Tony]

Yes indeed, when i push the update button it takes 3 second to fill the 6 cells.

[Excel Fox]

Try

    Dim var(3 To 8) As String
    .......
    .......
        For j = 3 To 8  'columns Start in Column 4 but you wish to NOT change
            var(j) = Me.Controls("TextBox" & j - 0).Text
        Next j
        .Cells(Rx1, 3).Resize(6).Value = Application.Transpose(var)

[Excel Fox]

And don't quote the previous post in all your replies.... it just makes the thread more clustered

[Tony]

Like this? it updates faster but it places it in the wrong cells.

Private Sub CommandButton2_Click()
    Dim var(3 To 8) As String
    Dim j As Long
    Dim x As Long

    If Me.ListBox1.ListIndex = -1 Then
        MsgBox "No Data Selected"
        Exit Sub
    End If

    If Me.TextBox1.Value = "" Then Exit Sub

    Rx1 = Rx1 + Me.ListBox1.ListIndex    'row index
    With Sheets("INPUT WV LISTBOX")
        For j = 3 To 8
        var(j) = Me.Controls("TextBox" & j - 0).Text 'columns Start in Column 4 but you wish to NOT change
            ' columns 4 through 7 so start in Column 8
        Next j
        .Cells(Rx1, 3).Resize(6).Value = Application.Transpose(var)
    End With
    Call UserForm_Initialize
End Sub