Linking sheet tab names to cells
 

Hi
you'll need an event procedure (see
http://www.cpearson.com/excel/events.htm for more details): Put the
following code in your worksheet module:
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
application.enableevents = false
With Target
If .Value < "" Then
Me.Name = .value
End If
End With
CleanUp:
Application.EnableEvents = True
End Sub

--
Regards
Frank Kabel
Frankfurt, Germany

"kevin" schrieb im Newsbeitrag
...
Hi,

Do you know a way in VBA to change the name of a sheet by linking it
to a cell. ie if cell A1 is "Monday" the name on the sheet tab is
Monday. but will change if i put Tuesday in A1

Regards
Kevin

Linking sheet tab names to cells
 

Frank Kabel wrote

application.enableevents = false

curious: why is this line needed?

--
David

Linking sheet tab names to cells
 

"David" wrote...
Frank Kabel wrote
application.enableevents = false

curious: why is this line needed?

Step through Frank's macro in the VBE and you'll see why.

--
To top-post is human, to bottom-post and snip is sublime.

Linking sheet tab names to cells
 

"David" wrote...
Frank Kabel wrote

application.enableevents = false

curious: why is this line needed?

Sorry. I screwed up in my previous response. This isn't necessary.

--
To top-post is human, to bottom-post and snip is sublime.

Linking sheet tab names to cells
 

"Frank Kabel" wrote...
...

Quibbles:

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

You've already established that Target must be a single cell, so no need for
Intersect. Just compare Target's address to "A1". Indeed, both If statements
above could be replaced by

If Target.Address(0, 0) < "A1" Then Exit Sub

On Error GoTo CleanUp
application.enableevents = false

Wrote this in your newsreader?

With Target
If .Value < "" Then
Me.Name = .value
End If
End With

If .Value were "", then Me.Name = .Value would throw a runtime error, just as
any other invalid worksheet name to which A1 could evaluate. If such a runtime
error occurs, the original worksheet name remains unchanged. So this entire With
block could be replaced by

Me.Name = Target.Value

CleanUp:
Application.EnableEvents = True
End Sub

--
To top-post is human, to bottom-post and snip is sublime.

Linking sheet tab names to cells
 

--
Regards
Frank Kabel
Frankfurt, Germany

"Harlan Grove" schrieb im Newsbeitrag
...
"David" wrote...
Frank Kabel wrote

application.enableevents = false

curious: why is this line needed?

Sorry. I screwed up in my previous response. This isn't necessary.


Hi Harlan
don't agree :-)
Changing the worksheet name will lead (AFAIK) to a recalculation of
formulas which reference this worksheet. So this could for example
trigger a worksheet_calculate event

Frank

Linking sheet tab names to cells
 

Quibbles:

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

You've already established that Target must be a single cell, so no
need for
Intersect. Just compare Target's address to "A1". Indeed, both If
statements
above could be replaced by

Ack
typical copy+paste function and you're of course right: in this case
not required and could be simplified as you posted.

[...]
On Error GoTo CleanUp
application.enableevents = false

Wrote this in your newsreader?

got me :-)


With Target
If .Value < "" Then
Me.Name = .value
End If
End With

If .Value were "", then Me.Name = .Value would throw a runtime error,
just as
any other invalid worksheet name to which A1 could evaluate. If such
a runtime
error occurs, the original worksheet name remains unchanged. So this
entire With
block could be replaced by

Good point though I like to prevent throwing an error (but then I
should have checked for existing or valid worksheet names also...)


Regards
Frank