Help with code

[davez]

Hi all, hoping someone can help solve a problem - the worksheet change procedure below work fine & calls a macro at 120sec & 10sec before the jump time but will only run on the worksheet I have currently selected - any ideas on how to get it to run on worksheets I do not have selected? Thanks for any help provided.

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Columns.Count <> 16 Then

    Application.EnableEvents = False

    With ThisWorkbook.Sheets(Target.Worksheet.Name)
    Select Case .Range("$HM$3").Value
   
    Case 120
    Call Victab_odds_for_main_use
   
   
    Case 10
    Call Victab_odds_for_main_use

   
    End Select
    End With

    Application.EnableEvents = True
       
End If
End Sub


[osknows]

Hello,

I'm assuming that the procedure 'Victab_odds_for_main_use' writes to the sheet also?

You need to tell it which sheet to write to

Eg this will only write to the active sheet

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Call Victab_odds_for_main_use
End Sub

sub Victab_odds_for_main_use()
  range("A1").value  ="Called"
end sub

this will write to whichever sheet you pass

Code: Select all

Private Sub Worksheet_Change(ByVal Target As Range)
    Application.EnableEvents = False
   
    Call Victab_odds_for_main_use(Target.Parent)

    'or
    Set ws = Sheet3
    Call Victab_odds_for_main_use(ws)
   
    Application.EnableEvents = True
End Sub

Sub Victab_odds_for_main_use(ByVal ws As Worksheet)
    With ws
        .Range("A1").Value = "Called"
    End With
End Sub

[davez]

Thanks osknows, is there any chance you could amend the code I posted as I have tried to incorporate what you have posted without any luck.

I think the main problem I face is that as I have a sheet for each race & linked to BA, each worksheet name can be variable & I have been unable to work out how to reference 'this sheet'.

[davez]

cancel that osknows, ive worked it out, thanks for your help