Tab Name

[mrs Doyle]

I am sure I saw the answer to this the other month but I can't find it at the moment.

I want the tab name to be picked up from the spreadsheet.
EG if I type "ASSETS" in cell A1 I would like the tab name to pick it up

hope this makes sense
thanks

 

[Mudface]

You need to use a worksheet Change event macro to do this. Right-click on the sheet tab, choose View Code and copy and paste in the code below. The error trap is there in case you type in an illegal name or try to rename as an existing worksheet:-

Private Sub Worksheet_Change(ByVal Target As Range)

With Target
    If .Count = 1 Then
        If .Address = "$A$1" Then
            On Error Resume Next
            Me.Name = .Value
        End If
    End If
    If Err Then MsgBox "Unable to rename worksheet as " & .Value
    On Error GoTo 0
End With

End Sub

 

[Duncgee]

Hi Mudface

I used your code and it works a treat.

How can I modify it to work on each sheet in the workbook

 

[Mudface]

Instead of placing the code in the worksheet module, place this in the ThisWorkbook module: -

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)

With Target
    If .Count = 1 Then
        If .Address = "$A$1" Then
            On Error Resume Next
            Sh.Name = .Value
        End If
    End If
    If Err Then MsgBox "Unable to rename worksheet as " & .Value
    On Error GoTo 0
End With

End Sub

 

[Duncgee]

Most excellent Mudface. Again works beautifully. Many thanks.

Apologies to mrs Doyle for butting in.

 

[Duncgee]

Can you tell me what is the reason for the line "Me.Name = .Value " in the code for a worksheet, in comparison with "Sh.Name = .Value " for that for the workbook?