Changing a tabs name

[aladinalamp]

Is it possible to change the name of a tab by the data that is entered into the first sheet. For example if I was entering a list of names into sheet 1 and I wanted those names to appear as Tabs. Can I make this happen automatically?

A B C D
1 MIKE
2 JOHN
3 RICH
4 PAUL
5
6

Sheet 1 MIKE JOHN RICH PAUL

 

[Scott Huish]

Try this in the Worksheet Change event (this is assuming Column A):

Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Dim sn, from As String
from = ActiveSheet.Name
If Target.Column <> 1 Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target = "" Then Exit Sub
sn = Worksheets(Target.Text).Name
If sn = "" Then
    Worksheets.Add after:=Sheets(Worksheets.Count)
    ActiveSheet.Name = Target
    Worksheets(from).Activate
End If
End Sub

To put this in:
Copy the code above
Right click on the tab for the sheet you want this to happen on
Click on View Code
This brings up the Visual Basic Editor (VBE)
Paste the code into the white area.
Hit Alt-q
When you save the workbook, the code will be saved with it.

 

[aladinalamp]

Nope

Target cells are b3 to b11 and the tabs I want to react are tabs 8 to 16.
thanks for your first post though,

 

[jindon]

Re: Nope

Target cells are b3 to b11 and the tabs I want to react are tabs 8 to 16.
thanks for your first post though,

try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim msg As String, ws As Worksheet, m As Object
With Target
    If Intersect(.Cells, Range("b13:b11")) Is Nothing Then Exit Sub
    If IsEmpty(.Value) Then Exit Sub
    If Sheets(.Row + 5).Name = .Text Then Exit Sub
End With
With CreateObject("VBScript.RegExp")
    .Pattern = "[\\\/\*\?\[\]:]"
    .Global = True
    If .test(Target.Text) Then
        For Each m In .execute(Target.Text)
           msg = msg & m.Value & vbLf
        Next
        MsgBox "Invalid character(s)" & vbLf & msg
        Exit Sub
    End If
End With
On Error Resume Next
Set ws = Sheets(Target.Text)
On Error GoTo 0
If Not ws Is Nothing Then
    MsgBox Target.Text & " is already used"
    Exit Sub
Else
    If Sheets.Count >= Target.Row + 5 Then
        Sheets(Target.Row + 5).Name = Target.Text
    Else
        MsgBox "Not enough number of sheets in this workbook"
    End If
End If
End Sub