Changing Tabs to Specific Cells

[Darren]

Is there a way to change the tab names (20 of them) to the cells in say
A1:A20? When the names are changed in the cells, I need the tabs names to
reflect what the cells reflect.

 

[Jason Morin]

Darren-

Place this code in the appropriate sheet module:

Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Me.[A1:A20]

If Target.Row > ActiveWorkbook.Worksheets.Count Then Exit Sub

On Error GoTo ErrHandler
Sheets(Target.Row).Name = Target.Value
Exit Sub

ErrHandler:
MsgBox "Invalid sheet name. Try again."
Target.Select

End Sub

 

[Jason Morin]

Oops...forgot one extremely important line of code:

If Intersect(Target, rng) Is Nothing Then Exit Sub

Add this under the "Set rng = ..." line. Sorry.

Jason

Darren-

Place this code in the appropriate sheet module:

Sub Worksheet_Change(ByVal Target As Range)

Dim rng As Range
Set rng = Me.[A1:A20]

If Target.Row > ActiveWorkbook.Worksheets.Count Then Exit Sub

On Error GoTo ErrHandler
Sheets(Target.Row).Name = Target.Value
Exit Sub

ErrHandler:
MsgBox "Invalid sheet name. Try again."
Target.Select

End Sub

---
HTH
Jason
Atlanta, GA

Is there a way to change the tab names (20 of them) to the cells in say
A1:A20? When the names are changed in the cells, I need the tabs names to
reflect what the cells reflect.