Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

There is probably a better way of doing this but this works and is rather
secure.

1 - Get a workbbok with the required number of worksheets (at least 32)
2 - In the VB Editor, go to Tools-Reference and select Microsoft Visual
Basic for Applications Extensibility 5.3 (or whatever version you have)
3 - Rename the codename of all you worksheets to Control, Day1, Day2,
Day3...Day31. Each sheet has two names. The codename appears as (Name) in the
properties window.
4 - Paste this code in the code section of the Control sheet.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MySheet As String

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Dates")) Is Nothing Then
MySheet = "Day" & Target.Row
ThisWorkbook.VBProject.VBComponents(MySheet).Activ ate
If Target.Value = "" Then
ActiveSheet.Name = "No " & MySheet
Else
ActiveSheet.Name = Target.Text
End If
End If

Control.Activate

Application.ScreenUpdating = True

End Sub

5 - Apply the name 'Dates' to the range A1 to A31 on the Control sheet

Hope this Helps and that I explained myself well enough. The use of codename
should help with ensuring that manual changes to the name by mistake don't
effect the code.


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone


"Steve" wrote:

Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

Change of Code. The following code will allow for drag'n'drop (Autofill??).
That is, if the target area (area changed) is more than one cell, it won't
through an error!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MySheet As String
Dim Cell As Range

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Dates")) Is Nothing Then
For Each Cell In Target
MySheet = "Day" & Cell.Row
ThisWorkbook.VBProject.VBComponents(MySheet).Activ ate
If Cell.Value = "" Then
ActiveSheet.Name = "No " & MySheet
Else
ActiveSheet.Name = Cell.Text
End If
Next Cell
End If

Control.Activate

Application.ScreenUpdating = True

End Sub


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone


"Steve" wrote:

Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

This is a variant on an FAQ. The solution here can be modified to apply
to different sheets' names:

http://www.mcgimpsey.com/excel/event...efromcell.html

You can find lots of other suggestions in the archives:

http://groups.google.com/advanced_group_search



In article <xQg9f.359077$oW2.279554@pd7tw1no,
"Steve" wrote:

Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

Please excuse my spelling mistakes!

--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone


"Richard Reye" wrote:

Change of Code. The following code will allow for drag'n'drop (Autofill??).
That is, if the target area (area changed) is more than one cell, it won't
through an error!

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim MySheet As String
Dim Cell As Range

Application.ScreenUpdating = False

If Not Intersect(Target, Range("Dates")) Is Nothing Then
For Each Cell In Target
MySheet = "Day" & Cell.Row
ThisWorkbook.VBProject.VBComponents(MySheet).Activ ate
If Cell.Value = "" Then
ActiveSheet.Name = "No " & MySheet
Else
ActiveSheet.Name = Cell.Text
End If
Next Cell
End If

Control.Activate

Application.ScreenUpdating = True

End Sub


--
Cheers!

Richard Reye

"Never argue with an idiot. They'll bring you down to their level then beat
you with experience" - someone


"Steve" wrote:

Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

This assumes that the Control worksheet is the first sheet in the worksheets
collection - i.e. can be refered to as Sheet(1). The sheets that have dates
for names can likewise be refered to as Sheet(2), Sheet(3) etc. Also assumed
is that you are changing the text in cells A1:A31 individually as opposed to
batch changing by macro or other means. You likely will need to format the
cells A1:A31 as Text else you may get an error if Excel tries to convert it
to a date format containing illegal characters (e.g. "/" or "-").

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
If Intersect(Target, Range("A1:A31")) Is Nothing Then Exit Sub
Sheets(Target.Row + 1).Name = Target.Value
On Error GoTo 0
End Sub

Regards,
Greg

"Steve" wrote:

Hi All,

I would really appreciate some help with the following:

I have a workbook with a worksheet called "Control"
In cells A1 through to A31 I have each of the days of the
month of January listed for example January 1 is in cell A1 and
in cell A2 the words January 2 etc.

The workbook also contains 31 additional worksheets which I would
like to be linked to the names in cells A1 to A31 on the "control sheet"
so the first worksheet after the control worksheet will be named January 1
etc.

If I change the names in A1 to A31 to February 1 for example I would
like the sheets to automatically be remained according to the new text.

Any ideas on how to accomplish the above?
All suggestions welcome.

Thanks,

Steve

Hello Steve,

For this code to run properly, "Control" must be the name of the
Leftmost Worksheet Tab. This will be the first Worksheet created in the
collection. The Worksheet names can change, but order of creation does
not.

Add this code into "Control's" Worksheet Events code.


Code:
--------------------
Private Sub Worksheet_Change(ByVal Target As Range)

Dim C As Long
Dim R As Long

'Change the Worksheet name to the value in cell A1 to A31
'When the cell's contents are changed.
'This code must be placed the First Worksheet (Leftmost Tab).

On Error GoTo NameFault

With Target
C = .Column
R = .Row
End With

If C = 1 And (R = 1 And R <= 31) Then
Worksheets(R + 1).Name = Target.Value
End If

Exit Sub

NameFault:
Msg = "Error " & Err.Number & vbCrLf _
& Err.Description
MsgBox Msg, vbExclamation + vbOKOnly

End Sub

--------------------

Sincerely,
Leith Ross


--
Leith Ross
------------------------------------------------------------------------
Leith Ross's Profile: http://www.excelforum.com/member.php...o&userid=18465
View this thread: http://www.excelforum.com/showthread...hreadid=480502