code does not work anymore

[Wanna Learn]

Hello Excel 2002 - I have a spreadsheet that I rename every month with
the currents month's name,( e.g. Sales for July 2008 ) and has a worksheet
for each work day of the month and a summary tab at the end. I've been
using the following code but the code does not work anymore .
above the code first box is worksheet second box is Selection change
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With ActiveSheet
newname = Application.WorksheetFunction.Text(Range("J1"), "mm-dd-yy")
ActiveSheet.Name = newname
End With
End Sub

thanks in advance

 

[Bernie Deitrick]

You should use the change event, and check if cell J1 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$1" Then Exit Sub
On Error Resume Next
With Target.Parent
.Name = Format(.Range("J1"), "mm-dd-yy")
End With
End Sub

HTH,
Bernie
MS Excel MVP

 

[TomPl]

Use this if you want it to change any time someone clicks anywhere on the
worksheet:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim newname As String
With ActiveSheet
newname = ActiveSheet.Range("J1").Value & " " & CStr(Date$)
Debug.Print newname
ActiveSheet.Name = newname
End With
End Sub

Use this if you want the sheet name to change only when cell J1 is changed:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address <> "$J$1" Then Exit Sub
On Error Resume Next
With Target.Parent
.Name = ActiveSheet.Range("J1").Value & " " & CStr(Date$)
End With
End Sub