Change sheet name based on cell value from another sheet tab

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JDaywalt - 24 Mar 2008 16:06 GMT

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?

Reply to this Message

lochmant - 24 Mar 2008 16:24 GMT

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

> 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
[quoted text clipped - 10 lines]
> 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?

Reply to this Message

Anant.Basant@gmail.com - 24 Mar 2008 16:36 GMT

On Mar 24, 8:06 pm, JDaywalt <JDayw...@discussions.microsoft.com>
wrote:

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

Reply to this Message