I duplicated your exact scenario, according to the articles on line the code is supposed to work.

Here is an inefficient, but works version of the code
Private Sub Worksheet_Activate()
MsgBox "Hello, you have selected the sheet"
ActiveSheet.Unprotect Password:="Password"
ActiveSheet.PivotTables(1).PivotCache.Refresh
ActiveSheet.Protect Password:="Password"
End Sub