The activate event will fire when the sheet becomes active. You can make the sheet active by clicking the sheets tab or use PageUp/Down to move through the sheets.

Once the sheet is active the event will not fire again, even if you use code.

' no effect 
Activesheet.activate