Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

Great sub, however in MY case I need 4 sheets to be renamed based on cells in
sheet 1. Can anyone help?

Rename tab on cell event
 

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

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

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!

Rename tab on cell event
 

Gord,

You are a gentlemen and a scholar, thanks very much

Scott

"Gord Dibben" wrote:

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:

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!

Rename tab on cell event
 

Good to hear you got sorted out.

Thanks for the feedback.


Gord

On Tue, 8 Jul 2008 16:38:01 -0700, Scott
wrote:

Gord,

You are a gentlemen and a scholar, thanks very much

Scott

"Gord Dibben" wrote:

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!