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?

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?
>
>

On Mar 24, 8:06*pm, JDaywalt <JDayw...@discussions.microsoft.com>
wrote:
> I have a "menu" page in a workbook that contains a table of numeric values1
> 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?

Hi,

I have created the sheet "Main" in excel like the following:
Col A Col B
Sheet Index Sheet Name
1 Main
2 Export
3 Report2
4 Report3
5 Report4
6 Report5
7 Report6

and written this sheet change macro.
You can try this and edit according to your requirement.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngShtNames As Range
Dim i As Integer

If Target.Column <> 2 Then Exit Sub

For i = 1 To Me.Cells(Me.Rows.Count, 2).End(xlUp).Row - 1
On Error Resume Next
Sheets(i).Name = Me.Cells(i + 1, 2).Value
If Err.Number <> 0 Then
MsgBox "Can't change the name of " & Me.Cells(i, 2).Value
End If
Next i
End Sub