How can i get my macro to run corretly when the worksheet deactivates

[tonywatsonhelp]

Hi Everyone,

got a problem here.

I have 35 sheets all of which I need to run the same macro when I exit them.
but when I exit them they are no longer the active sheet so it all goes wrong.
i'm hoping there might be a way to change activesheet to something else and solve this any ideas?
below is the trigger and the macro

thanks

Tony

Private Sub Worksheet_Deactivate()
Call Employees
End Sub

Sub [LEFT][COLOR=#222222][FONT=Verdana]Employees[/FONT][/COLOR][/LEFT]()
TRow = 64
If ActiveSheet.Range("Y" & TRow) = "Acceptable" Then Exit Sub
LrowRD = Sheets("Employees").Cells(Rows.Count, "D").End(xlUp).Row + 1
If LrowRD < 6 Then
LrowRD = 6
End If
Sheets("Employees").Range("D" & LrowRD).Value = ActiveSheet.Range("D1").Value
Sheets("Employees").Range("E" & LrowRD).Value = ActiveSheet.Range("E" & TRow).Value
Sheets("Employees").Range("F" & LrowRD).Value = ActiveSheet.Range("W" & TRow).Value - ActiveSheet.Range("W" & TRow).Value - ActiveSheet.Range("W" & TRow).Value
End Sub

 

[Steve_]

Add this code to EVERY sheet in your workbook...

Private Sub Worksheet_Activate()
    curSheet = ActiveSheet.Name
End Sub
Private Sub Worksheet_Deactivate()
    Exit_Code curSheet
End Sub

And now add the below code to any other module in your project...

Public curSheet
Function Exit_Code(shName)
    MsgBox shName & " says, BYE!"
End Function

This should get you there..or close enough to finish it off.

 

[tonywatsonhelp]

HI Steve,
Sure, I'll play around with it look like it should work great thanks

Tony

 

[Fluff]

Another option, rather than putting code in each sheet, just put this in the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Call Employees(Sh)
End Sub

And use this for the sub

Sub Employees(Ws As Worksheet)
TRow = 64
If Ws.Range("Y" & TRow) = "Acceptable" Then Exit Sub
LrowRD = Sheets("Employees").Cells(Rows.Count, "D").End(xlUp).Row + 1
If LrowRD < 6 Then
LrowRD = 6
End If
Sheets("Employees").Range("D" & LrowRD).Value = Ws.Range("D1").Value
Sheets("Employees").Range("E" & LrowRD).Value = Ws.Range("E" & TRow).Value
Sheets("Employees").Range("F" & LrowRD).Value = Ws.Range("W" & TRow).Value - Ws.Range("W" & TRow).Value - Ws.Range("W" & TRow).Value
End Sub

 

[Steve_]

Another option, rather than putting code in each sheet, just put this in the ThisWorkbook module

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
Call Employees(Sh)
End Sub

This is actually the PERFECT solution. I have had issues with it before though when programmatically switching between sheets.

 

[Fluff]

That's just one of the draw-backs of any Auto Event and why I tend to use them sparingly.