VBA command to update sheet name from cell

[Velz]

Hi Everyone, I apologize in advance for my rudimentary excel skills - I'm just learning this fantastic language. In my current project, I would like to have a sheet name updated based on a user input into a specific cell. I am currently using the formula:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$B$1" Then
Name = [b1].Text
End If
End Sub

What I wish to accomplish is to have a user input a name into a previous sheet, and have that input become this sheets name. Is this possible? and if so, please help!

Thanks

 

[Jerry Sullivan]

Hello and Welcome,

The code below should give you a start.

Private Sub Worksheet_Change(ByVal Target As Range)
    If (Intersect(Target, Range("B1")) Is Nothing) Then Exit Sub
    On Error GoTo ErrorHandler
    Dim lngIndex As Long
    Dim strName As String
 
    lngIndex = ActiveSheet.Index
    strNewName = ActiveSheet.Range("B1")
 
    Worksheets(lngIndex + 1).Name = strNewName
    Exit Sub
ErrorHandler:
    MsgBox "Not able to rename Sheet(" & lngIndex _
        & ") to " & strNewName
End Sub

This code will generate an error message in these two cases:
If the ActiveSheet is the last sheet in the workbook
If the New Sheet Name is being used by another worksheet

You could improve the code by testing for those conditions first and taking some other action.
(for example, if it is the last sheet- add a new sheet).

Good luck!

 

[Velz]

Thank you for the help JS!