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!