Problem with calling next market

[mariner1]

I have a simple VB script that inserts -1 automatically when a race is suspended but it seems to call the next race at the same meeting rather than the next one in timed from the quick pick list.

Manually entering -1 seems to work fine however.

Would appreciate any input or help on this.

[GaryRussell]

-1 is removed after it has been actioned so it's possible your VBA is inserting it multiple times. Each refresh triggers multiple Worksheet_Change events. You should set a flag to record that the next market has been triggered and not insert -1 if the flag is set. Clear the flag when it is detected that the market has changed. Do you want some sample code?

[mariner1]

-1 is removed after it has been actioned so it's possible your VBA is inserting it multiple times. Each refresh triggers multiple Worksheet_Change events. You should set a flag to record that the next market has been triggered and not insert -1 if the flag is set. Clear the flag when it is detected that the market has changed. Do you want some sample code?

Some sample code would be a great help Gary, thanks

[GaryRussell]

Here's an example of how it can be done. Insert the following code into the sheet1 module.

Code: Select all

Dim marketChanging As Boolean
Dim prevMarket As String

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
    If Target.Columns.Count = 16 Then
        If Range("a1") <> prevMarket Then marketChanging = False
        prevMarket = Range("a1")
        If Range("f2") = "Suspended" And Not marketChanging Then
            marketChanging = True
            Range("q2") = -1
        End If
    End If
    Application.EnableEvents = True
End Sub

[gizzard]

I'm having similar problems but I'm sure that it's my feeble VB skills.

I've tried some similar code to that provided but cannot get it to work.
What seems to happen for me is that the statement

If Range("a1") <> prevMarket Then marketChanging = False

is ALWAYS False because on each call of the subroutine prevMarket has been re-initialised to "" and doesn't keep the value of Range("A1")

How do I get it to keep the value of the previous market?

[Bid]

You need to make sure that your declaration of the variable prevMarket is outside the function.

ie put it at the top of the module

[gizzard]

Thanks, Bid. As I mentioned, I'm a VB novice but I'll give that a go.

[osknows]

You should find everything you need here

http://gruss-software.co.uk/forum/viewtopic.php?t=3763