Changing a tabs name

aladinalamp

New Member
Joined
Sep 15, 2006
Messages
31
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
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Scott Huish

MrExcel MVP
Joined
Mar 17, 2004
Messages
19,958
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Try this in the Worksheet Change event (this is assuming Column A):

Code:
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

New Member
Joined
Sep 15, 2006
Messages
31
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

MrExcel MVP
Joined
Aug 21, 2004
Messages
16,995
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
Code:
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
 

Forum statistics

Threads
1,141,720
Messages
5,708,078
Members
421,545
Latest member
TWR

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top