Page 1 of 2 12 LastLast
Results 1 to 10 of 20

Thread: Speed Up ListBox Getting Updated Filled Very Slow

  1. #1
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0

    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

  2. #2
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  3. #3
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0
    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.
    Last edited by Excel Fox; 02-06-2013 at 09:12 PM. Reason: Quote removed...

  4. #4
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  5. #5
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0
    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.
    Last edited by Excel Fox; 02-06-2013 at 09:12 PM. Reason: Quote removed...

  6. #6
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    You mean, just to fill 6 cells, it is taking too long?
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  7. #7
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0
    Yes indeed, when i push the update button it takes 3 second to fill the 6 cells.
    Last edited by Excel Fox; 02-06-2013 at 09:00 PM. Reason: Quote removed...

  8. #8
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    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)
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  9. #9
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,402
    Rep Power
    10
    And don't quote the previous post in all your replies.... it just makes the thread more clustered
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

  10. #10
    Junior Member
    Join Date
    Oct 2012
    Posts
    26
    Rep Power
    0
    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
    Last edited by Tony; 02-06-2013 at 09:39 PM.

Similar Threads

  1. Speed up excel to word VBA
    By bcostin in forum Excel Help
    Replies: 3
    Last Post: 05-22-2012, 10:49 AM
  2. Get the last updated file name.
    By princ_wns in forum Excel Help
    Replies: 3
    Last Post: 04-12-2012, 07:59 AM
  3. Speed up Loop VBA
    By PcMax in forum Excel Help
    Replies: 15
    Last Post: 04-09-2012, 04:20 PM
  4. Get last Filled Cell address in a Range.
    By Rajan_Verma in forum Rajan Verma's Corner
    Replies: 3
    Last Post: 03-24-2012, 01:08 AM
  5. Last Filled Cell Having Text
    By Excel Fox in forum Excel and VBA Tips and Tricks
    Replies: 5
    Last Post: 07-18-2011, 02:33 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •