Worksheet Event Deactivate

[Bengt]

I noticed a funny thing about the Deactivate-Event, and I was just curious whether someone else has noted the same, or if I am simply doing anything wrong.

I have this code:

Private Sub Worksheet_Deactivate()
Dim CurrentSheet As String
CurrentSheet = ActiveSheet.Name
MsgBox "You are deactivating worksheet " & CurrentSheet
End Sub

I have attached this code to Sheet1. When I leave Sheet1 to go to Sheet2, the code runs, but the value of CurrentSheet is not Sheet1 but Sheet2. It seems to me then, that this code is executed when Sheet2 is activated, rather than when Sheet1 is deactivated.

Another observation is its cousin, the Activate-Event.

I have this code:

Private Sub Worksheet_Activate()
CurrentSheet=ActiveSheet.Name
MsgBox "Welcome to " & CurrentSheet
End Sub

If Sheet2 is active and I click on Sheet1, The value of CurrentSheet Sheet1. This leads me to believe that the Activate-event and the DeActivate-event is really the same thing: Deactivate is triggered when the next sheet becomes active, as does Activate.

Am I wrong here.

Bengt

Running Excel2003 under W7.

 

[mikerickson]

If you work from Workbook_SheetDeActivate event in ThisWorkbook, you get the Sh argument.

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

    MsgBox "from " & Sh.Name & " To " & ActiveSheet.Name
    
    If Sh.Name = "KeyWorksheet" Then
        Rem do something
    End If
End Sub