Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Arrow

    I'm populating listboxes and want to reduce screen flicker. If I populate them between the screenupdating=false and screenupdating=true commands, the items in the listboxes aren't displayed. If I don't use the screenupdating commands all of the objects on the page flicker each time a line is added to the listbox. Is there a way to see these items while still eliminating the flicker?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Which version of Excel are you using?

    What kind of list box are you using?

    1. A list box on a worksheet, from the Control Toolbox toolbar (or its Excel 2007 equivalent).
    2. A list box on a worksheet, from the Forms toolbar (or its Excel 2007 equivalent).
    3. A list box on a userform (created in the Visual Basic Editor).

  3. #3
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    Which version of Excel are you using?

    What kind of list box are you using?

    1. A list box on a worksheet, from the Control Toolbox toolbar (or its Excel 2007 equivalent).
    2. A list box on a worksheet, from the Forms toolbar (or its Excel 2007 equivalent).
    3. A list box on a userform (created in the Visual Basic Editor).
    Excel 2003. The listbox is from the VBA toolbar.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    What exactly do you mean by the VBA toolbar?


  5. #5
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I have Excel 2003 at work and 2007 at home, and I'm home now, so let me think. I believe it's the toolbar that I brought up from the Visual Basic Editor, which I got to from Tools, Macro. Here's the code, if that helps:

    On Error GoTo HandleError
    Application.ScreenUpdating = False
    Set wsq = ActiveSheet

    Me.Activate
    lstEmpFrom.Clear
    lstEmpTo.Clear

    'Make the EmpMonDates collection



    On Error Resume Next
    For Each cell In wsq.Range("$b$17:$b$100")
    EmpMonDates.Add cell.Value, CStr(cell.Value)
    Next cell

    'Sort the EmpMonDates collection

    For i = 1 To EmpMonDates.Count - 1
    For j = i + 1 To EmpMonDates.Count
    If EmpMonDates(i) > EmpMonDates(j) Then
    Swap1 = EmpMonDates(i)
    Swap2 = EmpMonDates(j)
    EmpMonDates.Add Swap1, before:=j
    EmpMonDates.Add Swap2, before:=i
    EmpMonDates.Remove i + j
    EmpMonDates.Remove j + i
    End If
    Next j
    Next i

    On Error GoTo 0

    'Add the dates to the list boxes

    For Each Item In EmpMonDates
    ActiveSheet.lstEmpFrom.AddItem Item
    ActiveSheet.lstEmpTo.AddItem Item
    Next Item
    Application. ScreenUpdating = True



  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    The line

    ActiveSheet.lstEmpFrom.AddItem Item

    shows that you're using a list box on a worksheet from the Control Toolbox toolbar. Try adding a line

    DoEvents

    after

    Application.ScreenUpdating = True

    Alternatively, switch to another worksheet and back (this can be done before Application.ScreenUpdating = True)


  7. #7
    2 Star Lounger
    Join Date
    Apr 2004
    Location
    Jacksonville, Florida, USA
    Posts
    117
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    The line

    ActiveSheet.lstEmpFrom.AddItem Item

    shows that you're using a list box on a worksheet from the Control Toolbox toolbar. Try adding a line

    DoEvents

    after

    Application.ScreenUpdating = True

    Alternatively, switch to another worksheet and back (this can be done before Application.ScreenUpdating = True)
    Thanks, Hans! I knew you'd have a good answer for me.

Posting Permissions

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