0

I have the following VBA that I wrote a while ago to display to the user that, on the button press, the data is being updated and finally show the date/time when the data was last refreshed.

Private Sub CommandButton1_Click()
Application.StatusBar = "Working on it!!"
Sheet1.Range("LastUpdateDate").Value = "Getting data..."
ActiveWorkbook.RefreshAll

Application.StatusBar = "Done!!"
Application.Cursor = xlNormal
Application.Wait (Now + #12:00:02 AM#)
Application.StatusBar = False
Sheet1.Range("LastUpdateDate").Value = "Last updated: " & Now
End Sub

This is fine if the query is fairly quick, but if the query takes a while (and especially if its a background query) the end message can show when the query is still running.

I'm wondering if its possible to get the message from the status bar ("Running Background Query...") and display it in "LastUpdateDate" until the query has completed?

dazzathedrummer
  • 511
  • 2
  • 10
  • 26
  • I believe Excel will do just that if you don't touch the status bar at all. – GSerg Sep 23 '19 at 13:32
  • Actually backgroundquery is named "background" because it runs in the background and your code does not wait at this line `ActiveWorkbook.RefreshAll` until the query is finished. Therefore this approach changing the status bar manually doesn't work. – Pᴇʜ Sep 23 '19 at 13:38
  • @GSerg Sorry - I meant in the 'LastUpdateDate' cell so the user sees it clearly. I'll update the question. – dazzathedrummer Sep 23 '19 at 13:39
  • @Pᴇʜ - I've updated the question, I was meant to ask how to show the status bar message in the named range while the query is running - I'm finding that users don't know that a query is running and ask me why their data hasn't changed. – dazzathedrummer Sep 23 '19 at 13:43
  • I don't like that duplicate, the accepted answer is blatantly wrong. I would handle the `Workbook_SheetTableUpdate` event and display the message from there. You will need to keep track of how many tables are still updating though (e.g. fill a list with all query tables when you click the button and remove each respective name from the list when the event fires for that table, and display the message when the list is empty). – GSerg Sep 23 '19 at 13:56
  • It's not a duplicate question! - I am asking how to display the message in the status bar on the sheet to make it more visible to users - I'm not asking how to wait until the query finishes! – dazzathedrummer Sep 23 '19 at 14:00
  • @GSerg Did you have a look at the answer with the most votes (instead of the accepted one) – Pᴇʜ Sep 23 '19 at 14:00
  • 1
    @dazzathedrummer You need to wait until the query finishes because otherwise, like you have observed, "the end message can show when the query is still running". There is no way to automatically redirect taskbar to a cell, and to do it manually, you need to wait for all queries to complete. – GSerg Sep 23 '19 at 14:11
  • @GSerg - Thanks, that answers my question! – dazzathedrummer Sep 23 '19 at 14:28

0 Answers0