Page Tab Automatisation

diamond

New Member
Joined
Apr 5, 2002
Messages
21
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.
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
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.
 
Upvote 0
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
 
Upvote 0
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...
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,510
Messages
6,114,040
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top