Sounds like all you need is to place a workshet change event in your sheet module:
Private Sub Worksheet_Activate()
'Your code goes here
To access your sheet module, right click on your sheet tab, left click on View Code, and paste the procedure into the large white area that is the worksheet module. Press Alt+Q to return to the worksheet.