Auto name worksheet tab from a cell on another worksheet

[NHForester]

I need to update a Tab Name on one worksheet from a manually entered value on another worksheet. I tried modifying the code below and had no luck (almost no VB skills). Any assistance would be appreciated.

Shaun


[CODE
Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3-20-18 2:20 AM EDT
If Not Intersect(Target, Range("B4")) Is Nothing Then
On Error GoTo M
If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub
ActiveSheet.Name = Format(Target.Value, "MM-DD-YY")
End If
Exit Sub
M:
MsgBox "That sheet name already exist or is a improper name"
End Sub
[/CODE]

 

[Joe4]

The code that you posted is only for the current worksheet (pulls nothing from any other worksheet), and you mentioned getting the name from a cell on another worksheet.
Please describe in more detail the sheet which contains the names and which sheet(s) should have their names updated.

 

[NHForester]

Hey Joe,

I was trying to modify the above code and struck out using the Help resources to modify it. Below is what I am trying to accomplish:

The first page of the workbook is 'TS Info', the User will populate a list of tree species with up to 16 text values (B10:B25) and there will be corresponding 16 worksheets. When the User enters a Value in B10 the Name on Sheet 4 would change; when value is changed in B11 Sheet 5 would change etc.... It is possible that all 16 values would be used. I was envisioning that each sheet would have it's own code point to a specific Cell on 'TS Info' not very efficient but got to start somewhere.

Thanks

Shaun

 

[My Aswer Is This]

Your original post said B4

Your last post said B10

So are you saying if you enter:
Sam in Range("B10") you want sheet(4) named Sam
And if you enter Bob in Range("B11") you want sheet(5) named Bob

And will this continue all the way down column B or will it always stop at Range("B25")

So if some one changes a value in B26 the script will not run?

But in your case the value will always be a date

 

[Joe4]

OK, right-click on the sheet tab name at the bottom of your "TS Info" sheet, and enter this code in the resulting VB Editor window.
I think it will do what you want:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    Dim rw As Long

    Set rng = Intersect(Target, Range("B10:B25"))
    
    If rng Is Nothing Then
        Exit Sub
    Else
        For Each cell In rng
            rw = cell.Row
            On Error GoTo err_chk
            Sheets(rw - 6).Name = cell.Value
            On Error GoTo 0
        Next cell
    End If
    
    Exit Sub
    
err_chk:
    MsgBox "That sheet name already exist or is a improper name"
    
End Sub

 

[NHForester]

The code and cell references are the original code I copied from the a post of of this forum. My list will be from B10:B25, and you are correct in your naming of the sheets. These will be text entries (tree species) and not dates. If someone entered a value in B26 nothing should happen.

shaun

 

[Joe4]

The code and cell references are the original code I copied from the a post of of this forum. My list will be from B10:B25, and you are correct in your naming of the sheets. These will be text entries (tree species) and not dates. If someone entered a value in B26 nothing should happen.

.
The code I just posted above should do all that.

 

[My Aswer Is This]

In your original post you had:

ActiveSheet.Name = Format(Target.Value, "MM-DD-YY")

Sounds like your entering dates

Looks like Joe has provided the code you need.
Glad to see that.
​