Sheet naming with VBA based on cell value

[Javi]

Hi All,


I’m trying to modify one of the two codes below so that the work sheet needing the name change does not require that specific sheet to be active or require manual intervention.
This workbook has two sheets currently many more to be added later.

• “Main” summary sheet with data entry fields
• “Sheet1” all data on this worksheet will be referenced from “Main” this worksheet will not be active unless the user is going to print. No edits will ever be made directly on this sheet.

On worksheet “Main” cell C5 contains the name of what I would like “Sheet1” to be renamed

On worksheet “Sheet1” cell A1 is referencing “Main C5”

This configuration works fine with the two below codes, provided I activate sheet1 one and highlight cell A1 and hit enter or make a change somewhere on the sheet.

What I’m trying to accomplish is "Sheet1" to change names without needing any other intervention. When the cell value on worksheet “Main” C5 is changed or modified I would like the worksheet on "Sheet1" name to just change.

Sorry for being so wordy on what maybe should be a very simple question?

My preference would be to use code 2 below as I prefer the error handling but if that’s not possible no big deal.

Code 1

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Set Target = Range("A1")
If Target = "" Then Exit Sub
Application.ActiveSheet.Name = VBA.Left(Target, 31)
Exit Sub
End Sub

Code 2

Private Sub Worksheet_Change(ByVal Target As Range)
    'Specify the target cell whose entry shall be the sheet tab name.
    If Target.Address <> "$A$1" Then Exit Sub
        'If the target cell is empty (contents cleared) then do not change the sheet name
    If IsEmpty(Target) Then Exit Sub

    'If the length of the target cell's entry is greater than 31 characters, disallow the entry.
    If Len(Target.Value) > 31 Then
        MsgBox "Worksheet tab names cannot be greater than 31 characters in length." & vbCrLf & _
        "You entered " & Target.Value & ", which has " & Len(Target.Value) & " characters.", , "Keep it under 31 characters"
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
        Exit Sub
    End If

    'Sheet tab names cannot contain the characters /, \, [, ], *, ?, or :.
    'Verify that none of these characters are present in the cell's entry.
    Dim IllegalCharacter(1 To 7) As String, i As Integer
    IllegalCharacter(1) = "/"
    IllegalCharacter(2) = "\"
    IllegalCharacter(3) = "["
    IllegalCharacter(4) = "]"
    IllegalCharacter(5) = "*"
    IllegalCharacter(6) = "?"
    IllegalCharacter(7) = ":"
    For i = 1 To 7
        If InStr(Target.Value, (IllegalCharacter(i))) > 0 Then
            MsgBox "You used a character that violates sheet naming rules." & vbCrLf & vbCrLf & _
            "Please re-enter a sheet name without the ''" & IllegalCharacter(i) & "'' character.", 48, "Not a possible sheet name !!"
            Application.EnableEvents = False
            Target.ClearContents
            Application.EnableEvents = True
            Exit Sub
        End If
    Next i

    'Verify that the proposed sheet name does not already exist in the workbook.
    Dim strSheetName As String, wks As Worksheet, bln As Boolean
    strSheetName = Trim(Target.Value)
    On Error Resume Next
    Set wks = ActiveWorkbook.Worksheets(strSheetName)
    On Error Resume Next
    If Not wks Is Nothing Then
        bln = True
    Else
        bln = False
        Err.Clear
    End If

    'If the worksheet name does not already exist, name the active sheet as the target cell value.
    'Otherwise, advise the user that duplicate sheet names are not allowed.
    If bln = False Then
        ActiveSheet.Name = strSheetName
    Else
        MsgBox "There is already a sheet named " & strSheetName & "." & vbCrLf & _
        "Please enter a unique name for this sheet."
        Application.EnableEvents = False
        Target.ClearContents
        Application.EnableEvents = True
    End If

End Sub

 

[Fluff]

What cells are the other sheet names in?

 

[Javi]

The source would continue down column C sequentially

I made several attempts see below however, no success. I will end up using cells C5:C58 so 53 worksheets.

 If Target.CountLarge > 1 Then Exit Sub
   
   
   If Target.Address(0, 0) = "C5" Then
         If IsSheetNameOk(Target.Value) Then
             Sheet2.Name = Target.Value
             
          
         If Target.Address(0, 0) = "C6" Then
            If IsSheetNameOk(Target.Value) Then
                Sheet3.Name = Target.Value
       
       End If
       
         
      Else
           

       MsgBox "sheetname " & Target & " is not valid, or already taken"
      End If
   End If
End Sub

Your code is very efficient, I was only referencing a cell on the sheet in which I was changing the name because I did not know otherwise. As I look at your code it appears maybe we can eliminate that step. If it is not necessary I don't need to reference cell A1 in the code below. If we can obtain the name from the main sheet that is just fine. If it's necessary that we keep it as is it's fine I appreciate all your input.

function IsSheetNameOk(ShtName As String) As Boolean
   IsSheetNameOk = False
   If Len(ShtName) < 32 And Not ShtName = "" And Not ShtName Like "*[:/\*[?]*" And Not ShtName Like "*[]]*" And LCase(ShtName) <> "history" Then
      If Not Evaluate("isref('" & ShtName & "'!A1)") Then
         IsSheetNameOk = True
      End If
   End If
End Function

 

[Fluff]

Ok how about

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant
   If Target.CountLarge > 1 Then Exit Sub
   Ary = Array(Sheet2, Sheet3, Sheet4)
   
   If Not Intersect(Target, Range("C5:C7")) Is Nothing Then
      If IsSheetNameOk(Target.Value) Then
         Ary(Target.Row - 5).Name = Target.Value
      Else
         MsgBox "sheetname " & Target & " is not valid, or already taken"
      End If
   End If
End Sub

Just add the other sheets to the array & adjust the C5:C7 range as needed.
This line in the function

If Not Evaluate("isref('" & ShtName & "'!A1)") Then

is checking if the sheet name already exists so needs to stay.

 

[Javi]

Thank you!!! This works perfectly however there is one modification I need.

I have two sheets that I'm using that cannot change the name can we exclude them? I have some user forms and list boxes set up on those two sheets that are referenced by name.

The code identifier is "Sheet7 and Sheet8"

 

[Fluff]

As long as you don't add those codenames to the array they won't get changed.

 

[Javi]

Thank you again I appreciate all the assistance.

Just to clarify as long as I don't call out the sheet identifiers in the array I should be fine?

However, I will have the range covering those sheets. (Target, Range("C5:C60")) Will this create a conflict?

 

[Fluff]

With that range you will need 56 sheets in the array, otherwise you can get an error. For instance if you changed C60 & only had 55 sheets in the array the code will crash as it cant find the 56 item in the array.

 

[Javi]

Thank you for all the help I really appreciate it I'm going to give it a shot to finalize. I was overthinking this.

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Javi]

Everything is working as you designed it thanks again for all your help and expertise, I really appreciated.