Problem with tab names equaling cell name.

[cole516gray]

I found a code to make the tab name equal a cell in the worksheet:

Private Sub Worksheet_Change(ByVal Target As Range)
 Const WS_RANGE As String = "ad1" '<== change to suit
 On Error GoTo ws_exit
 Application.EnableEvents = False
 If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
 With Target
 Me.Name = .Value
 End With
 End If
ws_exit:
 Application.EnableEvents = True
 End Sub

This worked great for the first worksheet, but it is not working for any of the other sheets. Can anyone tell me if I am missing something in the code?

 

[nigelk]

You're turning events off but only turning them on again if you encounter an error.

Try something along these lines:

Private Sub Worksheet_Change(ByVal Target As Range)

Const WS_RANGE As String = "ad1" '<== change to suit
On Error GoTo ws_exit


Application.EnableEvents = False

If Not Intersect(Target, Me.Range(WS_RANGE)) Is Nothing Then
With Target
Me.Name = .Value
End With
End If

Application.EnableEvents = True

Exit Sub



ws_exit:
Application.EnableEvents = True
MsgBox "Sheet NOT named."

End Sub

 

[cole516gray]

I am now copying the tabs and the tabs are initially being renamed, however if I change the cell name after the initial time of renaming the cell, the tab name is not adjusting accordingly. Is there a way to make it always adjust with the cell name or are there specific conditions it will work in?

 

[nigelk]

Slightly different., it doesn't rely on the name of the cell.

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Tcell As Range

Set Tcell = ActiveSheet.Range("AD1")

If Target <> Tcell Then Exit Sub

On Error GoTo ws_exit

Application.EnableEvents = False
ActiveSheet.Name = Tcell
Application.EnableEvents = True

Exit Sub

ws_exit:
Application.EnableEvents = True
MsgBox "Sheet NOT named."

End Sub

 

[nigelk]

This will only work with the sheet that the code is in by the way. It won't have any effect on other sheets that do not contain the above code.

 

[cole516gray]

Thanks for your help! It is working great!