Worksheet_change waits for you to type something. (Your code checks to see if
you typed something in J3--if not, it doesn't do anything.)

Since J3 is a formula, it's not changed by typing.

You could use the worksheet_calculate event, but I think I'd just start looking
at J2:

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "j2"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = format(Target.Value, "dd mm")
'me.name = target.text
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

You could use Target.Text if you formatted that cell the way you wanted.

Big Rick wrote:

Hello to all my good friends out there.

I have the following code in my worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
Const WS_RANGE As String = "j3"

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = Target.Value
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Which will alter the tab name of the worksheet with whatever is in cell J3.
If however the contents of j3 changes, the tab name doesn't change. Not when
I press f9, even though calculation is set to auto and doesn't even change
when saving and reopening the workbook. The only way it does change is when I
click on the cell itself and press enter again.
Is there a way to have the tab name change immediately after the cell change.

eg. j2 = 5/3/1999
j3 = =TEXT(J2,"dd mm")

When j2 changes the tab name doesn't change until I click on J3 and press
enter.

Your help is and always has been very much appreciated.
Thanking you in anticipation.

Regards
--
Big Rick

--

Dave Peterson