Results 1 to 13 of 13
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Dynamic Worksheet Tab Names (XP)

    Is there a way to automatically change the Worksheet Tab Names when you pupulate, say cell H14 ??
    I have these big workbooks and the name of the sheet is in cell H14. (This is a linked cell, not sure if that makes a difference)
    I know I can right click and all that, but doing that with 150 sheets is a waste of time when there is a faster and error proof solution. Does anyone know if there is a shortcut or a simple macro ??

    I have tried

    Sub myTabName()
    ActiveSheet.Name = ActiveSheet.Range("A1")
    End Sub

    but that only works when I run the macro and not automatically

    My macro knowledge isn't the greatest

    Thanks

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

    Re: Dynamic Worksheet Tab Names (XP)

    You could use an event handler to change the worksheet name if the user types a name into cell H14, but you say that H14 is a linked cell, so I assume that the user isn't supposed to type in it. Can you provide more information? In what way is H14 linked?

  3. #3
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Worksheet Tab Names (XP)

    The workbook is an automised invoice system.
    I have this "personalise" page on which the user can personalise the invoices with their own information.
    Then I have 50 invoice pages
    When the 50 invoices are used, all they have to do is change the numbers on the personalise page to the next 50 numbers and all 50 sheets are changed.
    The invoice number is cell H14
    I thought it would be a good idea if the tabs changed automatically as well, but apparently its not as easy as I would have hoped.

    Hope this explains it

    Must admit never heard of event handler. My friends always come to me with excel questions, but when I ask something in the lounge I realise how little I know.

    So again, thanks for all your help

    Thanks

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

    Re: Dynamic Worksheet Tab Names (XP)

    So if I understand correctly, the user changes one or more cells on the "personalise" worksheet to generate new invoice numbers, and this should automatically change the names of the corresponding worksheets. Does the user have to change 50 cells on the "personalise" worksheet in order to generate 50 new invoice numbers, or does it work differently?

  5. #5
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Worksheet Tab Names (XP)

    Yes, that is how it works...

    Cheers

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

    Re: Dynamic Worksheet Tab Names (XP)

    Can you tell us
    - the exact name of the "personalise" sheet.
    - which cells are changed by the user to get a new invoice number.
    - which worksheet corresponds to which cell on the "personalise" sheet.

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

    Re: Dynamic Worksheet Tab Names (XP)

    In addition to Hans' questions, can you tell us exactly what is in cell H14 on the invoice sheets?
    Legare Coleman

  8. #8
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Worksheet Tab Names (XP)

    Good morning, (6am here now) a late reply... time zones and all that

    Sheets at this stage are called
    Personalise
    101, 102 etc till 150

    Cell H14 is linked to the personalise sheet. The range on the personalise sheet is
    E6 - E20 numbers 101 - 115
    F6 - F20 numbers 116-130
    G6-G20 numbers 131-145
    H6-H10 numbers 146-150

    Thanks

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

    Re: Dynamic Worksheet Tab Names (XP)

    You keep telling us that cell H14 is linked to the personalise sheet. We keep asking HOW cell H14 is linked to the personalize sheet. What EXACTLY does cell H14 contain? I am going to take a guess that cell H14 on the sheet named 101 contains the formula =Personalise!E6, and cell H14 on the sheet named 102 contains the formula =Personalize!E7, etc. If that guess is correct, then you can place the following code in the worksheet calculate event routine for each of the sheets named 101, 102, ... 150:

    <pre>Private Sub Worksheet_Calculate()
    If Me.Range("H14").Value <> "" Then
    Me.Name = Me.Range("H14").Value
    End If
    End Sub
    </pre>


    You will need to put that code into the event routine for all of those sheets.

    The attached workbook shows how it works.
    Legare Coleman

  10. #10
    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: Dynamic Worksheet Tab Names (XP)

    Instead of adding the code to each of the multiple sheets, I suggest adding this to the workbook object. It acts the same but ignores the personalise sheet, and does not require multiple copies of the same code...

    <pre>Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    With Sh
    If .Name <> "Personalise" Then
    If .Range("H14").Value <> "" Then
    .Name = .Range("H14").Value
    End If
    End If
    End With
    End Sub</pre>


    Steve

  11. #11
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Worksheet Tab Names (XP)

    Thanks for the macros.

    I will respond in more detail to the questions of Legare Coleman in about 10 hours or so. I'm at the clients premises at the moment and internet use is a huge no no.

    Cheers

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

    Re: Dynamic Worksheet Tab Names (XP)

    Yes, that does work better. I actually tried that before my last post, but for some reason it did not work then. I just did it again, and it now works fine. I don't know what I did wrong last time. I have attached my previous workbook with the appropriate change.
    Legare Coleman

  13. #13
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Dynamic Worksheet Tab Names (XP)

    That works like a treat... The first macro worked the best for me.
    Thanks sooo much for helping me

    Cheers

Posting Permissions

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