Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    tab name equal to cell value (97)

    tab name equal to cell value
    I

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

    Re: tab name equal to cell value (97)

    You can't directly. However, you can put the following code in the Worksheet Change event routine to make the name on the Tab whatever is in A1:

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

    Legare Coleman

  3. #3
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    Or in the Activate event:
    Private Sub Worksheet_Activate()
    On Error Resume Next
    me.name=range("A1").Value
    End Sub

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

    Re: tab name equal to cell value (97)

    Won't that miss changing the tab if you modify A1 until the next time you deactivate and the reactivate the sheet?
    Legare Coleman

  5. #5
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    Yes, but I didn't think the user wanted it to be so dynamic -- was just providing another way.

  6. #6
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    Neither suggestion seems to be working I thought the activateEvent suggestion would definitely work but not even that does and there is a value in A1

  7. #7
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    Maybe you have events disabled?
    Put this piece of code somewhere where you know it'll run:
    Application.EnableEvents = True

  8. #8
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    how would i re-enable without the code you provided as i don't do a lot of excel programming i'm talking some menu option that will re-enable the events.

  9. #9
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    thanks John your suggestion did the trick.

  10. #10
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: tab name equal to cell value (97)

    You may not have the event selected properly. Also, if the source cell is empty, a blank WS name is not permitted, so the code errors out. Here's how it can be done manually, the commented code will act on all WSheets in the WB:

    Sub NameTab()
    ' Dim shtWS As Worksheet
    ' For Each shtWS In ActiveWorkbook.Worksheets
    ' shtWS.Activate
    If ActiveSheet.Range("a1").Value <> "" Then ActiveSheet.Name = ActiveSheet.Range("a1").Value
    ' Next shtWS
    End Sub
    -John ... I float in liquid gardens
    UTC -7ąDS

  11. #11
    Star Lounger
    Join Date
    Jun 2001
    Posts
    91
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    If you didn't turn it off, it's on each time you start Excel. However, you can use alt/F11, then ctrl/G (brings up the "immediate" pane window), then simply type and enter
    Application.EnableEvents = True
    then Alt/Q to return to Excel.

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

    Re: tab name equal to cell value (97)

    Do you have it working now? I was not sure from your messages. The reason I asked is that one possible reason that it is not working is that you didn't get the code into the event routine in the code module behind the worksheet. It is also possible that you didn't change cell A1 after inserting the code (if you used the change event), or deactivate and reactivate the worksheet (if you used the activate event). If you still need help getting it to work, let us know.
    Legare Coleman

  13. #13
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    no Legare I was careful to be sure I changed the cell references to where my data actually sat (B1 in my case) as well as put it in the proper sheet & event I was testing it in. All in all I got something that worked so that

  14. #14
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: tab name equal to cell value (97)

    In code where you don't know what the Application.EnableEvents setting is it's probably easiest to do it this way:

    Dim boolEnEv As Boolean 'create a variable to store the setting
    boolEnEv = Application.EnableEvents 'store the setting
    Application.EnableEvents = True 'set EnableEvents to True, i.e., turn it on
    <code continues here>
    Application.EnableEvents = boolEnEv 'reinstate original setting

    The code is redundant if the setting is already True, but also permits you to turn EnableEvents on and off in the intervening code but eventually restore the original setting.
    -John ... I float in liquid gardens
    UTC -7ąDS

  15. #15
    4 Star Lounger
    Join Date
    Jun 2001
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: tab name equal to cell value (97)

    hey John, some of my values are greater than 31 char (the excel limit) what to do about those cuz the code halts on that sheet & won't rename the rest of the workbook.

Page 1 of 2 12 LastLast

Posting Permissions

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