Naming sheets automatically (Excel 97)

Topic

New Reply

We’re creating a workbook for contract persons to enter information. There’s a cell for them to enter a company name, and we want that name to become the name of the worksheet tab.

Also, if someone were to enter a different company name in the master cell (at a later date), we not only want to rename the tab, but also have the former name display somewhere on the worksheet.

Can someone help me?

Thanks for any help!

Reply | Quote

Replies

Edited to cater for an error such as deleteing contents of A1 – A.C.

Try the following example which saves the existing name in cell B1 and applies the contents of A1 as the new name to the active sheet. This happens anytime a change is made to A1.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error GoTo Finish
    Application.EnableEvents = False
    If Not Intersect(Target.Cells(1), [A1]) Is Nothing Then
        [B1] = ActiveSheet.Name
        ActiveSheet.Name = [A1].Text
    End If
Finish: Application.EnableEvents = True
End Sub

That code must me placeed in the codepane of the ThisWorkbook object, and will apply to all sheets in the active workbook. The references to A1 and B1 can be altered to suit your requirements.

If you do not wish to apply the procedure to all worksheets, try the following variation. To enter this code right click on the sheet tab(s) of the sheet(s) you want the code to apply to, and select View Code and place the code there :

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo Finish
    Application.EnableEvents = False
    If Not Intersect(Target.Cells(1), [A1]) Is Nothing Then
        [B1] = Me.Name
        Me.Name = [A1].Text
    End If
Finish: Application.EnableEvents = True
End Sub

Andrew C

Reply | Quote