Msgbox displays before the query has finished refreshing

[JeffGrant]

Hi All,

I often have this rather annoying issue where at the end of a sub I have a message box to let me know when the query refresh is done. The same also applies for an external program I run.

More often than not, the MsgBox gets displayed ahead of the query or external program finishing.

I have tried all manner of methods to avoid this as there are many suggestions on the Interne.

Does anybody have tried and tested method to display the message box AFTER the query has finished refreshing.

As always, thanks in advance.

 

[Kerryx]

Sub Optimise(Flag As Boolean)
On Error Resume Next
F = Not Flag
Application.ScreenUpdating = F
Application.DisplayAlerts = F
Application.EnableEvents = F
Application.DisplayStatusBar = F
ActiveSheet.DisplayPageBreaks = F
If F = True Then
Application.Calculation = xlCalculationAutomatic
Else
Application.Calculation = xlCalculationManual
End If
On Error GoTo 0
End Sub
Sub Update()
'switch off updating to stop screen flickering
Optimise (True)
SheetList = Array("Sheet1", "Sheet2", "Sheet3")
For Count = 0 To UBound(SheetList)
ActiveWorkbook.RefreshAll
DoEvents

,Rest of your code


'allow screen to update now
Optimise (False)
'reprotect the modified sheets
For Count = 0 To UBound(SheetList)
DoEvents
'Sheets(SheetList(Count)).Protect
Next
MsgBox "Job Done"
End Sub

 

[JeffGrant]

Thanks Kerry.

However, I finally solved the issue by going int the properties of the Query itself and disabling the Background Refresh option.