Excel Message Box

[sadavies2906]

I have a very simple macro that refreshes a Power Query, the users are happy with the output but the refresh takes between 1 and 3 minutes to finish in which time the screen is inactive.

I'm after a way to show a very simple message box that just says "File is currently refreshing"

Once the macro has finished I wanted a second message box to say "File has been updated" and then for the user to click an "OK" button to continue using the file.

I have seen progress bar tutorials but these tend to work on a macro that is performing calculations on a range of cells so is able to calculate a % complete not sure if this is possible with this a time of query can change.

Any help appreciated.

Sub REFRESH()
'
' REFRESH Macro
'
Application.ScreenUpdating = False
Sheets("RAW DATA").Select
Selection.ListObject.QueryTable.REFRESH BackgroundQuery:=False
Sheets("Work to List EP").Select
ActiveSheet.PivotTables("Work to List EP").PivotCache.REFRESH
Sheets("Overview").Select
Range("A1").Select
Application.ScreenUpdating = True
End Sub

 

[alansidman]

Insert a line of code

Application.StatusBar = "Processing.  Please be Patient"

 

[Mentor82]

Hi,
Type in the following code before you start refreshing the data.

Application.StatusBar = "Type your note to inform user about data updating..."

At the end of your code write the following line so the status bar test disappears after the data update.

Application.StatusBar = false

regards,
Sebastian

 

[sadavies2906]

Thank you both that works. Is there anything that is more in your face was hoping to get an actual message box?

Found this code on the Microsoft website but don't know if I can wrap it around or inside my original refresh macro?

With Worksheets(1).QueryTables(1)
If .Refreshing Then MsgBox "Query is currently refreshing: please wait"
Else
.Refresh BackgroundQuery := False
.ResultRange.Select
End If
End With