Load next market of quickpicklist at certain conditions

[iceman]

Hi,

Is it possible when a market is load in excel, to jump to next event of Quickpicklist at certain conditions?
For exemple if a market is load in excel, and there are no money matched, is it possible to load next market?

Thanks anyway

[GaryRussell]

Yes, but you will need to write some vba code to check the conditions and if met insert "-1" into cell Q2 to trigger the market change.

[iceman]

Ok, I'll wrote the conde in VBA.

Thanks Gary!

[GaryRussell]

You may find you get caught out by it changing market twice instead of once. Once you have inserted "-1" into cell Q2 you need to then wait for the market to change. See http://gruss-software.co.uk/forum/viewtopic.php?t=5163 for an example.

[iceman]

I have done a code for load nex market, it put -1 in Q2 at certain conditions (Cells(63, 4).Value = "jump"), it works but, when next market is load in excel, probably the -1 remain for a little fraction of seconds, an then the is load next market again.....
Is there any way to solve it? My macro is:

Code: Select all

Private Sub Worksheet_Calculate()
Static MyMarket As Variant
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
If [A1].Value = MyMarket Then
GoTo Xit
Else
        MyMarket = [A1].Value
        Worksheets("AUS-LayHorse").Activate
        Range("A5:P49") = ""
        'Range("A5:P49").Select
        'Selection.ClearContents
        Range("T5:Z49") = ""
        'Range("T5:Z49").Select
        'Selection.ClearContents
         Range("Q50:S50").Copy Range("Q5:S49")
        'Range("Q50:S50").Select
        'Selection.Copy
        'Range("Q5:S49").Select
        'Range("Q49").Activate
        'ActiveSheet.Paste
       
    If Cells(63, 4).Value = "jump" Then
        [Q2] = -1
    End If
End If
Xit:
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

[GaryRussell]

Code: Select all

Cells(63, 4).Value = "jump"

How is this cell being populated with "jump"? Is it done by a formula or code? Could it be that after the market has changed that it still contains "jump" so it inserts "-1" a second time?

[iceman]

the controll is with a formula in Sheet, so probably you're right.
When a new market is load the check remain....
I'm trying to solve tihis.
Thanks.