Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Rename tab to specific cell value (Excel 2003 SP1)

    Hi all <img src=/S/hello.gif border=0 alt=hello width=25 height=29> ,

    Is it possible to rename the tab of a sheet (i.e. Sheet1) dynamically to the value of a specific cell, from the moment an entry is made in that specific cell ?
    I did a search on the previous posts, but the results all pointed in the opposite way (how to get the tab value into a specific cell) <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> .

    Any help ?

    MTIA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Rename tab to specific cell value (Excel 2003 SP1)

    Say that you want the name of the worksheet to be the value of cell B1 in that sheet.
    Right-click the sheet tab of the worksheet.
    Select View Code to open the worksheet code module.
    Copy the code below and paste it into the module.
    Switch back to Excel (Alt+F11)
    Type something in B1 and press Enter or Tab to see how it works.

    <img src=/w3timages/blueline.gif width=33% height=2>

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error GoTo ErrHandler
    If Not Intersect(Target, Range("B1")) Is Nothing Then
    If Not Range("B1") = "" Then
    Application.EnableEvents = False
    Me.Name = Range("B1")
    End If
    End If
    ExitHandler:
    Application.EnableEvents = True
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

    <img src=/w3timages/blueline.gif width=33% height=2>

    Note: in a worksheet module, Me refers to the worksheet.

  3. #3
    2 Star Lounger
    Join Date
    Apr 2003
    Location
    Kontich, Vlaanderen, Belgium
    Posts
    103
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Rename tab to specific cell value (Excel 2003

    Thank you Hans,

    I knew I could count on you. As usual your code works fine. <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>

    Best regards,

Posting Permissions

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