(Solved) – VBA Excel cell refresh problem before ScreenUpdating false

(solved)-–-vba-excel-cell-refresh-problem-before-screenupdating-false

I am facing a problem, it should not be a big deal but I am not finding a way to solve it even if it seems simple.

I have a code that should loop through files in a folder and copy their contents in 1 final file.

To avoid screen flashing, I am using Application.ScreenUpdating = False before starting my loop and at the end of the loop I put it back to True.

I am also writing in A1, “Processing please wait”, and at the end of the loop I clear the A1.

I call the function LoopThroughFiles, through an active x command button placed on MySheet.

The problem is that the message on the “A1” cell, doesn’t always appear ! sometimes it appears and sometimes it doesn’t. Most of time the first time that I launch the code it appears, than after 3-4 tests it stops appearing, than after again few tests it appears back and so on. it’s random.
here is what I noticed :

If I remove the Application.ScreenUpdating = False, it always appear.
If I put a break point or a message box before the Application.ScreenUpdating = False, also it always appears.

I think that Application.ScreenUpdating = False, is coming so fast after the A1.value = “Processing please wait”, so the A1 is filled, but Application.ScreenUpdating = False comes too fast so it takes effect before that the content of A1 appears and the screen is not refreshed.
I tried to put a Doevents, before the Application.ScreenUpdating = False, but still same problem, any idea how to solve that and makes always the A1 content appears before Application.ScreenUpdating = False ?

here is how looks like my code

Private Sub CommandButton1_Click()
Call LoopThroughFiles
End Sub

Public Function LoopThroughFiles() As String
'Declare Variables etc...

'I change the Value of A1
With ThisWorkbook.Sheets(MySheet).Range("A1")
.Font.Color = -16776961
.Font.Bold = True
.Value = "Processing, Please Wait ..."
End with

Application.ScreenUpdating = False

'MyLoop
'The Rest of code

Application.ScreenUpdating = True
'I clear A1
With ThisWorkbook.Sheets(MySheet).Range("A1")
.Value = vbNullString
.Font.ColorIndex = xlAutomatic
.Font.Bold = False
End with

End function

Leave a Reply

Your email address will not be published. Required fields are marked *