Application.ScreenUpdating = True but cell doesn't show update.

[RHONDAK72]

I have written some code where it will turn on screen updating just long enough to input a text value into a cell that explains to the user what the current status is for the procedure. Once the status is inputted into the cell, I then the VBA turns off screen updating so that the rest of the procedure will run in the background. This is in order to help the code to run faster and to eliminate the screen from constantly flashing.

Next, once the code gets to a certain spot in the procedure, the code will turn screen updating back on, input another status value into the next cell to show the user the latest status of the current procedure. After that cell is updated, screen updating is turned off again while additional steps are completed in the background.

However, even though I am specifying code to turn on screen updating, enter the cell value with he status value and then turn off screen updating, I’m finding that the cells do not show the updated status value until after the entire code has finished running. Does anyone know why it doesn’t show the status as the cell value until the very end?

Is it a speed thing where screen updating is set back to true, but because the code is running so fast, it does not update the screen in time for the inputted value to be displayed in the cell before screen updating is set back to false? If so, how would you recommend fixing it? I want the status to update the cells o that it can be seen by the user the moment it is updated, not at the very end. Thanks in advance.

Example:

Application.ScreenUpdating = True
Worksheets(“Sheet1”).Range(“A1”).Value = “Process ABC started”
Application.ScreenUpdating = False

‘More code here runs through a procedure to replace various cell values with another value based on specific criteria. it runs through a loop until it reach Wend… Then the next cells A2 and A3 as shown below are updated with the latest status…

Application.ScreenUpdating = True
Worksheets(“Sheet1”).Range(“A2”).Value = “Process ABC finished”
Worksheets(“Sheet1”).Range(“A3”).Value = “Process DEF started”
Application.ScreenUpdating = False

‘More code here runs through a procedure to replace various cell values with another value based on specific criteria. it runs through a loop until it reach Wend… Then the next cells A3 and A4 as shown below are updated with the latest status…

Application.ScreenUpdating = True
Worksheets(“Sheet1”).Range(“A3”).Value = “Process DEF finished”
Worksheets(“Sheet1”).Range(“A4”).Value = “Process GHI started”
Application.ScreenUpdating = False

‘More code here runs through a procedure to replace various cell values with another value based on specific criteria. it runs through a loop until it reach Wend… Then the next cell A4 as shown below is updated with the latest status…

Application.ScreenUpdating = True
Worksheets(“Sheet1”).Range(“A4”).Value = “Process GHI finished”

‘and so on…

 

[joeu2004]

You might try adding the statement DoEvents before setting ScreenUpdating = False.

 

[RHONDAK72]

Thank you. I'm not very familiar with DoEvents. (I know only bits and pieces of VBA...) What does this statement do? Can you provide an example?

 

[joeu2004]

I'm suggesting that you do this (in multiple places):

Application.ScreenUpdating = True
Worksheets(“Sheet1”).Range(“A1”).Value = “Process ABC started”
DoEvents
Application.ScreenUpdating = False

-----

DoEvents yields the CPU to other threads that might be ready to execute. Perhaps more importantly, it releases the "lock" that prevents Excel from executing while VBA is executing (an internal design mechanism).

So it might give Excel the opportunity to react to your set ScreenUpdating = True.

I always wonder how effective DoEvents truly is on a computer that has multiple CPUS, each with multiple hardware "threads". But others claim that it does make things work. So I suspect that it is the release of the thread-execution "lock" that is more significant. If indeed the suggestion works. I don't know.

 

[My Aswer Is This]

I would suggest having a Message box popup with some sort of message.
The script stops until you click the OK button.

 

[RHONDAK72]

Thank you both very much. I will try this out and follow up as soon as possible.

 

[Mr P]

I've had this issue and tried all the above suggestions but to no avail. In case it helps others, this approach has worked for me:

Use the following in your code:

Application.ScreenUpdating = True
DoEvents
WaitTime (0.001)

With the WaitTime function being:

Function WaitTime(Time As Single)

Dim StartTime As Single
Dim DelayTime As Single

StartTime = Timer

Do Until DelayTime > Time
    DelayTime = Timer - StartTime
Loop

End Function

This doesn't seem to slow code appreciably, but as with any screen updating it depends how often you trigger it. The wait time may need to be adjusted depending on how much you are trying to update the screen. In my instance it is a small number of cells being changed.