Cycling Through Pick List

[jrarmstrong]

Hi,

I have added the code below to the Place Bets sheet to cycle through the quick pick list at 10 second intervals using the Q2 -1 trigger.

It works but seems quite cumbersome and wondered if there is anything I should be doing to make it more efficient.

The aim is to cycle through all football markets and place bets if criteria are met. Often the market loads up in BA but takes quite a long time to replicate in Excel. It sometimes has not replicated before the next 10 second insertion of -1 in Q2.

Thanks,

James

Code: Select all

Option Explicit
   
Public runThem As Boolean
   
Sub start()
    runThem = True
    Call MyMacro
End Sub
   
Sub stop()
    runThem = False
End Sub
   
Sub startORstop()
    runThem = Not (runThem)
    Call MyMacro
End Sub
   
Sub MyMacro()
If runThem Then
        With Sheets("Market").Range("Q2")
        Application.EnableEvents = False
        .Value = --(Not (.Value))
        Application.EnableEvents = True
    End With
    Application.StatusBar = Time
   
    Application.OnTime Now + TimeValue("00:00:10"), "MyMacro"
End If
End Sub

[mak]

hi this is from Captain here
http://www.gruss-software.co.uk/forum/v ... php?t=5124


Dim counter As Currency

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Columns.Count <> 16 Then Exit Sub

counter = counter + 1

If counter > 10 And [J3].Value = "L" Then
counter = 0
[Q2].Value = -5
ElseIf counter > 10 Then
counter = 0
[Q2].Value = -1
End If


End Sub

[jrarmstrong]

Thanks mac i'll give it a go