Active Cell and Timers - Fix References

[nicoan]

Hello all


Working with the IB ActiveX demo here.

This is the sample file:
Active Cell - Fix References.xlsm

cond orders.jpg



If the DA column shows "Update" and we select that cell (ActiveCell) and click on the "Update" button (PlaceModifyOrder macro), it sends/resends that order to the broker.

We just want to do that automatically every 10 secs.

The VBA code structure is already working in the Module1 with timers and popups.


The CancelOrder macro is a working sample (but you won´t be able to test the TWS code as it needs the software).
If we replace the popus for the commented TWS code, when "Cancel" appears on the "DA" column it cancels the open order.


Can you please help?

[mdmackillop]

Use the OnTime recursive procedure

Sub PlaceOrderTimer()
    Application.OnTime Now + TimeValue("00:00:10"), "PlaceModifyOrder"
End Sub

Sub PlaceModifyOrder()
    'as your code
    
    'Restart timer
    PlaceOrderTimer
End Sub

[nicoan]

Hi mdmackillop and thank you for stepping in.


Where do I place that code, in the "Conditional Orders" sheet or in Module1?
There are a lot of sheets in the workbook and the macro should work no matter what sheet we´re in.


If I place it in Module1 as now, there´s still the Active Cell reference problem
as the PlaceModifyOrder macro uses it to know which row needs to be updated.
That reference is lost when we copy it to the Module1.


Am I correct? How can be done?

[mdmackillop]

I've not tried to interpret your code, but if Update button is executing your code correctrly, the following simply automates the clicking of the button. It could also be called by a WorkBook Open event.


The Update button should be changed to call PlaceOrderTimer (above)
Rename PlaceModifyOrder_Click() to PlaceModifyOrder
Add this at the end of the PlaceModifyOrder code to restart the timer period.
'Restart timer
PlaceOrderTimer

[nicoan]

The problem is that the Update button works with Active Cell.
Doing so will update the order row where the current active cell is.


But if we´re on another cell/sheet, it won´t update the needed orders
(the ones that have "Update" in the DA col of the "Conditional Orders" sheet).

[mdmackillop]

Rather than use ActiveCell use Find within the macro to locate the desired cell, allocate a variable to this and substitute for ActiveCell within the code.

[nicoan]

Cool. Can you tell me how?


I´m not a coder. I´ve tried a couple of things but nothing worked.

[nicoan]

Can anyone please help with this issue?
I´ve been struggling with it for two months now.


I highly appreciate your help.

[mdmackillop]

Best I can do as I can't test the code and there is too much to comprehend
Good luck!