Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136

    Talking Unanswered: Programatically change name of worksheet

    I'd like my worksheet to change names and display the value from two different cells.

    A7=2/28/06
    F7=4/28/06

    I'd like the name of the worksheet to change to: 2-28-06 THRU 4-28-06

    If the information in the cells change, I'd like the name of the worksheet to update.

    Is this possible?

  2. #2
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi Zenaida

    Put this code in the Shhet Module you ant to change the name of.

    Note I have not tested it for none dates in cells A7, A8, I in=magine thid will need some error trapping (or some other code). but it's a start !

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        If Target.Address = "$A$7" Or Target.Address = "$A$8" Then
            Me.Name = Format(Range("A7"), "m-dd-yy") & " THRU " & Format(Range("A8"), "m-dd-yy")
        End If
    End Sub
    HTH

    MTB

  3. #3
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    It works perfect. Thanks so much!!
    Last edited by Zenaida; 04-13-06 at 12:35.

  4. #4
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I just realized I need your help a little more.

    I have 6 spreadsheets in this workbook. I need the names of all 6 spreadsheets to display what's in Cells A7 & F7 in each spreadsheet respectively. The subsequent spreadsheets 2 - 6 have formulas that cause cells A7 & F7 to fill in as soon as I enter a date in cell A7 in spreadsheet 1.

    I can get the spreadsheet names for spreadsheets 2-6 to change but only when I physically enter a date in cell A7 in each spreadsheet, which will never happen since the formulas enters the values I need.

    Any suggestions?
    Last edited by Zenaida; 04-13-06 at 12:39.

  5. #5
    Join Date
    Mar 2006
    Posts
    163
    Following on from Mike's code.
    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
    Dim ws As Worksheet
        If Target.Address = "$A$7" Or Target.Address = "$A$8" Then
            For Each ws In Worksheets
                 ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("A8"), "m-dd-yy")
            Next ws
        End If
    End Sub

  6. #6
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    You guys are awesome!!!!! Thanks so much.

    How bout one more . . .

    If cell A7 is empty, I'd like all the names of the spreadsheets to default to "Cert Period 1", "Cert Period 2", etc. is this possible? Or they could default to "Sheet1", Sheet2", etc. As it is, if the user removes the date from the cell, the names stay and I'd like to have them change to something else so the names of the worksheets are not reflecting invalid dates.
    Last edited by Zenaida; 04-14-06 at 13:21.

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    Following on from nosie's code

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        Dim i As Long
    
        If Target.Address = "$A$7" Or Target.Address = "$A$8" Then
            For Each ws In Worksheets
                i = i + 1
                If Not IsDate(Range("A7")) Then
                    ws.Name = "Sheet" & i
                Else
                     ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("A8"), "m-dd-yy")
                End If
            Next ws
        End If
    End Sub
    Although this works, it will course an error if, for ANY reason, the 'Name' string generated in code is the same as an existing sheet name, so be careful how you define ranges A7 & A8 in each sheet.


    MTB

  8. #8
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    Thanks for getting back with me, I really appreciate it. I tried the code and got the same result which is: when I delete the date from A7 (which removes the date from A8) the name of the worksheets still reflects the date that was in A7 and A8.

    Any suggestions?

  9. #9
    Join Date
    Mar 2006
    Posts
    163
    What should the name be when you remove the dates?

  10. #10
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I have six spreadsheets in the workbook. When the user enters a date in the first worksheet in cell A7, cell A8 in the first worksheet fills in with a date, and cells A7 and A8 in the other five worksheets fill in with a date. Each worksheet is then named by the values in cells A7 and A8.

    When the user deletes the date in the first worksheet in cell A7, I would like the worksheets to be named the following:

    The first worksheet: Cert Period 1
    The second worksheet: Cert Period 2
    The third worksheet: Cert Period 3

    and so on through worksheet 6.

    I hope that explains it enough for you. Thanks for your help.

  11. #11
    Join Date
    Mar 2006
    Posts
    163
    Just change this.
    Code:
    ws.Name = "Sheet" & i
    To this.
    Code:
    ws.Name = "Cert Period " & i

  12. #12
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    I made the coding change and deleted the date in cell A7 but the names of the worksheets still display the values that were in cells A7 and A8 even though those cells are now blank. (all the A7 and A8 cells have formulas except cell A7 in the first worksheet).

    Any ideas?

  13. #13
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    As I understand it all the dates in the 6 sheets are controlled from the cell A7 in ONE sheet. If this is the case then the code below should be in the SHEET code module of the CONTROLLING sheet.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        Dim i As Long
    
        If Target.Address = "$A$7" Or Target.Address = "$A$8" Then
            For Each ws In Worksheets
                i = i + 1
                On Error Resume Next
                If Not IsDate(Range("A7")) Then
                    ws.Name = "Cert Period " & i
                Else
                     ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("A8"), "m-dd-yy")
                End If
                If Err.Number <> 0 Then
                    MsgBox "Could not rename sheet " & ws.Name, vbCritical, "Renaming Error"
                    Err.Clear
                End If
            Next ws
        End If
    End Sub
    I have added error trapping to advise when there is a name conflict.

    I woud seem that the "Cert Period " & i numbering sequence names the sheets from left to right (tab order), so they need arranging from left to right for ascending name index when no date is entered in cell A7 of the controlling sheet.

    HTH

    MTB

  14. #14
    Join Date
    Sep 2005
    Location
    Utah
    Posts
    136
    You are correct in your understanding of how the sheets work. I tried the code and am still having the same issue: when I delete the date from cell A7 of the controlling sheet, the names of all the sheets remain. They do not change to anything.

    I have attached the file. We have been referencing cells A7 and A8 in the thread but the cells are actually A7 & F7. I have been changing A8 to F7 when I use the code.
    Attached Files Attached Files
    Last edited by Zenaida; 04-21-06 at 15:14.

  15. #15
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi again

    Your problem seems to be theat you are using merged cell for to hold the dates (ie A7:A8), therefore when you delete to date the cursor moves to the second merged cell (A8) and the 'Target.Address' changes to $A$7:$A$8 (not $A$7) so the If condition is then false.

    I have moded the code to look for a referance to $A$7 or "$A$8 OR $A$7:$A$8) which not seem to work.

    Code:
    Private Sub Worksheet_Change(ByVal Target As Range)
        Dim ws As Worksheet
        Dim i As Long
    
        If InStr(Target.Address, "$A$7") <> 0 Or InStr(Target.Address, "$A$8") <> 0 Then
            For Each ws In Worksheets
                i = i + 1
                On Error Resume Next
                If Not IsDate(Range("A7")) Then
                    ws.Name = "Cert Period " & i
                Else
                     ws.Name = Format(ws.Range("A7"), "m-dd-yy") & " THRU " & Format(ws.Range("F7"), "m-dd-yy")
                End If
                If Err.Number <> 0 Then
                    MsgBox "Could not rename sheet " & ws.Name, vbCritical, "Renaming Error"
                    Err.Clear
                End If
            Next ws
        End If
    End Sub
    However I would recommend (for simplicity!) that, if possible, you remove the merged cells if this is not required, and use the origional code.

    This would be easier to maintain, will not course any other similar problems !

    MTB

Posting Permissions

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