-1 Trigger Help

[Yorkie]

Hi Guys

I have a list of events in the quick pick list. I then have a cell in sheet1 that when the time to the start is less than 2 minutes and certain conditions are met turns to 1. These conditions can be the bet i want to place has been placed or the odds are too low or the spread is too large etc.
I then have a small piece of vba that populates Q2 with -1 when the cell in excel turns to 1 to move onto the next event.

The problem i have is that everytime the excel cell turns to 1 and therefore the Q2 -1 is triggered instead of moving onto the next event it moves on 2 events.
If anyone can tell me how i can stop this happening i'd be grateful.

The vba i use if it helps is

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If Workbooks("Trial1.xlsm").Sheets("Sheet1").Range("AJ5").Value = 1 Then
Workbooks("Trial1.xlsm").Sheets("Sheet1").Range("Q2").Value = -1

End If

Application.EnableEvents = True

End Sub

Cheers

Yorkie

[GaryRussell]

-1 sets a flag that tells BA to move to the next market. This flag is checked every second, but in the meantime everything else continues, it does not wait for the market change. Therefore your code needs to wait for the market to change. If you change it as follows it should be ok.

Code: Select all

Dim marketChanging as Boolean,currentMarket as String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then Exit Sub
Application.EnableEvents = False

If Workbooks("Trial1.xlsm").Sheets("Sheet1").Range("AJ5").Value = 1 Then
   If not marketChanging Then
      marketChanging=True
      currentMarket=[A1]
      Workbooks("Trial1.xlsm").Sheets("Sheet1").Range("Q2").Value = -1
   Else
      If [A1]<>currentMarket then marketChanging=False
   End If
End If

Application.EnableEvents = True

End Sub

[Yorkie]

Cheers Gary