Vba Help :)

[mak]

I have set bA to autoselect markets 1 min before the off

How can I call 2 macros (MyMacro1 - MyMacro2) just once when the market change?

[osknows]

maybe something similar to this - note cell Q1 in excel must equal Y to run the code

Code: Select all


Dim nextracetrigger As Integer


Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

       
    'forward quick pick race on after race ended - NOTE CAN BE TURNED ON/OFF USING Y IN CELL Q1
    With ThisWorkbook.Sheets(Target.Worksheet.Name)
   
    'forward market if market suspended
    If .Range("E2").Value = "In Play" And .Range("F2").Value = "Suspended" And UCase(.Range("Q1").Value) = "Y" And nextracetrigger = 0 Then
        nextracetrigger = 1
        lastrace = .Range("A1").Value
        .Range("Q2").Value = -1
       
    'forward market if market closed
    ElseIf .Range("F2").Value = "Closed" And UCase(.Range("Q1").Value) = "Y" And nextracetrigger = 0 Then
        nextracetrigger = 1
        lastrace = .Range("A1").Value
        .Range("Q2").Value = -1
       
    'catches last closed market at end of day and updates balance
    ElseIf .Range("F2").Value = "Closed" And UCase(.Range("Q1").Value) = "Y" And nextracetrigger = 1 Then
        nextracetrigger = 2
        .Range("Q2").Value = -6
       
    'catches last closed market at end of day and resets refresh rate to 1
    ElseIf .Range("F2").Value = "Closed" And UCase(.Range("Q1").Value) = "Y" And nextracetrigger = 2 Then
        nextracetrigger = 2
        .Range("Q2").Value = 1
   
    End If
   
    If lastrace <> .Range("A1").Value Then
        nextracetrigger = 0
       
        'macros to run when event changes
        MyMacro1
        MyMacro2
    End If
   
    End With
   
   
Application.EnableEvents = False
end if

end sub


[osknows]

sorry add 'lastrace' also with the other DIM statement

Code: Select all

Dim nextracetrigger As Integer
Dim lastrace As String


[mak]

Os thanks but is not working

Please note that I autoselect markets and I don't want to go in play...

I thought it could be an easy thing to do...

[osknows]

Code: Select all

Dim lastrace As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

    If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
       
        'macros to run when event changes
        MyMacro1
        MyMacro2
    End If
   
    End With
   
   
Application.EnableEvents = False
End If

End Sub


[mak]

Sorry, maybe i am doing something wrong, but it doesn't work also

I deleted -- End with

[mak]

Os
is working only when i first log BA to excel
afterwards is not calling the macros

[osknows]

sorry! This should work

Code: Select all

Dim lastrace As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

    If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
        lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
        'macros to run when event changes
        MyMacro1
        MyMacro2
    End If

   
Application.EnableEvents = False
End If

End Sub


[mak]

not yet
the Red line is red also in the code...

Dim lastrace As String

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count = 16 Then

Application.EnableEvents = False

If lastrace <> ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
lastrace = ThisWorkbook.Sheets(Target.Worksheet.Name).Range("A1").Value Then
'macros to run when event changes
MyMacro1
MyMacro2
End If


Application.EnableEvents = False
End If

End Sub

[osknows]

remove then on the red line. That's what you get trying to write code direct onto a forum

[mak]

today is not my day
still can't figure it out

still working only one time

[osknows]

try changing the last Application.EnableEvents = False

to Application.EnableEvents = true

[mak]

that's better

but now
it keep firing mymacros without stopping...

[osknows]

I'm not sure? If you're happy to send me your code or worksheetI'll have a look

os

[mak]

Os
you've got mail...