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]

Please check this:

http://www.mrexcel.com/board/viewtop...c=2877&forum=2

I would be around if you need.

[smozgur]

if those cells would never change (also tab names i mean) then you can just set the names at the opening of the doc or just once.

Please let me know what you need.

[smozgur]

My second comment could be done with this code in ThisWorkbook Open Sub.

(Assume A1 had the tab name you want to set)


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

'To make sheet name changed by changing link value
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$1" Then
Sh.Name = Target.Value
End If
If Err Then
MsgBox "Name is not valid"
End If
End Sub

[diamond]

Thanks Smozgur

I try your first suggestion, it works with names and text but I'm using dates format.
04/07/2002 which is change to show 7-Apr-02.

The sheet tab show the pay day that I want.

I try to use an other cell by =A27 in cell BA27. The result is the same, it doesn't see the date.

Thanks for you help...

[Aladin Akyurek]

On 2002-04-07 08:23, diamond wrote:
Thanks Smozgur

I try your first suggestion, it works with names and text but I'm using dates format.
04/07/2002 which is change to show 7-Apr-02.

The sheet tab show the pay day that I want.

I try to use an other cell by =A27 in cell BA27. The result is the same, it doesn't see the date.

Thanks for you help...

In BA27 enter:

=TEXT(A27,"d-mmm-yy")

to see if this works.

[diamond]

It works.

Now, the last challenge is that I have to click on the cell then click on the formula then the green check mark and it will update.
I forget to mention I'm working with 97 version.

So if I change A23 that is connected with A24 by A23+1 to change the date from Mon to Fri all the way to A27. It change everything the way it is suppose to even BA27 but it doesn't update the tab until I go in the formula of that cell (BA27)and get out again. Then it will see the change. It is like the macro doesn't update automatically.

Am I doing something wrong?

Thanks guys, I'm learning a lot.

[smozgur]

Hi,

You remember Change event?

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
On Error Resume Next
If Target.Address = "$A$23" Then
Sh.Name = Target.Value
End If
If Err Then
MsgBox "Name is not valid"
End If
End Sub

Now you should change it like above. You should check for A23 cell changing.

regards

[smozgur]

I mean, you will check the being modified cell in SheetChange event.

[diamond]

Hi

Sorry for not getting it.

I created a test sheet for this project of mine.

Here every step that I made.

Cell A3= Date Format 4/4/2002
Cell A4= A3+1
Cell A5= A4+1
Cell A6= A5+1
Cell A7= A6+1

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


When I right click on the Sheet label and view code I see the VB page:
On the top left, I see:
Project-VBA Project

Bottom left, I see:
Properties-sheet1

On the right side, I see the macro window, I suppose:

Worksheet  Change 

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

The only routine that works partially is the above one.

I still need to click on the formula in C7 then click green check mark and the sheet label changed.

When I change the date in A3, C7 update automatically but not the tab.

I tried your suggestions but I’m missing a point some were.

Don’t forget this is the first time that I go in VBA.

Thanks for your patience.




[ This Message was edited by: diamond on 2002-04-10 22:19 ]