Results 1 to 5 of 5
  1. #1
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Tab Names (2000)

    I have code (placed in Worksheet 1) to update the worksheet names on all 4 tabs in my workbook based on a change of cell G3 on Worksheet(1). Cell G3 accepts a date entry (ex: 01/01/07). Cell W16 would then have a text value of Jan07. Cells W17 and W18 would have a text value Feb07 and Mar07 and W19 would display 1st Quarter (2007). The user should always be entering a month value that corresponds to the beginning of a yearly quarter (01, 04, 07, or 10). If they do, the worksheet names (tabs) update just fine. However, if they don't, a function is in cell G4 to check for this and displays an error message when incorrect data has been entered in G3. And, whenever this happens, it starts to screw up the tab names. It seems as though the calculations of the tab names happens before some of the cells W16:W19 have updated. Some of the tab names get changed and some don't. This only seems to happen when typing a month that is not 01, 04, 07, or 10, but it then screws it up when a user types a correct month. I apologize that I cannot attach this workbook. It contains too much company data that cannot be easily removed. If we can't figure it out from the code and my description, I'll work on removing the confidential data and submit the workbook.

    --------------------------------------------------------------------------------
    Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("G3")) Is Nothing Then
    ActiveWorkbook.Unprotect Password:="password"
    Worksheets(1).Name = Range("W16")
    Worksheets(2).Name = Range("W17")
    Worksheets(3).Name = Range("W18")
    Worksheets(4).Name = Range("W19")
    ActiveWorkbook.Protect Structure:=True, Password:="password"
    End If
    End Sub
    --------------------------------------------------------------------------------

    Thanks!

    Chris

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Names (2000)

    Put validation on cell G3 to accept only the first day of a quarter:
    - Select cell G3.
    - Select Data | Validation...
    - Select Custom from the dropdown list.
    - Enter the following formula in the box:
    <code>
    =AND(DAY(G3)=1,MOD(MONTH(G3),3)=1)
    </code>
    - Activate the Error Alert tab.
    - Enter a warning message.
    - Click OK.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Tab Names (2000)

    Hi Chris,
    If I understand you correctly, you can do something like:
    <pre>Private Sub Worksheet_Change(ByVal Target As Range)
    On Error Resume Next
    If Not Intersect(Target, Range("G3")) Is Nothing Then
    if (month(Range("G3")) - 1) mod 3 = 0 then
    ActiveWorkbook.Unprotect Password:="password"
    Worksheets(1).Name = Range("W16")
    Worksheets(2).Name = Range("W17")
    Worksheets(3).Name = Range("W18")
    Worksheets(4).Name = Range("W19")
    ActiveWorkbook.Protect Structure:=True, Password:="password"
    else
    msgbox "Month must be 1, 4, 7 or 10!"
    end if
    End If
    End Sub
    </pre>

    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Star Lounger
    Join Date
    Jun 2006
    Posts
    88
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Tab Names (2000)

    I didn't even think about validation. That should take care of the problem.

    Any thoughts as to why the code would work for Jan, Apr, Jul, and Oct but mess up with any other month? The cells in W16:W19 are always correct regardless of the month, but the worksheets.name don't get updated correctly.

    Thanks for the help!

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Tab Names (2000)

    We'd have to know more about the worksheet - for example, what are the formulas in W16 etc.?

Posting Permissions

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