Programatically change name of worksheet

Zenaida · #1 (**permalink**) 04-12-06, 17:44

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?

MikeTheBike · #2 (**permalink**) 04-13-06, 03:26

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

Zenaida · #3 (**permalink**) 04-13-06, 11:22

It works perfect. Thanks so much!!

Zenaida · #4 (**permalink**) 04-13-06, 11:28

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?

norie · #5 (**permalink**) 04-13-06, 13:12

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

Zenaida · #6 (**permalink**) 04-13-06, 15:39

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.

MikeTheBike · #7 (**permalink**) 04-18-06, 08:07

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

Zenaida · #8 (**permalink**) 04-20-06, 12:03

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?

norie · #9 (**permalink**) 04-20-06, 12:33

What should the name be when you remove the dates?

Zenaida · #10 (**permalink**) 04-20-06, 13:56

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.

norie · #11 (**permalink**) 04-20-06, 14:01

Just change this.

Code:

ws.Name = "Sheet" & i

To this.

Code:

ws.Name = "Cert Period " & i

Zenaida · #12 (**permalink**) 04-20-06, 14:32

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?

MikeTheBike · #13 (**permalink**) 04-21-06, 04:12

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

Zenaida · #14 (**permalink**) 04-21-06, 11:48

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.

MikeTheBike · #15 (**permalink**) 04-24-06, 08:15

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

Internet Services

Web Development

Digital Marketing

Consumer Tech