Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 2 of 2 FirstFirst 12
Results 11 to 15 of 15

Thread: Page Tab Automatisation

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

    Default

    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
    Suat

  2. #12
    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 Smozgur

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

    I will work more with it after work.

    Thanks again.

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

    Default

    Nice to hear that.


    Suat

  4. #14
    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, 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 ]

  5. #15
    Board Regular
    Join Date
    Mar 2002
    Location
    London, UK
    Posts
    167
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

    <table style="background-color:#0e54be" cellspacing="1" cellpadding="2"><td style="background-color:#ceffff;font-family:arial;color:#072c63;font-size:8pt;"> DALEY :P </td></table>

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
  •