Changing Worksheet Name based on formula

[BigViking]

I am creating an annual calendar with a tab for each month as well as a parameter tab. Users can select the start month in the parameter tab and all the other tabs will update automatically. The only thing that I cannot get to change automatically is the tab names. I have found various codes out there that allow the tab names to be changed based on a cell value, however, when that cell contains a formula (to show the relevant month) it will not rename the tab. Can anyone assist me with this? Thank you

 

[kevin9999]

What is the formula, presumably on the 'parameter' tab, and what do you want the tab names to change from and to?

 

[BigViking]

What is the formula, presumably on the 'parameter' tab, and what do you want the tab names to change from and to?

Each worksheet has a field =TEXT(B12,"mmmm") which simply pulls the month from a separate date field. I want this month name to be the tab name

 

[kevin9999]

from a separate date field

And where is the "separate date field"? What I'm trying to determine is whether a Worksheet Change or Calculate event could be used to automatically change all the tab names. You said in your initial post:

Users can select the start month in the parameter tab

How is this done?

 

[kevin9999]

Also, where is the TEXT formula located on each sheet?

 

[kevin9999]

While I'm waiting for your responses, here's a demo of what's possible with worksheet change event code. For the purpose of the demonstration, it's assumed that your parameters sheet is called "parameters" and that a change in cell A1 of that sheet (manually or by dropdown selection etc.) will trigger the code to run.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim ws As Worksheet
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> "parameters" Then
                ws.Name = Format(ws.Range("B12").Value, "MMM")
            End If
        Next ws
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[kevin9999]

Couple of tweaks on the previous code - but also runs when a change is manually made to cell A1 in your parameters sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim ws As Worksheet, s As String, i As Long
        s = Me.Name
        'give the sheets a temporary name
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> s Then
                ws.Name = "Temp " & i
                i = i + 1
            End If
        Next ws
        'give the sheets their new name
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> s Then
                ws.Name = Format(ws.Range("B12").Value, "MMM")
            End If
        Next ws
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

 

[BigViking]

And where is the "separate date field"? What I'm trying to determine is whether a Worksheet Change or Calculate event could be used to automatically change all the tab names. You said in your initial post:

How is this done?

So, the 'Parameters' tab has a drop down list in B6 where you can select the start month. On the other tabs, the =TEXT(B12,"mmmm") formula in W2

Couple of tweaks on the previous code - but also runs when a change is manually made to cell A1 in your parameters sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("A1"), Target) Is Nothing Then
        On Error GoTo Escape
        Application.EnableEvents = False
        Dim ws As Worksheet, s As String, i As Long
        s = Me.Name
        'give the sheets a temporary name
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> s Then
                ws.Name = "Temp " & i
                i = i + 1
            End If
        Next ws
        'give the sheets their new name
        For Each ws In ThisWorkbook.Worksheets
            If ws.Name <> s Then
                ws.Name = Format(ws.Range("B12").Value, "MMM")
            End If
        Next ws
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub

Oh wow! This worked! Thank you so much!! You have no idea how long I have been trying to figure this out :^)

 

[kevin9999]

Happy to help Big Viking, and thanks for the feedback