Results 1 to 11 of 11
  1. #1
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Forcing screen update? (2000/sp3)

    I've got tons of stuff going on in a button click() event/macro.. I'm opening other workbooks, searching them, pulling peices out of them, and putting them into a new workbook...

    Naturally, I have "application.screenupdates = false" before it all, and "...=true" when I'm done.

    Everything works great, I'm just making it a little more slick. :-)

    I've got a listbox that I use ".additem" to populate (not bound to any worksheet items). This listbox has several columns, one of which being a "status" field. I update this field to "waiting," "processing," "done," "skip (error)," etc.

    Obviously with screen updates off, this item doesn't get updated. So I have the following type statements for each update:

    Application.ScreenUpdating = True
    lstMyBox.List(lngIndex, constStatusColumn) = "Processing..."
    Application.ScreenUpdating = False

    However, I think because Excel is so busy processing all these files, it doesn't actually repaint the screen with the new information. I set them all to "waiting" before I start, so in effect what I see when I click the button is that they all go to "waiting" and then when it's all done, they all update to "done" I don't see anything in between.

    Is there a way to get excel to _force_ a repaint event on the screen, or at least this listbox?

    thanks so much!
    ..dane

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Try this slight modification:

    <pre> Application.ScreenUpdating = True
    lstMyBox.List(lngIndex, constStatusColumn) = "Processing..."
    DoEvents
    Application.ScreenUpdating = False
    </pre>

    Legare Coleman

  3. #3
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Legare,

    Thanks for the suggestion. It works to some extent. Files that are processed too quickly are still skipped (so that it looks like one file is being processed, and then rather than the "processing" string moving to the next item, it looks like it "jumps" to say the fith one down, and the ones in between just go to "done." And when the final msgbox appears, the lstbox contents have not caught up either... (see attached screenshot)

    I can live with that. There are still flashes on the screen where it looks like it's going back and forth to some of the other workbooks... Again -- I can live with it, but it's less than ideal.

    Any other suggestions?

    ..dane

  4. #4
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    My only other suggestion would be to put the DoEvents in a For loop and execute it a number of times.
    Legare Coleman

  5. #5
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Good thought. Doesn't seem to change anything (tried a loop of 1,000 times, and 15,000 times). I'll keep messing with it, but so far no solution..

    ..dane

  6. #6
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Interesting...

    I tried changing a few properties to "force" the update, and it does work very well.

    I first attempted to change the ".forecolor", but it turns out that when you change the color (fore or back), any highlights (selections) within the list are removed.

    I next tried to 'touch' the .enabled property (lstbox.enabled = lstbox.enabled) with no luck.

    However, _changing_ the property (and then changing it back) does force the update.

    So, my code looks like this:

    <pre>'here we go
    application.screenupdating = false

    ' do lots of stuff

    lstbox.item(x,y) = newvalue

    application.screenupdating=true
    lstbox.enabled = not lstbox.enabled ' do it once to force an update
    lstbox.enabled = not lstbox.enabled ' do it twice to set it back to what it was
    application.screenupdating=false

    ' continue doing lots of stuff

    'we're done.
    application.screenupdating=true
    </pre>


    and it works like a charm.

    Only thing next is to encapsulate this into a function so it's nice and tidy...

    <pre>Public Sub subUpdateListBox(ByRef varListBox As ListBox)

    Dim blnUpdateState As Boolean

    ' save current state
    blnUpdateState = Application.ScreenUpdating

    ' enable the screen
    Application.ScreenUpdating = True
    ThisWorkbook.Activate

    ' force control 'repaint'
    varListBox.Enabled = Not varListBox.Enabled
    varListBox.Enabled = Not varListBox.Enabled

    ' return to previous screen setting
    Application.ScreenUpdating = blnUpdateState

    End Sub
    </pre>


    But unfortunately when I call this function:

    subUpdateListBox(lstbox)

    I get the following error:

    Runtime error '424': Object Required

    any ideas? I think my newbieness is showing here because I don't know what this error means, or how to resolve it. [img]/forums/images/smilies/sad.gif[/img]

  7. #7
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    You might have a little bit more control if you use a dialog box as your progress reporter - you don't have to worry about repainting your screen. See the attached as a simple example that works under XL2000 and XL97.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  8. #8
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Tim,

    Great idea, thanks for that. I may use that.

    I'm a little reluctant to add a progress bar at this point, but I may consider it if I can't get the other going just right... I will certainly hold on to it for future projects though!

    ..dane

  9. #9
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Just thought I'd let everyone know what I've ended up doing (that works)...

    Excel will repaint a shape (control) when its width changes also... Since the "enabled" property does not apply to all shapes, but the "width" property does, I have adjusted my sub to accept the stringName of a shape, and it will search the activesheet for that shape, adjust its width + 1 and then - 1, and call DoEvents to allow Excel to repaint the shape. This snippet works great. Feel free to grab it and use/abuse it as you wish:

    <pre>' Force a 'repaint' of a control by quickly adjusting
    ' its width, and then calling doevents...
    Public Sub subForceShapeRepaint(strControlName As String)

    Dim blnUpdateState As Boolean
    Dim shpTemp As Shape
    Dim lngTemp As Long

    ' save current state
    blnUpdateState = Application.ScreenUpdating

    ' enable the screen
    Application.ScreenUpdating = True
    ThisWorkbook.Activate

    ' force control 'repaint' by changing
    ' its width by one and then back again...
    For Each shpTemp In ActiveSheet.Shapes
    If shpTemp.Name = strControlName Then
    shpTemp.Width = shpTemp.Width + 1 ' +1, in case it's width=1...
    shpTemp.Width = shpTemp.Width - 1 ' back to our original size...
    End If
    Next shpTemp

    ' Let Excel update itself...
    DoEvents

    ' return to previous screen setting
    Application.ScreenUpdating = blnUpdateState

    End Sub
    </pre>


    ..dane

  10. #10
    5 Star Lounger Ruff_Hi's Avatar
    Join Date
    Feb 2001
    Location
    New York, New York, USA
    Posts
    768
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    I was more thinking along the lines that you would put your list box on the dialog form and update it there. you could put anything on the progress form - I usually put some form of counter just to show the end user that something is actually happening.
    (Location Australia, then UK, but now USA. Heart, outlook, attitude, etc always Australian)
    Quote: "All Happiness is the release of internal pressure"

  11. #11
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Forcing screen update? (2000/sp3)

    Tim,

    Ah-ha! My newbieness is obviously showing... I didn't even think about putting different control data on your form. But that's a very good idea. Theoretically, I could have all of my controls (buttons, listboxes, etc) on a userform, and use the userform's repaint mechanism. Wow, that's brilliant. <img src=/S/doh.gif border=0 alt=doh width=15 height=15>

    Well, the tool seems to work now and I think is done. But I will definately consider this route for future tools.

    Thanks for all the help everyone,
    ..dane

Posting Permissions

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