Change name of Wksheet to reflect a month auto.

[dolphinv4]

Hi,

is it possible to have a macro change the name of a sheet
to a certain month reflected in a cell? If so, how do I
do it?

Thx!
val

 

[Olly]

Assuming your cell (in this case, A1) contains a date value:

activesheet.name = format(range("A1").value,"mmmm")

 

[Frank Kabel]

Hi
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

 

[Max]

Here's 2 macros from a previous post by J.E. McGimpsey which will do the job
(with some added-on implementation steps below ..)
---------
From: J.E. McGimpsey ([email protected])
Subject: Re: Associate name sheet with cell
Newsgroups: microsoft.public.excel.links
Date: 2001-09-06 13:39:34 PST

If by "cell name" you mean cell value, you'd need to use a
Worksheet_Change() event macro (stored in the sheet module) or a
Workbook_SheetChange() event macro (stored in the ThisWorkbook module) (
I don't know if you can automatically change the sheet name based on a
change in range name):

Macro A
-----------
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
ActiveSheet.Name = Range("A1").Value
End If
End Sub

or

Macro B
----------
Private Sub Workbook_SheetChange(ByVal Sh As Object, _
ByVal Target As Excel.Range)
If Not Intersect(Target, Range("A1")) Is Nothing Then
Sh.Name = Range("A1").Value
End If
End Sub

Is it possible to associate a sheet name to a cell, so
when we change the cell name, the sheet will change at the
same time???

Thanks

Charles

-----------

Some steps to ease-in & quickly implement ..
---------------------------------------------------
Macro A:

Right-click on a sheet tab > Choose "View Code"

Clear the defaults in the white space and paste Macro A there

Type a name, "Peter" in A1 (the target cell) in the sheet, press Enter

Sheet tab will be named "Peter"

Macro B:
Right-click on the Excel icon (to the left of "File" on the menu bar)

Choose "View Code"

Clear the defaults in the white space and paste Macro B there

Type a different name, "Adam" in A1 (the target cell) in *any* sheet, press
Enter

Sheet tab will be named "Adam"

--
Macro A will apply only to the sheet that the code is pasted in,
whilst Macro B will apply to all sheets in the book.
The target cell can be changed to suit.

 

[Max]

The 2 macros work for dates in the target cells, too.

Albeit if you want "May 2004" to be the name appearing on the tab
the contents in the target cell A1 need to be either entered as text
(preceded by an apostrophe [ ' ]), or A1 formatted as Text

Sheetnames cannot be duplicates of existing sheets,

31 characters, nor contain illegal character(s): : \ / ? * [ or ]