Why don't you put the change on the menu page instead of each change... this
will need more error trapping etc but a start...

'Declare global var for the sheet name
Dim strName As String


Private Sub Worksheet_Change(ByVal Target As Range)
On Error Resume Next
Sheets(strName).Name = Target.Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
strName = Target.Value
End Sub


"JDaywalt" wrote:

I have a "menu" page in a workbook that contains a table of numeric values 1
to 15 that represent the default sheet tab names in this workbook. I want
the user to have the option of changing this list of values to a more
meaningful text description, then have the corresponding sheet tab names
change automatically.

Currently I have this VB code built into each of the 15 worksheets:

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Name = Sheets("Xref").Range("D11").Value
End Sub

This code only works if I go to each tab afterwards & make some other type
of change. Clearly I am missing something here. Can someone help?