Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman



"michaelspearin" wrote in message
...

There's a minor gotcha in this code. By using Me.Name = Target.Value,
rather than the cell reference, an error will occur if multiple cells
are selected (since Name can't be assigned an array).

A variation of this, with some additional error checking, can be seen at

http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Norman Jones" wrote:

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman

Hi JE,

You are correct and, in any case, I prefer your version.

Thank you.

---
Regards,
Norman



"JE McGimpsey" wrote in message
...
There's a minor gotcha in this code. By using Me.Name = Target.Value,
rather than the cell reference, an error will occur if multiple cells
are selected (since Name can't be assigned an array).

A variation of this, with some additional error checking, can be seen at

http://www.mcgimpsey.com/excel/event...efromcell.html



In article ,
"Norman Jones" wrote:

Hi Michael,

Right-Click the worksheet tab, select the 'View Code' option and paste
the
following code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Cleanup:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
With Target
Me.Name = .Value
End With
End If
Cleanup:
Application.EnableEvents = True
End Sub

Change A1 to the required cell reference,

---
Regards,
Norman