Macro to Wait for Query to Run

This requires code. I have done this using a userform that shows a "Processing..." message. I have also used progress bars but that is only really helpful for long waits. Another approach is to create a graphic on the page with a "busy" message that is normally not visible, but is made visible by the VBA code and then hidden again when done.

To do this with a userform, you would create a modeless form (if you allow it to be modal then nothing will execute while it's showing) giving whatever message you want to give. Let's suppose we call the form userformBusyMessage. At the beginning of the macro run by the button you would add the line

Please Login or Register  to view this content.

then at the end of the macro, just before switching to the other page, you would use

Please Login or Register  to view this content.

P.S. This doesn't sound codeless: ...a button named "Run" that has a macro attached to it that executes the query then switched to another sheet which I will call "search results."

Originally Posted by SP Brian

Could you please explain modeless forms to me? I am not familiar with this. I'm very new to macros and any kind of advanced functionality.

Also, it is not codeless - it is just codeless development since I do not have programming experience.

This might be a little intimidating if you don't really want to get into coding. If this sounds like too much, try the graphic approach (I got that from someone else, I'll have to look it up to give proper credit).

Even simpler, you can even designate a cell to contain your message and then populate that cell from code then clear it when the job it done. To get a little fancier you could create a new worksheet, display the message, then delete the worksheet.

If you want to delve into UserForms, you can bring up the VBA development window with ALT-F11. On the left side, you will see a folder structure, including a folder for the workbook you currently have open. If you right-click on that folder, you can select Insert, and then select UserForm from the submenu. This will create a sub-item for UserForm1 in the folder structure. You can select it, and then press F4 to get a list of its attributes. The first attribute is the name, which is what you should change first to something meaningful. Farther down is an attribute called ShowModal. If it is TRUE (default), then the form will be modal. That means that Excel will not allow the user to select anything outside that form nor allow any code to run as long as the form is being shown. If you set it to FALSE, the form becomes modeless, which means that while the form is being shown, code continues to run and the user can select cells, do menu commands, etc. (Modal and modeless are programming terms that can apply to all windows in the Windows environment.)

If you double-click on UserForm1, you will get a graphical editor that allows you to define what the form will look like. In your case you just need to go to the toolbox and select Label, drop one on the form, then edit it to your text ("Busy..." or whatever.)

Once you done all that you can add the code I provided above. I named my form userformBusyMessage.

This is not really that hard but is a bit of a bump to get over the first time you do it.

Hello SP Brian,

I am guessing you are have set up an External Query. This will hide the Query properties and events from VBA. If the Query is setup in VBA then you will access to the AfterRefresh event. This event signals the end of the refresh and the macro could then be run. The way it is now you will never truly know when the refresh has completed.

I would have your code alter the caption of the button to something like "Processing...", change the pointer to an hourglass, then run the query but specifying Backgroundquery:= False so that the code only proceeds when the query has run. Finally, reset the cursor and the button caption.
We'd need the workbook to provide specific code, or at least need to know more about the button.
You can also trap the AfterRefresh event as Leith suggested, but it takes a little more effort.

Originally Posted by SP Brian

...executing the query...

After reading subsequent posts I am now curious--what do you mean by "query"? I am not experienced with using database tables and queries within Excel so maybe my answer is not the most appropriate one.

Originally Posted by SP Brian

Can someone explain how I can tell the macro to only switch to the search results sheet after the refresh has completed / executed?

Isn't that what we tried to do?

Here's a hint: post your code and it will be much easier for someone to adapt it!

On the sheet with the query output, right-click the data range, and uncheck the 'Allow background query' option. Your code should then be fine.

On the sheet where the query output is, right-click one of the outout cells and choose 'Data range properties', then uncheck the background refresh option.

Yep, but I need to know what kind of button it is - from the Forms toolbar or the Control Toolbox?

Something like:

Please Login or Register  to view this content.