Rename Worksheets Based on Values in cells

[Steve]

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

[Leith Ross]

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.

Please Login or Register  to view this content.

Sincerely,
Leith Ross

[Richard Reye]

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).Activate
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
>
>
>

[Richard Reye]

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).Activate
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
>
>
>

[JE McGimpsey]

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" <[email protected]> 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

[Richard Reye]

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).Activate
> 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
> >
> >
> >

[Greg Wilson]

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