VBA Excel cell refresh problem before ScreenUpdating false

1
votes

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
2
excelvba

2 Answers

0
votes

try to put 

Application.ScreenUpdating = True

after write content to A1. this will look like 

With ThisWorkbook.Sheets(MySheet).Range("A1")
.Font.Color = -16776961
.Font.Bold = True
.Value = "Processing, Please Wait ..."
End with
Application.ScreenUpdating = True
Application.ScreenUpdating = False
0
votes

Just if it may help someone, I fixed my problem by adding a loop to slow the code. I prefere not to use the Wait because Wait is for minimum 1 second and I don't need to Wait 1 second I prefere not to use Sleep because it's an API, and I prefere to use native vba, it will be better for version's compatibility I had to choose between a loop of debug.print or doevents, I prefered to do that way :

Dim cptWait as integer
For cptWait = 1 To 75
Debug.Print "Wait 75"
Next cptWait

So the function became like this 

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

Dim cptWait as integer
For cptWait = 1 To 75
Debug.Print "Wait 75"
Next cptWait

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

Thanks to all who tried to help