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