tab name equal to cell value (97)

Topic

New Reply

tab name equal to cell value
I

Reply | Quote

Replies

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:

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

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.

Reply | Quote

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

Reply | Quote

thanks John your suggestion did the trick.

Reply | Quote

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.

Reply | Quote

This modification to John’s code will set the name to the first 31 characters if the length is more than 31:

Sub NameTab()
'    Dim shtWS As Worksheet
'    For Each shtWS In ActiveWorkbook.Worksheets
'        shtWS.Activate
        If ActiveSheet.Range("A1").Value  "" Then
            If Len(ActiveSheet.Range("A1").Value) > 31 Then
                ActiveSheet.Name = Trim(Left(ActiveSheet.Range("A1").Value, 31))
            Else
                ActiveSheet.Name = ActiveSheet.Range("A1").Value
            End If
        End If
'    Next shtWS
End Sub

Reply | Quote

thank you very much Legare worked beautifully!

Reply | Quote