Colin Hayes used his keyboard to write :
Hi Garry
OK thanks for that. I got it working here now , I'm pleased to say. I think
the sheet protection was tripping it up before.
As you asked , I'll try to expand on the issue. As I change the content of A1
on sheet 1 , so the content of A1 on the other 11 sheets updates to match it.
I was trying to get each the tab names to update automatically to match the
changes made , each tab changing to match the content of A1.
I had a worksheet in the past which did this. It had coding inserted under
the tabs , which reacted to changes in A1 to rename each tab accordingly and
immediately.
The content of A1 on sheet 1 is critical to the functioning of the whole
project , and changing the content defines the whole point of the workbook.
You can get it at the link below so you can better see what I'm describing.
It's probably easier that way.
http://www.chayes.demon.co.uk/Perpet...endar_Prac.zip
Enter a month and date into A1 on sheet 1 to see how it works.
The project is pretty much finished apart from the auto-tab-naming idea , so
I'm grateful for your time and expertise.
Bets wishes.
Hi Colin,
You can put code in the Worksheet_Change event behind Sheet1. This,
then, means if you change the date in Sheet1.Range("$A$1") the code
runs to insert the formulas and update the sheet tabs automatically. So
try these changes to your project:
Sub IncrementMonths()
Dim i As Integer
For i = 2 To 3 '12
With Sheets(i)
.Range("$A$1").Formula = _
"=DATE(YEAR('" & Sheets(1).Name _
& "'!$A$1),MONTH('" & Sheets(1).Name & "'!$A$1)+" _
& i - 1 & ",DAY('" & Sheets(1).Name & "'!$A$1))"
.Name = Format(.Range("$A$1").Value, "mmm_yyyy")
End With
Next
Sheets(1).Name = Format(Sheets(1).Range("$A$1").Value, "mmm_yyyy")
End Sub
Behind Sheets(1), paste the following...
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then IncrementMonths
End Sub
I took a look at your project file. Looks awesome! When I get a chance,
I'll post back any further comments.
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc