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

[Excel Fox]

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...

[Tony]

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

[Excel Fox]

Try this then



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

[Tony]

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.


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

[Tony]

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%)

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.)

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

[Excel Fox]

try

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

[Tony]

Just tested but not working :(

[snb]

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

[Tony]

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

See 599

When i use
.Cells(Rx1, 3).Resize(6).Value = Application.Transpose(var) it works but then the data wil be placed in 1 column

When i use
.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:

[Tony]

Solved the problem using
.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: