Naming sheet tabs

  • Thread starter Thread starter paula O.
  • Start date Start date
P

paula O.

I need to have the sheet tab name be the same as what
appears in a cell. I can't remember how to set that up.
Any help would be appreciated.
 
Try this

Sub test()
On Error Resume Next
ActiveSheet.Name = Range("A1").Value
On Error GoTo 0
End Sub
 
Here is some code posted by MVP Nick Hodge:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("A1")) Is
Nothing Then
Me.Name = Target.Value
End If
End Sub

Right-click the worksheet tab, View Code, and paste this
in the window. As Nick mentions, there is no error
checking and it won't work if illegal characters for a
worksheet name are keyed into A1.

HTH
Jason
Atlanta, GA
 
This will not blow if the you make the cell empty or if there is a cell value that you can't use as a sheet name.
Place this in the Thisworkbook module

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 of the workbook the tab name will change to the cell value
 
A rather fine point. Use Sh.Name instead of Activesheet.Name. If
there is some other change event that changes the activesheet, the
proposed code will change the name of *that* sheet. I just tried it
with a worksheet level change procedure that switched to another sheet
and the workbook sheetchange event. It changed the name of the wrong
worksheet.

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions
 
Back
Top