Change sheet name based on cell value from another sheet tab
 

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?

Change sheet name based on cell value from another sheet tab
 

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?

Change sheet name based on cell value from another sheet tab
 

On Mar 24, 8:06*pm, 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?

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