Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Dec 2000
    Location
    Denver, CO
    Posts
    14
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Naming sheets automatically (Excel 97)

    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!

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Naming sheets automatically (Excel 97)

    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.<pre>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</pre>

    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 :<pre>

    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</pre>


    Andrew C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •