Worksheet Naming

[Andy Sandford]

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 Kabel]

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

 

[Arvi Laanemets]

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!

 

[Frank Kabel]

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)

 

[Andy Sandford]

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 Kabel]

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

 

[Bob Phillips]

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)

 

[Bob Phillips]

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)

 

[Bob Phillips]

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]

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.

 

[Frank Kabel]

Hi Bob
that was what I was trying to say
I just wouldn't change the worksheet name with a worksheet_change event
as you have to include IMHO a lot of error checking (preventing
duplicates, invalid strings, etc.)

 

[Bob Phillips]

Frank,

Yes I saw that, but you still seemed to agree with Arvi that it wasn't a
good idea, you wouldn't do it, and I cannot see why.

Bob

 

[Frank Kabel]

Hi bob
just a personal taste
I always try to prevent the usage of event macros as IMHO they tend to
make a spreadsheet more complex/difficult to understand and in most
cases they are not really required.
Thats all - and that from someone who posted a code to make cells
blink ;-)

Greetings and have a nice sunday (hopefully with not so much work to
do)

 

[Bob Phillips]

Frank,

just a personal taste
I always try to prevent the usage of event macros as IMHO they tend to
make a spreadsheet more complex/difficult to understand and in most
cases they are not really required.

I agree with the pirnciple of that. althoughy being more of a VBer most of
my soltions are VBA oriented.

Thats all - and that from someone who posted a code to make cells
blink ;-)

Indeed. Thankfully, I never saw the full '... custom style applied to many

Greetings and have a nice sunday (hopefully with not so much work to
do)

No, it's Sunday. The garden is a mess and needs preparing for Spring, I am
re-doing my hall, and I want to convert the loft to an extra bedroom. But
hey, Arsenal are on the TV in the FA Cup (leadintg 2-1 at this moment), and
next up is Italy v England in the Rugby 6 Nations trophy, so life is good.
Why are ther so few Germans in our footbal league, tons of French, few
Italians, Spaniards, loads of Africans, even a few Yanks, but the only
Germans I can think of is Lehmann (Arsenal goalkeeper), and Christian
Ziegler (who rarely gets a game with Tottenham these days).

Can always tell a slow day in the NGs. It gives us an opportunity tgo chat
and get to know each other better.

All the best from warm if not sunny Poole on the Doset coast, the world's
on ly heritage coastline, one of only two counties in England with no
motorways (AFAIK),

Bob

 

[Frank Kabel]

Bob,

Indeed. Thankfully, I never saw the full '... custom style applied to
many cells and using different (random) colors ...' that you
created<g>

It was beautiful -> and now it's in the trash bin...

No, it's Sunday. The garden is a mess and needs preparing for Spring,
I am re-doing my hall, and I want to convert the loft to an extra
bedroom. But hey, Arsenal are on the TV in the FA Cup (leadintg 2-1
at this moment), and next up is Italy v England in the Rugby 6
Nations trophy, so life is good. Why are ther so few Germans in our
footbal league, tons of French, few Italians, Spaniards, loads of
Africans, even a few Yanks, but the only Germans I can think of is
Lehmann (Arsenal goalkeeper), and Christian Ziegler (who rarely gets
a game with Tottenham these days).

The patriotic answer: Our German national league is so good our best
players have no reason to play in England. And of course we will win
the EM this year (Definition of a footbal game: A game lasts 90 minutes
and in the end the Germans win)
Though talking about that it becomes difficult just to name some good
Germany players nowadays :-(
And to be honest I really enjoyed watching the Champions League game
Celtic against Bayer München in an Irish Pub here in Frankfurt! (and I
wasn't on Bayern's side)

All the best from warm if not sunny Poole on the Doset coast, the
world's on ly heritage coastline, one of only two counties in England
with no motorways (AFAIK),

I envy you for that view. All the best

Frank

 

[Bob Phillips]

(Definition of a footbal game: A game lasts 90 minutes
and in the end the Germans win)

Unfortunately that is so often true. But we can beat you at rugby and
cricket anyday (as lontg as you don 't start playing them seriously!).

Watching the Champions League game
Celtic against Bayer München in an Irish Pub here in Frankfurt!

What is the world coming to?

Anyway, enough already, England are up, they have an exciting pacey side, I
have a cup of coffee, but I just wish I was in Rome.

Take care

Bob

 

[Tom Ogilvy]

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]

Hi Tom
As the name change triggers the Calculate event I think disabling
events is good practice anyway

 

[Bob Phillips]

and we were referring to principles rather than this specific instance.

Bob

 

[Frank Kabel]

Hi Bob, Tom
you're right - nothing to do with the issue the OP had originally

nice sunday to both of you