Results 1 to 10 of 10
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Names (2000 SR1)

    Is there a way to have the tab name match a cell value in its worksheet?

    EX: Cell A1 has the text value of "Data". Is there a way for the worksheet tab to also be called data?

    Thanks!

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

    Re: Tab Names (2000 SR1)

    See <post:=519,555>post 519,555</post:>. The code in that post uses cell A5. To make it use cell A1, change both instances of A5 to A1.

  3. #3
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (2000 SR1)

    Got it. Code works. Just thought of something else though. I won't want the users to be able to change the tab name manually. So, if I protect the workbook, is there anything I can do so that the code will still work? It won't update when protected.

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

    Re: Tab Names (2000 SR1)

    Sorry, you've lost me there. If the cell cannot be changed, you don't seem to need the code. Can you explain what you want to accomplish?

  5. #5
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (2000 SR1)

    My apologies. That did sound strange.

    The user will be given the protected Excel sheet that has one tab for each employee to track their sales. The user will enter an employee's name in cell B3 and I want the tab to match their name, and I don't want them to be able to click on the tab and change it manually. In other words, I want the tab to always match cell B3. This is for some conformity throughout the multiple managers using the sheet.

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

    Re: Tab Names (2000 SR1)

    If you protect the structure of the workbook (Tools | Protection | Protect Workbook...), the user can't change the sheet names manually. You can unprotect the workbook temporarily in the code:

    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Not Range("A1") = "" Then
    ActiveWorkbook.Unprotect
    Me.Name = Range("A1")
    ActiveWorkbook.Protect Structure:=True
    End If
    End If
    End Sub

  7. #7
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (2000 SR1)

    Thanks! I must have explained it better. That was exactly what I was looking for!

    One more question. This works fine if the workbook is protected with no password. (Actually it overrides any password and sets it to no password). If I were to password protect the workbook, could the code be modified to automatically insert my password upon unprotecting and put the same password back in for protecting? I know I forgot to specify that I wanted it password protected.

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

    Re: Tab Names (2000 SR1)

    Change Hans' code to:

    <code>
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("A1")) Is Nothing Then
    If Not Range("A1") = "" Then
    ActiveWorkbook.Unprotect Password:="MyPassword"
    Me.Name = Range("A1")
    ActiveWorkbook.Protect Structure:=True, Password:="MyPassword"
    End If
    End If
    End Sub
    </code>
    Legare Coleman

  9. #9
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (2000 SR1)

    Much appreciated as always!! I need to learn VB (somehow got skipped in college by C++). Any recomendations of some VB basics?

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

    Re: Tab Names (2000 SR1)

    See the thread starting at <post:=435,353>post 435,353</post:> for some suggestions and links.

Posting Permissions

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