Change Name Of Worksheet On Open Without Macros

I'm trying to avoid using macros in my workbook, however, I'm unable to get my worksheets to change name (based on a named range) without use of them, as follows.

I've written the macro:


Sub MyMacro()
ActiveSheet.Name = Range("MyRange")
End Sub

and then dropped the following into the worksheet


Private Sub Worksheet_Activate()
Run "MyMacro"
End Sub

This works fine, but I'm trying to avoid using macros. When I try to place the following script in the worksheet private code, nothing happens:


Private Sub Worksheet_Activate()
ActiveSheet.Name = Range("MyRange")
End Sub