Worksheet Naming

Hi All

Is it possible to have the name of a worksheet change based on a value in a
cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within a
building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a different room.

Thanks in advance

Andy

Hi
this requieres (AFAIK) VBA. You could use the worksheet_change event.
E.g. the following code will change the tab name based of the value in
A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

Andy Sandford wrote:
Hi All

Is it possible to have the name of a worksheet change based on a
value in a cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within
a
building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a different
room.

Thanks in advance

Andy

Hi

I think it may be possible through workshhets Change event. Anyway I
consider it a very bad idea! How? Let's assume you change sheets name.
Whenever you do it manually, all formulas referring to this sheet are
adjusted automatically. But as much as I know, it won't be so when you
change worksheets name through VBA - as result all links to renamed sheet
will be broken!


--
(Don't use my reply address - it's spam-trap)

Arvi Laanemets



"Andy Sandford" wrote in message
...
Hi All

Is it possible to have the name of a worksheet change based on a value in
a
cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within a
building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a different
room.

Thanks in advance

Andy

Hi Arvi
though I totally agree with you that I wouldn't use this kind of
procedure in my own spreadsheets your assumption regarding link updates
is not correct. At least in Excel 2003 the links are updated if you
change the worksheet name with VBA (and I assume this is true for the
older Excel versions as well)

--
Regards
Frank Kabel
Frankfurt, Germany

Arvi Laanemets wrote:
Hi

I think it may be possible through workshhets Change event. Anyway I
consider it a very bad idea! How? Let's assume you change sheets
name.
Whenever you do it manually, all formulas referring to this sheet are
adjusted automatically. But as much as I know, it won't be so when
you
change worksheets name through VBA - as result all links to renamed
sheet will be broken!



"Andy Sandford" wrote in message
...
Hi All

Is it possible to have the name of a worksheet change based on a
value in a cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within
a building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a
different room.

Thanks in advance

Andy

Frank

Worked a treat!

Only problem I now have is that it won't work with the previous VBA code I
have in place...
I get the following message -

"Compile error: Ambiguous name detected"

I just pasted your code in the same window as the previous...
I'm guessing that it's because both bits of code have the same "title" -

Private Sub Worksheet_Change(ByVal Target As Range

Did I put it in the correct place? Is there a way around this if I did?

Thanks again!

Andy

Hi
looks like you have already a worksheet_change event procedure in your
code. So you have to incorporate the code for changing the worksheet
name in you existing code. If you like, post your existing code and
I'll add the lines for changing the worksheet name

--
Regards
Frank Kabel
Frankfurt, Germany

Andy Sandford wrote:
Frank

Worked a treat!

Only problem I now have is that it won't work with the previous VBA
code I have in place...
I get the following message -

"Compile error: Ambiguous name detected"

I just pasted your code in the same window as the previous...
I'm guessing that it's because both bits of code have the same
"title" -

Private Sub Worksheet_Change(ByVal Target As Range

Did I put it in the correct place? Is there a way around this if I
did?

Thanks again!

Andy

You need to combine the code from the previous change macro with this new
code.

When you combine it, test for one range, take the appropriate action, then
test for the other, and take the new action . Something like

If Not Intersect(Target, Me.Range("A1")) Is Nothing Then
' do whatever is necessary here
ElseIf Not Intersect(Target, Me.Range("B1")) Is Nothing Then
'do something else
End If

Another way is to use Case statements

Select Case True
Case Not Intersect(Target, Me.Range("A1")) Is Nothing :
' do whatever is necessary here
Case Not Intersect(Target, Me.Range("B1")) Is Nothing Then
'do something else
End Select

It becomes problemmatical when both actions apply to the same target, or
even worse, part of the same target.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
looks like you have already a worksheet_change event procedure in your
code. So you have to incorporate the code for changing the worksheet
name in you existing code. If you like, post your existing code and
I'll add the lines for changing the worksheet name

--
Regards
Frank Kabel
Frankfurt, Germany

Andy Sandford wrote:
Frank

Worked a treat!

Only problem I now have is that it won't work with the previous VBA
code I have in place...
I get the following message -

"Compile error: Ambiguous name detected"

I just pasted your code in the same window as the previous...
I'm guessing that it's because both bits of code have the same
"title" -

Private Sub Worksheet_Change(ByVal Target As Range

Did I put it in the correct place? Is there a way around this if I
did?

Thanks again!

Andy

Frank,

Not trying to be picky, but I have noticed this on a few of your posts. You
include error trapping and reset event handling in your event code (that's
good), but you don't disable events at the start. Thus, change code will
re-fire the same event. Often this is not a big handicap, but it is less
efficient that it needs to be, and in exceptional circumstances, the event
is fired multiple times.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi
this requieres (AFAIK) VBA. You could use the worksheet_change event.
E.g. the following code will change the tab name based of the value in
A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

Andy Sandford wrote:
Hi All

Is it possible to have the name of a worksheet change based on a
value in a cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within
a
building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a different
room.

Thanks in advance

Andy

Sorry, but I cannot see what is wrong with this practice. When you change a
worksheet name through VBA, all formulas are automatically updated, same as
doing it manually.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"Frank Kabel" wrote in message
...
Hi Arvi
though I totally agree with you that I wouldn't use this kind of
procedure in my own spreadsheets your assumption regarding link updates
is not correct. At least in Excel 2003 the links are updated if you
change the worksheet name with VBA (and I assume this is true for the
older Excel versions as well)

--
Regards
Frank Kabel
Frankfurt, Germany

Arvi Laanemets wrote:
Hi

I think it may be possible through workshhets Change event. Anyway I
consider it a very bad idea! How? Let's assume you change sheets
name.
Whenever you do it manually, all formulas referring to this sheet are
adjusted automatically. But as much as I know, it won't be so when
you
change worksheets name through VBA - as result all links to renamed
sheet will be broken!



"Andy Sandford" wrote in message
...
Hi All

Is it possible to have the name of a worksheet change based on a
value in a cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room within
a building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a
different room.

Thanks in advance

Andy

Hi Bob
not picky on your side but a mistake on my side. Eue to copy and paste
of existing code I daccidentally deleted the line
Application.EnableEvents = False

So the code should read

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value "" Then
Application.EnableEvents = False '- add this line
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub


Thanks for pointing this out.

--
Regards
Frank Kabel
Frankfurt, Germany

Bob Phillips wrote:
Frank,

Not trying to be picky, but I have noticed this on a few of your
posts. You include error trapping and reset event handling in your
event code (that's good), but you don't disable events at the start.
Thus, change code will re-fire the same event. Often this is not a
big handicap, but it is less efficient that it needs to be, and in
exceptional circumstances, the event is fired multiple times.


"Frank Kabel" wrote in message
...
Hi
this requieres (AFAIK) VBA. You could use the worksheet_change
event.
E.g. the following code will change the tab name based of the value
in A1
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit Sub
On Error GoTo CleanUp:
With Target
If .Value "" Then
Me.Name = .Value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub



--
Regards
Frank Kabel
Frankfurt, Germany

Andy Sandford wrote:
Hi All

Is it possible to have the name of a worksheet change based on a
value in a cell within that worksheet?

e.g. data to be input in a cell relates to the name of a room
within
a
building - say "Office 1"

I'd like the worksheet name to change as the text input changes.

I have several identical worksheets - but each relates to a
different room.

Thanks in advance

Andy