Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 15

Thread: Page Tab Automatisation

  1. #1
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver Area, Canada
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  2. #2
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    Please check this:

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

    I would be around if you need.
    Suat

  3. #3
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.
    Suat

  4. #4
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    Suat

  5. #5
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver Area, Canada
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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...

  6. #6
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,637
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    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.

  7. #7
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver Area, Canada
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  8. #8
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

    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
    Suat

  9. #9
    BatCoder
    Join Date
    Feb 2002
    Location
    Turkey
    Posts
    813
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default

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

  10. #10
    New Member
    Join Date
    Apr 2002
    Location
    Vancouver Area, Canada
    Posts
    21
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •