Rename tab on cell event

  • Thread starter Thread starter John G.
  • Start date Start date
J

John G.

Looking for code that would simply, on the fly, depending what was entered in
a cell at anytime, any amount of times, the tab name would be renamed to what
the cell contents are.

I have seen some pretty elaborate ways of renaming tabs. I am new at Excel
and any help would be appreciated. Thanks!
 
Hi,

Right click your sheet tab, view code and paste this in then every tiome A1
is changed to a 'legal' worksheet name the sheet will be renamed.

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
ActiveSheet.Name = Range("A1").Value
enditall:
End Sub

Mike
 
Mike,

That works really slick. Question... If someone changes the cell to a
nothing or null value, it does not rename the sheet. Can there be a way to go
back to say "sheet 1" or "sheet 2" if cell becomes empty?

John G.
 
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo enditall
Application.EnableEvents = False
With Me
If .Range("A1") = "" Then
.Name = .Name
Else
.Name = .Range("A1").Value
End If
End With
enditall:
Application.EnableEvents = True
End Sub


Gord Dibben MS Excel MVP
 
Thanks Gord and Mike. Making life simpler, or complex, depends on what else
this leads to. Thanks a bunch!
 
Gord,
This returns the right value only the cell shows a square symbol between the
round number and the fraction. I tried to paste it here, but it did not come
over the same.
 
John

I think you have responded to the wrong posting.

No round numbers or fractions that I can see in my post.

But square symbols are usually linefeeds within a cell.


Gord
 
Great sub, however in MY case I need 4 sheets to be renamed based on cells in
sheet 1. Can anyone help?
 
Sorry, I should have been more specific.

I have a that has 8 sheets.

Sheet 1 is the master sheet.

All sheets have default names. (contractor1, contractor2....)

Once I enter the contractor name into the Master sheet (C4) I want sheet 2
to update to the value in sheet 1, C2. Similar for sheet 1, D2. And for
other cells in the master sheet.

thanks in advance.

dan
 
In the sheet code module, paste this in - change [A2] to your desired cell.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, [A2]) Is Nothing _
Or [A2] = "" Then Exit Sub
Dim wks As Worksheet
For Each wks In Sheets
If UCase([A2]) = UCase(wks.Name) Then
MsgBox "Can't rename a sheet with " & [A2].Value _
& vbNewLine & "as that name already exist."
Exit Sub
End If
Next wks
On Error Resume Next
ActiveSheet.Name = [A2].Value2
If Err.Number <> 0 Then MsgBox Err.Description
End Sub

Well it was going to be a 1 liner, but that's been taken :)


Regards
Robert McCurdy
 
G’Day Gord

This works a treat and is simple to understand. I have one remaining
question.

The cell I am referencing (B1 in sheet 2) has a CONCATENATE function
(joining "sheet1!A1,A1" ) . When I change A1, I sheet2, B2 also changes as
does the Sheet2 sheet name. However, when I change the value in Sheet1!A1
there is no change to the Sheet2 sheet name, even though a B1 has changed? I
have tried "F9", even closing and opening the workbook but I can't seem to
force a change.

Is there a simple solution?

Thanks

Scott
 
You need a calculate event for that.

Private Sub Worksheet_Calculate()
On Error GoTo enditall
Application.EnableEvents = False
With Me
If .Range("B1") = "" Then
.Name = .Name
Else
.Name = .Range("B1").Value
End If
End With
enditall:
Application.EnableEvents = True
End Sub

Assumes code is in Sheet2 and B1 has formula

=CONCATENATE(Sheet1!A1,A1)


Gord
 
Back
Top