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