I did what you suggested and started from a completely blank worksheet and it
work!
But when i go back to the worksheet i am working on it does not work. Does
it matter that the cell i am trying to copy the name from (b5) is merged?
i am using this forumla
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Target.Count = 1 And Target.Address = "$B$5" Then
If Trim(Target.Text) <> "" Then Me.Name = Trim(Range("B5"))
End If
Application.EnableEvents = True
End Sub