Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy rows from one Data sheet to another sheet based on cell conte | Excel Discussion (Misc queries) | |||
Help: auto-copy entire rows from 1 sheet (based on cell criteria) to another sheet. | Excel Programming | |||
How can I change sheet tab color based on cell value in sheet? | Excel Programming | |||
Lookup cell contents in on sheet based on a formula in second sheet | Excel Worksheet Functions | |||
Excel VBA (?!)-refer to a cell on Sheet to left of X, based on criteria on Sheet X | Excel Programming |