none
Macro Triggered by Cell Content RRS feed

  • Question

  • Hello All

    First, this is the sample file:
    http://www.mediafire.com/file/ebd0q6hy6sq84gq

    Not in this sample file, but in the actual one there is a formula in the "Status" column (DA) with conditions to show "CANCEL" when is time to cancel the open orders.
    We want to automatically call the "CancelOrder_Click()" macro (for that row/open order) as soon as  "CANCEL" appears in that "Status" column.

    Note: right now if we select a "CANCEL" cell (like the current DA12 or DA21 and click on the "CANCEL" button above (that calls the mentioned macro), it cancels that order.


    Can you please help?



    • Edited by NicoPer Sunday, March 5, 2017 3:51 AM
    Saturday, March 4, 2017 7:57 PM

Answers

  • This may be a dumb question but, is there a way to replace the active cell part of the code for specific sheet/cells references?

    Not a dumb question at all. In fact it is almost never necessary to Activate worksheets or cells/ranges in VBA code and the preferred method is to fully reference the worksheet and cell/range. I only suggested it to try to get the code to work for you because I believed that the worksheet and range had previously been activated and the code was using ActiveCell.

    However, lets ensure we are on the same wave length. Within problem code:

    • Are the following 2 references referring to cells on the "Conditional Orders" worksheet?
    • Do they refer to the same row in which "CANCEL" appears?

    (Note the references are extracted from the full lines of code)

    Cells(ActiveCell.Row, COLUMN_ORDERID)
                        
    Cells(ActiveCell.Row, Columns(COLUMN_ORDERSTATUS).Column)

    If the answer to both questions above is "Yes", then try the following code. When the ranges are assigned to a range variable as per the example then the range variable contains the actual range, the worksheet where the range is located and the workbook name so it is no longer necessary to use more than the range variable to reference the required range. When creating the range variables as I have done, it does not matter which worksheet is the ActiveSheet because it will reference the worksheet in the With statement.

    Sub ProcessTimer()
        Dim rngStatus As Range
        Dim rngCancel As Range
        Dim strFirstAddr As String
        Dim rng1 As Range
        Dim rng2 As Range
       
        DoEvents        'Finish any existing processing currently taking place
       
        With ThisWorkbook.Sheets("Conditional Orders")
            Set rngStatus = .Range(.Cells(7, "DA"), .Cells(.Rows.Count, "DA").End(xlUp))
        End With
       
        With rngStatus
            Set rngCancel = .Find(What:="CANCEL", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                               
            If Not rngCancel Is Nothing Then
                strFirstAddr = rngCancel.Address
       
                Do
                    'MsgBox only used for testing. Remove from production workbook
                    'MsgBox "Cell being processed is " & rngCancel.Address(0, 0) & vbCrLf & _
                            "Value in cell is " & rngCancel.Value & vbCrLf & _
                            "Cells(rngCancel.Row, Q).Value = " & Cells(rngCancel.Row, "Q").Value
                           
                    '***********************************************************************
                    'OssieMac cannot test the code between the asterisk lines
                   
                    With Worksheets("Conditional Orders")
                        Set rng1 = .Cells(rngCancel.Row, COLUMN_ORDERID)
                        Set rng2 = .Cells(rngCancel.Row, Columns(COLUMN_ORDERSTATUS).Column)
                    End With
                   
                   
                    If Not (objTWSControl Is Nothing) Then
                        If objTWSControl.m_isConnected Then
                   
                            Call objTWSControl.m_TWSControl.CancelOrder(rng1.Value)
                   
                            rng2.Value = STR_ORDER_CANCELLED
                        Else
                            MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
                        End If
                    Else
                        MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
                    End If
                   
                    '************************************************************************
               
                    Set rngCancel = .FindNext(rngCancel)
                    If rngCancel Is Nothing Then Exit Do
                Loop While rngCancel.Address <> strFirstAddr
               
            End If
           
        End With
           
        dteNextTime = Now + TimeValue("00:00:10")       'Time intervals currently 10 seconds
       
        Application.OnTime _
            EarliestTime:=dteNextTime, _
            Procedure:="ProcessTimer", _
            Schedule:=True
     
     
    End Sub


    Regards, OssieMac

    • Marked as answer by NicoPer Wednesday, March 8, 2017 3:55 AM
    Monday, March 6, 2017 11:45 PM

All replies

  • I am not able to fully test the code below because I do not have the reference for TWS. However, I have tested that the correct cell with "CANCELLED" is being addressed so try it and see how you go.

    Ensure you have a Back Up of your workbook before testing.

    The code goes in the Worksheets module (where you have your present code running from a button) and should run each time a cell in the column Id updated to "CANCELLED".

    The code could possibly give problems if it runs while other code is running.

    Private Sub Worksheet_Change(ByVal Target As Range)
       
        Dim rngUpdatedCell As Range
       
        Set rngUpdatedCell = Range(Target.Dependents.Address)

        If Not Intersect(rngUpdatedCell, Range("DA:DA")) Is Nothing Then
           
            On Error GoTo ReEnableEvents
            Application.EnableEvents = False
           
            If UCase(rngUpdatedCell.Value) = "CANCEL" Then
               
                'Following line used for testing. Uncomment to test if correct cell being processed
                'MsgBox rngUpdatedCell.Address & " : Value = " & rngUpdatedCell.Value
               
                If Not (objTWSControl Is Nothing) Then
                    If objTWSControl.m_isConnected Then
               
                        'Following row "ActiveCell" replaced with "rngUpdatedCell"
                        Call objTWSControl.m_TWSControl.CancelOrder(Cells(rngUpdatedCell.Row, COLUMN_ORDERID).Value)
               
                        'Following row "ActiveCell" replaced with "rngUpdatedCell"
                        Cells(rngUpdatedCell.Row, Columns(COLUMN_ORDERSTATUS).Column).Value = STR_ORDER_CANCELLED
                    Else
                        MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
                    End If
                Else
                    MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
                End If
               
            End If
        End If
       
    ReEnableEvents:
        If Err.Number <> 0 Then
            MsgBox "Error occurred in Private Sub Worksheet_Change"
        End If
       
        Application.EnableEvents = True
       
        DoEvents    'Catch up on processing before running further code

    End Sub


    Regards, OssieMac

    Sunday, March 5, 2017 5:01 AM
  • Hi OssiMac, and thank you very much for your reply and code.

    I´ve tested it and it doesn´t seem to work, but I think I know why. 
    I´m very sorry, I should have mentioned that we use dynamic RTD data changing by the second (the status formula is derived from that) and it doesn´t work with Worksheet Change. 

    I know it should work with Application.OnTime tough.
    Can you please tell me what to change to run it with Application.OnTime say every 10 secs?

    Sunday, March 5, 2017 6:41 PM
  • I have uploaded a test workbook to the following link. The workbook only contains an extract from the workbook that you uploaded because I don't have the TWS stuff and it will not run and test for me.

    I have inserted a start timer button and a stop timer button just so you can see what the code does.

    You will need to decide where and how you want to call the process to start the timer; maybe in the workbook open event. (Run the Sub ProcessTimer to start the timer). (To stop the timer run Sub StopProcessTimer). Both subs can be run at any time but ensure that the Stop timer is called from the workbook close event otherwise if not closed, it will continue to run and open the workbook again.

    https://1drv.ms/u/s!ArAXPS2RpafCh0pyzd7nIAPw8D3t

    Below is a copy of the code in the workbook. Note the comment beside Public variable which is the first line. 

    Public dteNextTime As Date      'Must be a public variable at very top of STANDARD module

    Sub ProcessTimer()
        Dim rngStatus As Range
        Dim rngCancel As Range
        Dim strFirstAddr As String
       
        DoEvents        'Finish any existing processing currently taking place
       
        With ThisWorkbook.Sheets("Conditional Orders")
            Set rngStatus = .Range(.Cells(7, "DA"), .Cells(.Rows.Count, "DA").End(xlUp))
        End With
       
        With rngStatus
            Set rngCancel = .Find(What:="CANCEL", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                               
            If Not rngCancel Is Nothing Then
                strFirstAddr = rngCancel.Address
       
                Do
                    'MsgBox only used for testing. Remove from production workbook
                    MsgBox "Cell being processed is " & rngCancel.Address(0, 0) & vbCrLf & _
                            "Value in cell is " & rngCancel.Value
                       
                    '***********************************************************************
                    'OssieMac cannot test the code between the asterisk lines
                   
                    '    If Not (objTWSControl Is Nothing) Then
                    '        If objTWSControl.m_isConnected Then
                    '
                    '            'Following row "ActiveCell" replaced with "rngCancel"
                    '            Call objTWSControl.m_TWSControl.CancelOrder(Cells(rngCancel.Row, COLUMN_ORDERID).Value)
                    '
                    '            'Following row "ActiveCell" replaced with "rngCancel"
                    '            Cells(rngCancel.Row, Columns(COLUMN_ORDERSTATUS).Column).Value = STR_ORDER_CANCELLED
                    '        Else
                    '            MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
                    '        End If
                    '    Else
                    '        MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
                    '    End If
                   
                    '************************************************************************
               
                    Set rngCancel = .FindNext(rngCancel)
                    If rngCancel Is Nothing Then Exit Do
                Loop While rngCancel.Address <> strFirstAddr
               
            End If
           
        End With
           
        dteNextTime = Now + TimeValue("00:00:10")       'Time intervals currently 10 seconds
       
        Application.OnTime _
            EarliestTime:=dteNextTime, _
            Procedure:="ProcessTimer", _
            Schedule:=True
     
     
    End Sub

    Sub StopProcessTimer()
        'This code to be called from the workbook close event
        'Returns error if timer has already stopped
        'and hence the On Error handling.
        On Error Resume Next
       
        DoEvents      'Finish any existing processing currently taking place
       
        Application.OnTime _
        EarliestTime:=dteNextTime, _
        Procedure:="ProcessTimer", _
        Schedule:=False
       
        On Error GoTo 0
     
    End Sub


    Regards, OssieMac

    Monday, March 6, 2017 2:12 AM
  • I meant to include the following in my reply before.

    If as you say "we use dynamic RTD data changing by the second" then you might find that it interferes with the On Time. If so, I don't know if you can do this, but think about also calling the RTD from the timer sub so that it is processed first and then the "CANCEL" code is processed.


    Regards, OssieMac

    Monday, March 6, 2017 2:58 AM
  • Great, thank you very much. The popup messages are being shown when needed. 

    I´m currently running other timer each 5 secs and it calls 3 different macros (working perfectly with RTD).
    As I need this macro to run non-stop from the open, I just added the call to this macro there too.

    So the popups are working perfectly now, but when I replace them (comment & uncomment) 
    with the cancel macro code is giving me this error:





    Can you see what´s wrong?
    Monday, March 6, 2017 3:51 AM
  • I cannot test so I can't say for sure what is causing the problem.

    The code in the workbook you uploaded for me refers to ActiveCell in that line of code. I assumed that the ActiveCell was the one with "CANCEL" in it on worksheet "Conditional Orders". Is this correct? If this is correct then Activate worksheet "Conditional Orders" in the Timer code and change your code back to reference ActiveCell. If my assumption is not correct and it refers to an ActiveCell on another worksheet then that worksheet needs to be Activated and change the code back to ActiveCell.

    See modified code below to Activate the Worksheet to which ActiveCell in the following lines of code references and also the Activate the required cell.


    Sub ProcessTimer()
        Dim rngStatus As Range
        Dim rngCancel As Range
        Dim strFirstAddr As String
       
        DoEvents        'Finish any existing processing currently taking place
       
        With ThisWorkbook.Sheets("Conditional Orders")
            Set rngStatus = .Range(.Cells(7, "DA"), .Cells(.Rows.Count, "DA").End(xlUp))
        End With
       
        With rngStatus
            Set rngCancel = .Find(What:="CANCEL", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                               
            If Not rngCancel Is Nothing Then
                strFirstAddr = rngCancel.Address
       
                Do
                    'MsgBox only used for testing. Remove from production workbook
                    'MsgBox "Cell being processed is " & rngCancel.Address(0, 0) & vbCrLf & _
                            "Value in cell is " & rngCancel.Value
                       
                    '***********************************************************************
                    'OssieMac cannot test the code between the asterisk lines
                   
                    'Following line needs to Activate the workssheet to which ActiveCell below refers
                    Worksheets("Conditional Orders").Activate
                   
                   'Add the following line only if ActiveSheet is "Conditional Orders" and
                    'ActiveCell in following lines of code refers to the cell containing "CANCEL"
                    'Otherwise delete the line
                    rngCancel.Activate
                   
                    If Not (objTWSControl Is Nothing) Then
                        If objTWSControl.m_isConnected Then
                           
                            Call objTWSControl.m_TWSControl.CancelOrder(Cells(ActiveCell.Row, COLUMN_ORDERID).Value)
                       
                            Cells(ActiveCell.Row, Columns(COLUMN_ORDERSTATUS).Column).Value = STR_ORDER_CANCELLED
                        Else
                            MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
                        End If
                    Else
                        MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
                    End If
                   
                    '************************************************************************
               
                    Set rngCancel = .FindNext(rngCancel)
                    If rngCancel Is Nothing Then Exit Do
                Loop While rngCancel.Address <> strFirstAddr
               
            End If
           
        End With
           
        dteNextTime = Now + TimeValue("00:00:10")       'Time intervals currently 10 seconds
       
        Application.OnTime _
            EarliestTime:=dteNextTime, _
            Procedure:="ProcessTimer", _
            Schedule:=True
     
     
    End Sub


    Regards, OssieMac

    Monday, March 6, 2017 6:54 AM
  • Ok, still getting the same error, with or without the rngCancel.Activate line.

    I have almost zero VBA knowledge (just trying to learn) but for what I see and you tell, it looks like a reference problem. (There are a lot of sheets in the original file and the macro should work no matter the sheet or cell we´re in).


    The original Cancel macro is in the conditional orders sheet, and it works when you click the button on an open order row (not cell). So when you´re on any cell of an open order row and click the cancel button, it cancels that open order.

    But we copied the code to the Module1 and called that macro from a timer in ThisWorkbook open, so the reference could be lost there.

    This may be a dumb question but, is there a way to replace the active cell part of the code for specific sheet/cells references?

    The popups are working perfectly after all. 

    How can we solve this? 
    Monday, March 6, 2017 9:40 PM
  • This may be a dumb question but, is there a way to replace the active cell part of the code for specific sheet/cells references?

    Not a dumb question at all. In fact it is almost never necessary to Activate worksheets or cells/ranges in VBA code and the preferred method is to fully reference the worksheet and cell/range. I only suggested it to try to get the code to work for you because I believed that the worksheet and range had previously been activated and the code was using ActiveCell.

    However, lets ensure we are on the same wave length. Within problem code:

    • Are the following 2 references referring to cells on the "Conditional Orders" worksheet?
    • Do they refer to the same row in which "CANCEL" appears?

    (Note the references are extracted from the full lines of code)

    Cells(ActiveCell.Row, COLUMN_ORDERID)
                        
    Cells(ActiveCell.Row, Columns(COLUMN_ORDERSTATUS).Column)

    If the answer to both questions above is "Yes", then try the following code. When the ranges are assigned to a range variable as per the example then the range variable contains the actual range, the worksheet where the range is located and the workbook name so it is no longer necessary to use more than the range variable to reference the required range. When creating the range variables as I have done, it does not matter which worksheet is the ActiveSheet because it will reference the worksheet in the With statement.

    Sub ProcessTimer()
        Dim rngStatus As Range
        Dim rngCancel As Range
        Dim strFirstAddr As String
        Dim rng1 As Range
        Dim rng2 As Range
       
        DoEvents        'Finish any existing processing currently taking place
       
        With ThisWorkbook.Sheets("Conditional Orders")
            Set rngStatus = .Range(.Cells(7, "DA"), .Cells(.Rows.Count, "DA").End(xlUp))
        End With
       
        With rngStatus
            Set rngCancel = .Find(What:="CANCEL", _
                                LookIn:=xlValues, _
                                LookAt:=xlWhole, _
                                SearchOrder:=xlByRows, _
                                SearchDirection:=xlNext, _
                                MatchCase:=False)
                               
            If Not rngCancel Is Nothing Then
                strFirstAddr = rngCancel.Address
       
                Do
                    'MsgBox only used for testing. Remove from production workbook
                    'MsgBox "Cell being processed is " & rngCancel.Address(0, 0) & vbCrLf & _
                            "Value in cell is " & rngCancel.Value & vbCrLf & _
                            "Cells(rngCancel.Row, Q).Value = " & Cells(rngCancel.Row, "Q").Value
                           
                    '***********************************************************************
                    'OssieMac cannot test the code between the asterisk lines
                   
                    With Worksheets("Conditional Orders")
                        Set rng1 = .Cells(rngCancel.Row, COLUMN_ORDERID)
                        Set rng2 = .Cells(rngCancel.Row, Columns(COLUMN_ORDERSTATUS).Column)
                    End With
                   
                   
                    If Not (objTWSControl Is Nothing) Then
                        If objTWSControl.m_isConnected Then
                   
                            Call objTWSControl.m_TWSControl.CancelOrder(rng1.Value)
                   
                            rng2.Value = STR_ORDER_CANCELLED
                        Else
                            MsgBox (STR_TWS_CONTROL_NOT_CONNECTED)
                        End If
                    Else
                        MsgBox (STR_TWS_CONTROL_NOT_INITIALIZED)
                    End If
                   
                    '************************************************************************
               
                    Set rngCancel = .FindNext(rngCancel)
                    If rngCancel Is Nothing Then Exit Do
                Loop While rngCancel.Address <> strFirstAddr
               
            End If
           
        End With
           
        dteNextTime = Now + TimeValue("00:00:10")       'Time intervals currently 10 seconds
       
        Application.OnTime _
            EarliestTime:=dteNextTime, _
            Procedure:="ProcessTimer", _
            Schedule:=True
     
     
    End Sub


    Regards, OssieMac

    • Marked as answer by NicoPer Wednesday, March 8, 2017 3:55 AM
    Monday, March 6, 2017 11:45 PM
  • Misread the question. Please ignore.
    • Edited by NicoPer Tuesday, March 7, 2017 6:27 PM
    Tuesday, March 7, 2017 2:25 AM
  • Irrespective of what constants you use for them, if they are referencing cells on the same row as "CANCEL" then the code I gave you should be OK. If you want to change the names of constants then I will leave that up to you.

    Regards, OssieMac

    Tuesday, March 7, 2017 4:45 AM
  • Sorry, I misread the question in prev message. 
    The answer is yes: both are referring to the "Conditional Orders" sheet
    and to the same row in which "CANCEL" appears. 


    I tested the code and am getting the same error but on a different line. Here:


    Any ideas on what could it be?

    Tuesday, March 7, 2017 9:44 PM
  • It can't find the Constant COLUMN_ORDERID. Are you testing with a workbook where the constants are not declared?

    Also if you use Option Explicit and then select Debug -> Compile before running the code then it should tell you that the variable is not defined.


    Regards, OssieMac

    Tuesday, March 7, 2017 11:17 PM
  • The Constant COLUMN_ORDERID is declared at the start of the "Conditional Orders" sheet code. 

    The column "S" is hidden in the sheet.


    Got me thinking that maybe is the way that TWS wrote it.

    This is part of the Sub Worksheet_Calculate (within the Conditional Orders sheet) for example:

    Sub Worksheet_Calculate()
    Application.EnableEvents = False
        On Error Resume Next
        Dim validCond As Boolean
        Dim rowMod As Integer
        Dim action As String
        For rowMod = ROW_FIRST To ROW_LAST
            validCond = Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column).value
            action = Cells(rowMod, Columns(COLUMN_COND_ADDMOD).column).value

            ' modify order?
            If action = "MOD" Then
                ' if the order is filled, clear the conditional order
                Dim numFilled As Integer, numRemaining As Integer
                numFilled = Cells(rowMod, Columns(COLUMN_FILLED).column)
                numRemaining = Cells(rowMod, Columns(COLUMN_REMAINING).column)
                If numFilled <> 0 And numRemaining = 0 Then
                    Range(Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column), Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column)).ClearContents
                Else
                    'if condition is met...
                    If validCond Then
                        ' copy the conditional order to the real order
                        Cells(rowMod, Columns(COLUMN_ACTION).column).value = Cells(rowMod, Columns(COLUMN_COND_ACTION).column).value
                        Cells(rowMod, Columns(COLUMN_TOTALQTY).column).Formula = Cells(rowMod, Columns(COLUMN_COND_TOTALQTY).column).Formula
                        Cells(rowMod, Columns(COLUMN_ORDERTYPE).column).value = Cells(rowMod, Columns(COLUMN_COND_ORDERTYPE).column).value
                        Cells(rowMod, Columns(COLUMN_LMTPRICE).column).Formula = Cells(rowMod, Columns(COLUMN_COND_LMTPRICE).column).Formula
                        Cells(rowMod, Columns(COLUMN_AUXPRICE).column).value = Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column).value
                        ' clear the conditional order
                        Range(Cells(rowMod, Columns(COLUMN_COND_STATEMENT).column), Cells(rowMod, Columns(COLUMN_COND_AUXPRICE).column)).ClearContents
                        ' place the order
                        Cells(rowMod, Columns(COLUMN_SYMBOL).column).Activate
                        Call PlaceModifyOrder_Click
                    End If
                End If



    They do something similar to what we´re trying to do here (once, without the timer).

    Does it help?


    • Edited by NicoPer Wednesday, March 8, 2017 2:02 AM
    Wednesday, March 8, 2017 1:35 AM
  • The Constant COLUMN_ORDERID is declared at the start of the "Conditional Orders" sheet code. 

    That is probably the problem. If you have moved the code to another module then it will not find the constant declared in the worksheets module. Constants and Variables declared at the top of a worksheet module are only available to subs in that same module.

    Constants and/or Variables that need to be used throughout the project and in any module must be declared as Public in a STANDARD module like the following.

    Public Const COLUMN_ORDERSTATUS = "T"
    Public Const COLUMN_ORDERID = "S"


    Regards, OssieMac

    Wednesday, March 8, 2017 2:23 AM
  • Tried editing that part but got this error:

    maybe adding the reference in the module?


    Wednesday, March 8, 2017 2:50 AM
  • I very clearly stated in my previous post Constants and/or Variables that need to be used throughout the project and in any module must be declared as Public in a STANDARD module.

    Standard modules are the modules that get inserted when in the VBA editor you select menu item Insert -> Module

    Your error message indicates that you have attempted to declare it in an Object Module. Modules belonging to Worksheets and Userforms etc are Objects Modules.


    Regards, OssieMac

    Wednesday, March 8, 2017 3:18 AM
  • Finally!! Thanks a million OssieMac.

    You really helped me a lot. God bless your patience :)

     
    Wednesday, March 8, 2017 3:58 AM