Results 1 to 12 of 12
  1. #1
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    formulas to name tabs and custom header/footers (XP)

    I am trying to name a tab according the description in a cell in its corresponding sheet. i am also trying to insert a header according a cell reference. is this possible? any help would be greatly appreciated!

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

    Re: formulas to name tabs and custom header/footers (XP)

    For cell references in headers/footers, see this thread of yesterday/today: <post#=306547>post 306547</post#>.

    Would you like the tab to be renamed each time the cell changes, or when the user wants to update the name?

  3. #3
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    i the tab name to change each time the cell is changed in the sheet...thanks!

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

    Re: formulas to name tabs and custom header/footers (XP)

    Activate the Visual Basic Editor (Alt+F11)
    Activate the Project Explorer (Ctrl+R)
    If necessary, expand the workbook you are working on until you see the name of the worksheet you want to change the name of.
    Double click the name of the worksheet. You will see the code module belonging to the worksheet.
    Type or paste the following code into the module:

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

    Replace A1 by the address of the cell that contains the sheet name.

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    The following code, placed in the Worksheet Change event routing in the module behind the sheet in question will change the worksheet name to be the same as the contents of cell A1 on that sheet whenever A1 is changed. If cell A1 is cleared, the sheet name is not changed.

    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, ActiveSheet.Range("A1")) Is Nothing Then
    If ActiveSheet.Range("A1").Value <> "" Then
    ActiveSheet.Name = ActiveSheet.Range("A1").Value
    End If
    End If
    End Sub
    </pre>

    Legare Coleman

  6. #6
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    thank you for the very good directions for a person who never used VB before! that worked well. now i want to complicate it. can i concanetate somehow to draw tab renaming from 2 cells with a space between the cell contents? i want the tab to draw an item number from one cell, then an space, and then an item description from another cell. Thanks!!

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

    Re: formulas to name tabs and custom header/footers (XP)

    Say that the cells are A1 and B1. Modify the code as follows:

    Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A1:B1")) Is Nothing Then
    ActiveSheet.Name = Range("A1") & " " & Range("B1")
    End If
    End Sub

    You can replace A1 and B1 by any pair of adjacent cells (one next to the other or one below the other)

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    You really should check to make sure that he cells are not empty before setting the name.
    Legare Coleman

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

    Re: formulas to name tabs and custom header/footers (XP)

    You are quite correct. I was lazy.

  10. #10
    Super Moderator
    Join Date
    May 2002
    Location
    Canberra, Australian Capital Territory, Australia
    Posts
    5,054
    Thanks
    2
    Thanked 417 Times in 346 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    When using these macros, you'll need to keep in mind that none of the following is a valid character in a sheet name:
    : ? / * [ ]
    and Worksheet names are limited to 31 characters.

    You could use error checking in VBA to manage this. For example:
    If ActiveSheet.Range("A1:B1") <> Empty Then
    ActiveSheet.Name = Left(Trim(ActiveSheet.Range("A1").Value & " " & ActiveSheet.Range("B1").Value), 31)
    End If
    guards against A1 & B1 being empty, having repeated/leading/trailing spaces, or being too long.

    As for guarding against the invalid characters, you could modify the macro to either prevent them being entered in A1 or B1, or to simply ignore them when creating the sheet name.

    Cheers
    Cheers,

    Paul Edstein
    [MS MVP - Word]

  11. #11
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    You could just use something like this:

    On Error Resume Next
    ActiveSheet.Name = ActiveSheet.Range("A1").Value & _
    " " & ActiveSheet.Range("B1").Value
    On Error GoTo 0

    If the name is invalid, it just won't change it. It will just keep the current name.

    Steve

  12. #12
    New Lounger
    Join Date
    Aug 2003
    Posts
    16
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: formulas to name tabs and custom header/footers (XP)

    thank you everyone for all of the help!

Posting Permissions

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