Place this in the Thisworkbook module Ric

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value < "" Then
On Error Resume Next
ActiveSheet.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

If you change A1 in each sheet the tab name will change to the cell value



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ric" wrote in message ...
I want to run a macro in excel, such that when a name is typed into a cell
and Return pressed the name becomes the sheet name...

I have used: ActiveSheet.name = ActiveCell.value to do the naming

Ideally this would work with the down arrow or tab as well - or even better,
whenever the cell 'loses focus'.

I hope this is as blindingly simple as I feel it should be...

Thanks in advance

Ric

This is the standard defintion for the event. When you select the event
from the module dropdowns at the top (workbook object in left dropdown,
SheetChange in right dropdown) this is what is entered (if you can't select
it, you are in the wrong module).

the Sh argument holds a reference to the worksheet that contains the range
(target) that triggered the event.

--
Regards,
Tom Ogilvy


"Ric" wrote in message
...
Ron

That's brilliant - thanks very much!

I can see how it all works except one thing: What does "ByVal Sh As
Object"
do?

Thanks again

Ric

"Ron de Bruin" wrote in message
...
Place this in the Thisworkbook module Ric

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Address = "$A$1" Then
If Target.Value < "" Then
On Error Resume Next
ActiveSheet.Name = Target.Value
On Error GoTo 0
End If
End If
End Sub

If you change A1 in each sheet the tab name will change to the cell
value



--
Regards Ron de Bruin
(Win XP Pro SP-1 XL2002 SP-2)
www.rondebruin.nl



"Ric" wrote in message
...
I want to run a macro in excel, such that when a name is typed into a
cell
and Return pressed the name becomes the sheet name...

I have used: ActiveSheet.name = ActiveCell.value to do the naming

Ideally this would work with the down arrow or tab as well - or even
better,
whenever the cell 'loses focus'.

I hope this is as blindingly simple as I feel it should be...

Thanks in advance

Ric