Force a screen update in Excel VBA

Question

My Excel tool performs a long task  and I m trying to be kind to the user by providing a progress report in the status bar  or in some cell in the sheet  as shown below  But the screen doesn t refresh  or stops refreshing at some point  e g  33    The task eventually completes but the progress bar is useless   What can I do to force a screen update   For i 1 to imax   imax is usually 30 or so     fractionDone cdbl i  cdbl imax      Application StatusBar   Format fractionDone   0     amp   done           or  alternatively        statusRange value   Format fractionDone   0     amp   done            Some code         Next i   I m using Excel 2003

User · Answer

This worked for me     ActiveWindow SmallScroll down  0   or more simply   ActiveWindow SmallScroll 0

User · Answer

Text boxes in worksheets are sometimes not updated  when their text or formatting is changed  and even the DoEvent command does not help   As there is no command in Excel to refresh a worksheet in the way a user form can be refreshed  it is necessary to use a trick to force Excel to update the screen   The following commands seem to do the trick   - ActiveSheet Calculate     - ActiveWindow SmallScroll     - Application WindowState   Application WindowState

User · Answer

Hubisans comment worked best for me   ActiveWindow SmallScroll down  1 ActiveWindow SmallScroll up  1

User · Answer

This is not directly answering your question at all  but simply providing an alternative   I ve found in the many long Excel calculations most of the time waiting is having Excel update values on the screen   If this is the case  you could insert the following code at the front of your sub   Application ScreenUpdating   False Application EnableEvents   False   and put this as the end  Application ScreenUpdating   True Application EnableEvents   True   I ve found that this often speeds up whatever code I m working with so much that having to alert the user to the progress is unnecessary   It s just an idea for you to try  and its effectiveness is pretty dependent on your sheet and calculations

User · Answer

Put a call to DoEvents in the loop   This will affect performance  so you might want to only call it on each  say  10th iteration  However  if you only have 30  that s hardly an issue

User · Answer

I couldn t gain yet the survey of an inherited extensive code  And exact this problem bugged me for months  Many approches with DoEnvents were not helpful  Above answer helped  Placeing this Sub in meaningful positions in the code worked even in combination with progress bar Sub ForceScreenUpdate       Application ScreenUpdating   True     Application EnableEvents   True     Application Wait Now    12 00 01 AM      Application ScreenUpdating   False     Application EnableEvents   False End Sub

User · Answer

Specifically  if you are dealing with a UserForm  then you might try the Repaint method  You might encounter an issue with DoEvents if you are using event triggers in your form  For instance  any keys pressed while a function is running will be sent by DoEvents The keyboard input will be processed before the screen is updated  so if you are changing cells on a spreadsheet by holding down one of the arrow keys on the keyboard  then the cell change event will keep firing before the main function finishes   A UserForm will not be refreshed in some cases  because DoEvents will fire the events  however  Repaint will update the UserForm and the user will see the changes on the screen even when another event immediately follows the previous event   In the UserForm code it is as simple as   Me Repaint

User · Answer

Add a DoEvents function inside the loop  see below   You may also want to ensure that the Status bar is visible to the user and reset it when your code completes   Sub ProgressMeter    Dim booStatusBarState As Boolean Dim iMax As Integer Dim i As Integer  iMax   10000      Application ScreenUpdating   False     Turn off screen updating      booStatusBarState   Application DisplayStatusBar     Get the statusbar display setting      Application DisplayStatusBar   True     Make sure that the statusbar is visible      For i   1 To iMax      imax is usually 30 or so         fractionDone   CDbl i    CDbl iMax          Application StatusBar   Format fractionDone   0     amp    done                  or  alternatively               statusRange value   Format fractionDone   0     amp    done                  Some code                 DoEvents             Yield Control      Next i      Application DisplayStatusBar   booStatusBarState     Reset Status bar display setting      Application StatusBar   False     Return control of the Status bar to Excel      Application ScreenUpdating   True     Turn on screen updating  End Sub

[vba] Force a screen update in Excel VBA

Examples related to vba

Examples related to excel