Page Tab Automatisation

[diamond]

Hi

I created a buget and time sheet forms that used this formula. From page to page.

=SUM('1 Mar 02'!B1+14)

I would like to link the page tab to the cell that has this formula. Like that I don't have to write them by hand 26 times in my workbook. It is bad if I have to change the date on the first page. I require to manually change every tab.

Please give me a simple solution to this challenge. This is my first project.

Thanks a million.

 

[smozgur]

You mean you will change the A3?

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$3" Then Sheet1.Name = Range("c7")
If Err Then Target = ActiveSheet.Name
End Sub


Hope this works.
Regards

 

[diamond]

Hi Smozgur

It works. Now I know were I was going wrong.

I will work more with it after work.

Thanks again.

 

[smozgur]

Nice to hear that.

 

[diamond]

Hi, it’s me again.

Your last solution work on the first page, no problem.
But not on Sheet 2 and 3 in my test workbook.

After experimenting for a few hours.

Here, what I did.

Sheet 2
Cell A3= ’18 Jan 02’!A7+1 then
Cell A4= A3+1
Cell A5= A4+1
Cell A6= A5+1
Cell A7= A6+1

Cell C7 = =TEXT(A7,"d mmm yy")


And the same for sheet 3
Cell A3 = Sheet2!A7+1
Etc…

I copy your solution in VB for each Sheet Object. Ie: Sheet 2, 3

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$3" Then Sheet1.Name = Range("C7")
If Err Then Target = ActiveSheet.Name
End Sub

Making sure that I changed the Sheet Number for each page.
If Target.Address = "$A$3" Then Sheet2.Name = Range("C7")
If Target.Address = "$A$3" Then Sheet3.Name = Range("C7")

But when I changed A3 in sheet 1 all the dates changed on all the sheets,
the way they are suppose to change except that the sheet label
for 2 and 3 remains sheet2 and sheet3.

Is it because the cell A3 on page 2 and 3 are automatically change by this formula (= Sheet2!A7+1)?
That, it is not working.

Is there a way to get this process done in one operation?

I was so curious that I got a book call “The visual guide to Visual Basic for windows” by Richard Mansfield, 1388 pages

I have been reading on the different codes in one of your suggestions.

Private Sub Workbook_Open()
Dim wrkSheet As Worksheet
On Error Resume Next
For Each wrkSheet In ThisWorkbook.Worksheets
wrkSheet.Name = wrkSheet.Range("C7").Value
If Err Then
MsgBox "Name is not valid for " & wrkSheet.Name
End If
Next wrkSheet
End Sub

I try this but it is not even changing the label on the first sheet.
It seems to have a loop that would do the same operation for every sheet.
And if I use some thing like this were should I put it, sheet 1 maybe?
So, what do you suggest that I do? I have 26 sheets in my budget workbook that I want this process to work for.

Thanks so very much for you help and guidance.


_________________
Denis
This message was edited by diamond on 2002-04-13 12:02

 

[daleyman]

that code you quoted in the last reply needs to go in the workbook module, then it will update every time you open the workbook.

otherwise, you could add a new code module and put the same routine in as a sub-routine and call it from every page... ie...

IN NEW MODULE....

Sub SheetNameUpdates()
Dim wrkSheet As Worksheet
On Error Resume Next
For Each wrkSheet In ThisWorkbook.Worksheets
wrkSheet.Name = wrkSheet.Range("C7").Value
If Err Then
MsgBox "Name is not valid for " & wrkSheet.Name
End If
Next wrkSheet
End Sub

THEN IN EACH WORKSHEET MODULE YOU COULD USE...

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error Resume Next
If Target.Address = "$A$3" Then SheetNameUpdates
If Err Then Target = ActiveSheet.Name
End Sub

AND IF YOU WANT IT TO RUN EVERYTIME YOU OPEN THE WORKBOOK AS WELL, PUT THE FOLLOWING INTO THE WORKBOOK MODULE...

Private Sub Workbook_Open()
SheetNameUpdates
End Sub