Excel Message Box

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
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
 

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

alansidman

Well-known Member
Joined
Feb 26, 2007
Messages
6,002
Office Version
  1. 365
  2. 2019
Platform
  1. Windows
Insert a line of code

VBA Code:
Application.StatusBar = "Processing.  Please be Patient"
 

Mentor82

Active Member
Joined
Dec 30, 2018
Messages
311
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
  2. Mobile
  3. Web
Hi,
Type in the following code before you start refreshing the data.

VBA Code:
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.
Code:
Application.StatusBar = false

regards,
Sebastian
 

sadavies2906

New Member
Joined
Feb 5, 2011
Messages
44
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,269
Messages
5,571,227
Members
412,372
Latest member
JON_ROCKS
Top