Worksheet Naming

Actually, when I said there is probably nothing wrong with disabling events,
I had given some thought to the calculate event and my feeling would be that
disabling might be a disadvantage in this case. Obviously it would depend
on the particulars.

--
Regards,
Tom Ogilvy

"Frank Kabel" wrote in message
...
Hi Tom
As the name change triggers the Calculate event I think disabling
events is good practice anyway :-)

--
Regards
Frank Kabel
Frankfurt, Germany

Tom Ogilvy wrote:
Changing the name of a sheet doesn't trigger the change event.

I suppose it doesn't hurt to disable events, but it isn't required in
this instance.


"Frank Kabel" wrote in message
...
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

Then a principle might be to not do something if it isn't required
particularly if it has side effects.

The principle of disabling events when performing an action that would cause
a recursive call is certainly sound.

However, under that principle

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
if Target.Address = $A$1 then
Target.Offset(0,1).Value = Date
end if
End Sub

Would cause a recursive call, but I wouldn't see a need to disable events in
this instance.

Of course each person is free to do whatever they want.

--
Regards,
Tom Ogilvy