Excel VBA user form open and background data refresh never takes place

Question

I have an excel spreadsheet running with a VBA user form (dialog) that is open all the time, waiting for use input.

I can see that the background data query refresh is running (continuously) at the bottom of the excel screen, but the data is never refreshed in the sheets until after the user form closes. The updates do not happen if the form is modal or not.

How can I have a user dialog open all the time and have the data connections to other spreadsheets refreshed periodically?? One thought I had was to programmatically close the user form once a day to enable the refresh, but don't really see a good way to do that either. I'd need to close the form, wait until all the data refreshes take place and then open the form back up.

Any ideas are appreciated

Answer 1

Hi,

Unfortunately, Excel user forms are typically modal, which means they pause VBA code execution until the user form is closed. This makes it difficult to keep a form open while running background tasks like data refreshes.

You could schedule VBA code to close the user form, trigger data refresh, and then reopen the user form at a predetermined time interval (for example, daily). You'd need to save the form's state (e.g., user inputs) before closing it and restore it when reopening it. This method may be a little complicated and, if not handled carefully, may result in data loss.

Best Regards.