Change Worksheet Tab Names based on a cell reference on another worksheet

[MADBusiness]

Hey there

I am creating a template which has a variables set up page.

On that page I want to provide the user the ability to enter values into cells which generate tab names. There is a finite number of tabs - so want to have a direct link between a cell reference and a specific tab name.

My research has led me to believe there may be vba code I can enter on each tab to reference a cell on that worksheet. But I have no idea how to do this. In fact if there is a way without the use of vba, would be awesome so I can keep my file as macro free.

Thanks

Danny

 

[jorismoerings]

As to my knowledge this can't be done without VBA.
Take a look at this:https://www.extendoffice.com/documents/excel/2762-excel-dynamic-name-sheets-from-cell-values.html#dynamic

 

[gallen]

This code does as you ask. It will need amending to suit. Please read comments carefully

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    'this sub must be placed in the 'ThisWorkbook' object
    'this sub assumes sheet4 is the settings sheet
    'this sub assumes there are cells on the settings sheet named '_Sheet1Name', '_Sheet2Name' & '_Sheet3Name'
    
    'Handle any errors
    On Error GoTo errHandle
    
    If Target.Parent.Name = Sheet4.Name Then 'ensure user has changed a cell on  the settings sheet
        Select Case Target.Address
            Case Range("_Sheet1Name").Address
                'user has changed the '_Sheet1Name' cell
                Sheet1.Name = Target
            Case Range("_Sheet2Name").Address
                Sheet2.Name = Target
            Case Range("_Sheet3Name").Address
                Sheet3.Name = Target
        End Select
    End If
    
Exit Sub


errHandle:
    MsgBox Err.Description & vbNewLine & "Name not Updated", vbCritical, Err.Number
    Target.Select
End Sub

 

[My Aswer Is This]

If I understand your question correctly you want to be able to enter a sheet name into a Cell on the Template sheet.

And when you enter that value you want to create a new sheet and a link to that sheet

So if you enter "Alpha" into Range("A1") on the Template sheet you want a new sheet created named "Alpha"

And then when you click on Alpha you will be taken to the sheet named "Alpha"

If that is what you want then try this:

This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab named Template
Select View Code from the pop-up context menu
Paste the code in the VBA edit window


This script will run when you enter any value in column A of the Template sheet.

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  8/22/2019  1:36:59 AM  EDT
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
    If Target.Column = 1 Then
        On Error GoTo M
        Sheets.Add(After:=Sheets(Sheets.Count)).Name = Target.Value
        Hyperlinks.Add Anchor:=Target, Address:="", SubAddress:=Sheets(Target.Value).Name & "!" & Range("A1").Address, TextToDisplay:=Sheets(Target.Value).Name
    End If
Exit Sub
M:
MsgBox "We had a problem the sheet named  " & Target.Value & vbNewLine & " May already exist or you may be trying to use a improper name."
End Sub

 

[MADBusiness]

This code does as you ask. It will need amending to suit. Please read comments carefully

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    'this sub must be placed in the 'ThisWorkbook' object
    'this sub assumes sheet4 is the settings sheet
    'this sub assumes there are cells on the settings sheet named '_Sheet1Name', '_Sheet2Name' & '_Sheet3Name'
    
    'Handle any errors
    On Error GoTo errHandle
    
    If Target.Parent.Name = Sheet4.Name Then 'ensure user has changed a cell on  the settings sheet
        Select Case Target.Address
            Case Range("_Sheet1Name").Address
                'user has changed the '_Sheet1Name' cell
                Sheet1.Name = Target
            Case Range("_Sheet2Name").Address
                Sheet2.Name = Target
            Case Range("_Sheet3Name").Address
                Sheet3.Name = Target
        End Select
    End If
    
Exit Sub


errHandle:
    MsgBox Err.Description & vbNewLine & "Name not Updated", vbCritical, Err.Number
    Target.Select
End Sub

Bloody perfect - tried the simpler versions first - they kept trying to change the name of the tab ALL OF THE TIME. Gallen you are a deadset champion.

Thanks so much for taking the time mate.

Danny