Name worksheet tab after cell data

[belvy123]

Hi All

I am working on a new spreadsheet for work.
I want to be able to have the worksheet tabs rename themelves after data is
entered in a particular cell eg ("G9"). Also I would like this to work on all
worksheets created in the workbook so if I add more sheets to the book I will
be able to have each renamed to that particular cells data.
Also is there a way to have the sheet names displayed alphabetically and
updated if a sheet is renamed

Thanks for the help

 

[Gord Dibben]

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, ActiveSheet.Range("G9")) Is Nothing Then Exit Sub
On Error GoTo CleanUp
Application.EnableEvents = False
With Target
If .Value <> "" Then
ActiveSheet.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

This is sheet event code that goes into Thisworkbook module so's it works on all
sheets.

Right-click the Excel Icon left of "File" on the menubar and "View Code". Paste
the above code into that module.

As far as sorting sheets, you could call Chip Pearson's sortworksheets macro
from the above after the End With

http://www.cpearson.com/excel/sortws.aspx


Gord Dibben MS Excel MVP