Automatically change sheet names

Question

The following code adds/deletes sheets and inserts a list of all the sheet names while making them hyperlinks:

https://stackoverflow.com/a/48159499/9102830

The following code changes the sheet name depending on several cell names:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Address(0, 0) = "C13" Then
    Sh.Name = Sh.Range("B33").Value + "_" + Sh.Range("C13").Value + "_" + Sh.Range("C22").Value + "_N01"
End If
End Sub

The questions are:

Instead of changing when the cell input changes, can this be done automatically?
Could it be a part of the "Sub add_sheet" code? Like, if I add a sheet, it should be named depending on the cells, and since it is copied and the cells are the same, the name will obviously be taken, in which case the name should be "N02" instead of "N01" (instead of the default "(2)" that Excel does).
Finally, when/if the cells are changed, so that the sheet name is not equal to a previous sheet name, could it automatically go back to "N01"?

Thanks in advance!

What criteria would make it automatic? Seems to me when a cell changes and your sheet name is updated in the process, it's automatic. — K.Dᴀᴠɪs
Problem is that the "Private Sub Workbook_SheetChange" only works when the cell input is changed by entering the cell and changing the input, however, my cells are changed based on input in other cells, hence the "Private Sub Workbook_SheetChange" does not recognize the change. — DFH

K.Dᴀᴠɪs K.Dᴀᴠɪs · Accepted Answer · 2018-01-10T09:41:27

There are a few ways you can handle that. One way is to combine Worksheet_Activate with Worksheet_Calculate, and throw in a public variable.

When you first open up your worksheet, you will automatically set the variable, and the variable will set with every change as well.

Option Explicit

Public myVar As Variant

Private Sub Worksheet_Activate()

    'Set the public variable
    myVar = Range("B33").Value

End Sub

Private Sub Worksheet_Calculate()

    If myVar <> Range("B33").Value Then
        sh.Name = ...
        myVar = Range("B33")
    End If

End Sub

You can also set the variable when you open the workbook with Workbook_Open as well. Really just depends on your particular needs.

Automatically change sheet names

1 Answers