Examples of Activation Events

Activate and deactivate events in a sheet

Private Sub Worksheet_Activate()

    MsgBox "you just activated -" & ActiveSheet.Name

End Sub

 

Private Sub Worksheet_Activate()

    Range("a1").Activate

End Sub

 

Private Sub Worksheet_Deactivate()

    MsgBox "you must stay on sheet1"

    Sheets("sheet1").Activate

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    MsgBox Sh.Name

End Sub

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

    If TypeName(Sh) = "Chart" Then

        OldSheet.Activate

        MsgBox "Sorry, you can't activate any charts."

    End If

End Sub

Dim OldSheet As Object

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

Set OldSheet = Sh

End Sub

 

Private Sub Workbook_SheetActivate(ByVal Sh As Object)

If TypeName(Sh) = "Chart" Then

MsgBox "Sorry, you can’t activate any charts."

OldSheet.Activate

End If

End Sub

 

Private Sub Workbook_SheetActivate()

    ActiveWindow.WindowState = xlMaximized

End Sub

 

Private Sub Workbook_SheetDeactivate()

    ThisWorkbook.Windows(1).WindowState = xlMinimized

End Sub