Excel trigger after before moving to next market

[vanbuuts]

All, below is probably a familiar bit of code, it switches to the next market - for me based on cell AB1 = "OK"

I want to run a macro when cell Y1= "OK" - however, I'm not sure where to add this into the code below to ensure both macro's run each race. When I have tried to put this into the code below (in between the End If and enable events = true it runs once and then the macro to skip to the next race never fires again.

Looking for some help if possible to be able to run 2 macro's without any confliction of one stopping the other other working again

Code: Select all


Private Sub Worksheet_Change(ByVal Target As Range)

 If Target.Columns.Count = 16 Then
        Application.EnableEvents = False
       
           If [A1] <> currentMarket Then marketSelected = False
        currentMarket = [A1]
        If [AB1] = "OK" And Not marketSelected Then
            marketSelected = True
              ThisWorkbook.Sheets("Race1").Select
                                            [Q2] = -1
                                              End If
                                             
             
         
                                                         
                                                         
        Application.EnableEvents = True
       
End If

Code: Select all

If [Y1] = "OK" Then
           
           Call get_updates

[Captain Sensible]

In your current code the first If statement is controlling everything , it opens at

Code: Select all

If [A1] <> currentMarket

and ends, somewhat weirdly, after the

Code: Select all

 Application.EnableEvents = True

This means that unless [A1] <> currentMarket none of your code will be executed.

[Captain Sensible]

You could try something like this

Code: Select all

    Private Sub Worksheet_Change(ByVal Target As Range)

     If Target.Columns.Count = 16 Then
            Application.EnableEvents = False
           
            If [A1] <> currentMarket Then marketSelected = False:  currentMarket = [A1]
            If [Y1] = "OK" Then Call get_updates

                If [AB1] = "OK" And Not marketSelected Then
                marketSelected = True
                ThisWorkbook.Sheets("Race1").Select
                                                [Q2] = -1
                                                  End If
                                                 
                 
             
                                                             
                                                             
            Application.EnableEvents = True
           

End Sub







[Captain Sensible]

Missed out an end if, should be something like this. I haven't tested it , so check it works before betting with it. Also you should probably code it to the sheet rather than just having open references like [A1] etc as if you were looking at a different sheet when the code executed it would execute on whatever sheet you had open, i.e. sheet2 rather than the sheet1 you wanted.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

     If Target.Columns.Count <> 16 Then Exit sub

            Application.EnableEvents = False
           
            If [A1] <> currentMarket Then marketSelected = False:  currentMarket = [A1]
            If [Y1] = "OK" Then Call get_updates

                If [AB1] = "OK" And Not marketSelected Then
                marketSelected = True
                ThisWorkbook.Sheets("Race1").Select
                                                [Q2] = -1
                                                  End If
                                                 
                 
             
                                                             
                                                             
            Application.EnableEvents = True
           

End Sub



[vanbuuts]

Thanks very much that is helpful