Speed Up ListBox Getting Updated Filled Very Slow

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.

Code:

---

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

Code:

---

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

---

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

Code:

---

  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

Code:

---

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

---

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.

Just add

Code:

---

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

Funny i just added the .clear and removed the

Code:

---

.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

Code:

---

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

---

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

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

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

Try

Code:

---

    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)

---

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

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

Code:

---

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

---

What exactly is happening? I didn't have your file to test it. I'm sure you can figure out what's going wrong where, since it's pretty straight forward...

It updates the the 6 cells in 1 column 6 rows instead of updating the 6 cells in 1 row and 6 columns. See Attachment 595

Try this then

Code:

---

.Cells(Rx1, 3).Resize(,6).Value = var

---

Great works FAST :D

Last question... what do i need to change when working with multitabs and multiple listboxes. I tried to use the code below.

Code:

---

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

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

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

    Rx2 = Rx2 + Me.ListBox1.ListIndex    'row index
    With Sheets("INPUT BS LISTBOX")
        For j = 3 To 8
            var(j) = Me.Controls("TextBox" & j + 8).Text 'Starts in Textbox 9
    Next j
            .Cells(Rx2, 3).Resize(, 6).Value = var
    End With
    Call UserForm_Initialize
End Sub

---

Got a question about the code i used couple of days. When i typed for example 3 in my textbox and i pushed on update it showed automatically 3% but with the new code it does not take over the cell property's :( how can.

OLD CODE (fills cell format e.g. 3 will be automatically 3%)

Code:

---

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 
    With Sheets("INPUT WV LISTBOX")
        For j = 8 To 10
            .Cells(Rx1, j).Value = Me.Controls("TextBox" & j - 3).Text
        Next j
    End With
    Call UserForm_Initialize
End Sub

---

NEW CODE FAST (but puts only the value in the box e.g. 3 stays 3.)

Code:

---

rivate 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
    With Sheets("INPUT WV LISTBOX")
        For j = 3 To 8
        var(j) = Me.Controls("TextBox" & j).Text
        Next j
        .Cells(Rx1, 3).Resize(, 6).Value = var
    End With
    Call UserForm_Initialize
End Sub

---

try

Code:

---

.Cells(Rx1, 3).Resize(, 6).Formula = var

---

Just tested but not working :(

Why don't you post a sample workbook ???

Quote:

---

Originally Posted by snb

Why don't you post a sample workbook ???

---

See Attachment 599

When i use

Code:

---

.Cells(Rx1, 3).Resize(6).Value = Application.Transpose(var)

---

it works but then the data wil be placed in 1 column

When i use

Code:

---

.Cells(Rx1, 3).Resize(, 6).Value = var

---

it places the content in 6 column on 1 row what i need but then the cell propertys will not be used :confused:

Solved the problem using

Code:

---

.Cells(Rx1, 3).Resize(, 6).Value = Application.Transpose(var)

---

:) one small change...but don't know why that function application.transpose does the trick. :confused: