Make macro wait for process to complete

Hi all, I'm using a pre-made spreadsheet from my stock broker (Interactive Brokers) that retrieves and displays real-time quotes, and allows one to retrieve historical stock data, among other things. I've created a little macro within it to try and automate some common tasks I do everyday - basically I want to retrieve a year of daily stock quotes for "stock 1" from IB's servers, then have the macro wait for the retrieval to be done and written to the spreadsheet (takes anywhere between 10 and 30 secs). After that's all done, then I want to do the same for "stock 2". Then when that's done, the macro proceeds to go ahead and do some calcs on stock1 and stock2. The problem is after my stock1 data request, the macro just keep on trucking through to the next commands while stock1's data retrival is still going on.... so things are getting all balled up. How can I get my macro to wait until stock1's data retrieval is all done?

Any help is greatly appreciated!

Thanks!
Shawn

Thanks JBeaucaire, but the length of time this data retrieval takes varies - could be 10 seconds, could be 30 seconds or so, could even be more. During this whole time, the cursor changes to an hourglass. Is there a way to detect just when the process finishes and then have my macro immediately continue after that?

Thanks
Shawn

shg, I think this might work! That's a good idea. Once cell J254 is written to, then the retrieval process is over. How does this Change event thing work? Can you give me an idea how I would code it and how I would check for a change in J254, and if no change then to just wait?

So I guess there's no "Wait UNTIL" type of thing in VBA eh?

Thanks!
Shawn

thanks shg, I'll try it. So therefore I need to split up my one big macro into 2 (or more) macros)?

thanks shg.

Hey, what about the idea of checking for the cursor to stop being a hourglass. I found a thread here about that and tried the code:

Do While Application.Cursor = xlWait
Application.Wait (Now + TimeValue("0:00:15"))
Loop

but it gave a runtime error of some sort. This command not allowed in VBA maybe?

Thanks
Shawn

well, on 2nd thought shg, I don't know if your proposed solution is going to work for me. The sheet that has cell J254 on it only gets created itself during my data retrieval process - it doesn't even exist prior to that. Dang....

Even if the worksheet has not been created yet? I'm still a little confused, I tried things out last night and it didn't seem to work. My 1st macro goes as follows:

Sub FirstMacro()
...
...
Application.Run "'macrotest.xls'!Sheet12.requestHistoricalData"
End Sub

Then just below that I put your code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$J$254" Then myNextmacro
End Sub

Then below that I have:

Sub myNextmacro()
...
...
...
End Sub


When you offered me the code for the Change event, you mentioned "Code goes in the sheet module", but I didn't quite understand that. The sheet that I'm looking for the cell change in has not been created yet at that point, so how can I add the code into that sheet's module. So I just added your code right in the same module as where I have the original calling macro, I don't know if that's correct or not.
Also in your code where it says "Private Sub Worksheet_Change", how does this know how to refer to my new sheet that has not yet been created? How do I tell this Change event the name of the new sheet?

Thanks!
Shawn

Well, basically my workbook is what I described in my original post - it's a pre-made workbook from by broker - InteractiveBrokers. It uses DDE to retrieve live realtime stock data from IB's servers. It has several sheets on it, all with their own functions (placing orders, showing realtime account balances, etc), but for my problem here, I'm only interested in the one worksheet called "Historical Data". From here I can specify a stock symbol (ex. MSFT), and also some parameters on what sort of historical data I want (daily, by the hour, by the minute, etc) and also how long a period I want the historical data for (1 year, 1 month, 1 day, etc.). In my case, I want a year's worth of daily data figures for MSFT. Once I click the macro button to do this retrieval, the workbook automatically creates a brand new sheet called MSFT and writes all the retrieved data onto it. This takes about 10 to 30 seconds and the hourglass cursor is showing the whole time until it's done. When it's finally done, I am placed in the newly created MSFT sheet with the retrieved data displayed.
Because I am asking for 1 year's worth of data exactly, the final cell of data written is always J254 on the newly created sheet.
Every morning, I need to do this same process for about 10 to 15 stocks, so that's why I'd like to automate it in a macro.

Shg, I'm not sure what you mean by "code tags"... how do I do this?

Thanks
Shawn

thanks shg, I'll try that, but I don't really see where the "ThisWorkbook" module is....? I have only Module1 and Module2. Module2 is where is placed my macro I first tried to make (by using 'Record Macro')

Thanks
Shawn

also shg, where does Sub Workbook_SheetChange get called from, where do pass "MSFT" into the parameter 'Sh'? I don't see that.

Sorry for all these seemingly dumb questions!

Thanks
Shawn

Please Login or Register  to view this content.

OK, sorry shg. My questions again:

- I don't really see where the "ThisWorkbook" module is in my VBA Editor....? I have only Module1 and Module2. Module2 is where my original macro got placed (by using 'Record Macro'). In Module 2 I have only - my original macro (Firstmacro), MyNextMacro, and the code for your Sub Workbook_SheetChange.

- also shg, where does Sub Workbook_SheetChange get called from, where do I pass "MSFT" into the parameter 'Sh'? I don't see that.

Thanks!
Shawn

Thank you for your help on this shg.

Cheers
Shawn