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

"John G." wrote:

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!

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

Thanks Gord and Mike. Making life simpler, or complex, depends on what else
this leads to. Thanks a bunch!

"Gord Dibben" wrote:

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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.

"Gord Dibben" wrote:

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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

On Thu, 8 May 2008 11:33:02 -0700, John G.
wrote:

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.

"Gord Dibben" wrote:

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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

GDay 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

"Gord Dibben" wrote:

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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

On Tue, 8 Jul 2008 14:56:01 -0700, Scott
wrote:

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

"Gord Dibben" wrote:

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

On Wed, 7 May 2008 10:40:02 -0700, John G.
wrote:

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.

"Mike H" wrote:

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

"John G." wrote:

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!

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
"John G." wrote in message ...
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!