Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
In searching for some help I found the opposite of my questions but couldn't
make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
Not without VBA.
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JICDB" wrote in message ... In searching for some help I found the opposite of my questions but couldn't make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
My typo was supposed to say with OR without VBA. I don't mind VBA I just
don't know how to do it. "Bob Phillips" wrote: Not without VBA. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JICDB" wrote in message ... In searching for some help I found the opposite of my questions but couldn't make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
I'm having a hard time understanding your request. It looks like you want the
roster sheet name to to automatically update to equal the value in C3 on that sheet. how does that help you with the 18 sheets (1 for each player)? Or, do you mean that C3 on every player's sheet has the player's name, pulled from a cell on the roster sheet? In any case, as Bob wrote, you need VBA to do this. The following code, when placed in the code module for a worksheet, will make that sheet's tab name equal the value in C3 on that sheet whenever it changes (unless C3 contains characters which are invalid for a sheet name). Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo WCerr 'If the cell changed was C3, set the sheet name to the value in C3. If Target.Address = Range("C3").Address Then ActiveSheet.Name = Target.Value End If Exit Sub WCerr: MsgBox "Could not update sheet name" End Sub To add this code to a sheet, right-click on the sheet tab and select View Code. Paste the code into the sheet module in the Visual Basic Editor, which is displayed. Repeat for each sheet where you want this automatic name updating to occur. Hope this helps, Hutch "JICDB" wrote: In searching for some help I found the opposite of my questions but couldn't make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
Sorry Tom I guess I wasn't clear. The roster has a list of players in column
C. The players name appears in row 3. For the first tab I would set the name to =C3 in the roster tab. Tab 2 would be set to =C4 in the roster tab and so on. Thanks for the suggestion. I'll give it a try. "Tom Hutchins" wrote: I'm having a hard time understanding your request. It looks like you want the roster sheet name to to automatically update to equal the value in C3 on that sheet. how does that help you with the 18 sheets (1 for each player)? Or, do you mean that C3 on every player's sheet has the player's name, pulled from a cell on the roster sheet? In any case, as Bob wrote, you need VBA to do this. The following code, when placed in the code module for a worksheet, will make that sheet's tab name equal the value in C3 on that sheet whenever it changes (unless C3 contains characters which are invalid for a sheet name). Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo WCerr 'If the cell changed was C3, set the sheet name to the value in C3. If Target.Address = Range("C3").Address Then ActiveSheet.Name = Target.Value End If Exit Sub WCerr: MsgBox "Could not update sheet name" End Sub To add this code to a sheet, right-click on the sheet tab and select View Code. Paste the code into the sheet module in the Visual Basic Editor, which is displayed. Repeat for each sheet where you want this automatic name updating to occur. Hope this helps, Hutch "JICDB" wrote: In searching for some help I found the opposite of my questions but couldn't make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Worksheet name = cell name
Sub SetNames
Dim sh As Worksheet Dim i as long For Each sh In Activeworkbook.Worksheets If sh.Name < Activesheet.Name Range("C3").Offset(i,0).Value = sh.Name i = i + 1 End If Next sh End Sub Select the sheet to add the names and run the macro. -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "JICDB" wrote in message ... Sorry Tom I guess I wasn't clear. The roster has a list of players in column C. The players name appears in row 3. For the first tab I would set the name to =C3 in the roster tab. Tab 2 would be set to =C4 in the roster tab and so on. Thanks for the suggestion. I'll give it a try. "Tom Hutchins" wrote: I'm having a hard time understanding your request. It looks like you want the roster sheet name to to automatically update to equal the value in C3 on that sheet. how does that help you with the 18 sheets (1 for each player)? Or, do you mean that C3 on every player's sheet has the player's name, pulled from a cell on the roster sheet? In any case, as Bob wrote, you need VBA to do this. The following code, when placed in the code module for a worksheet, will make that sheet's tab name equal the value in C3 on that sheet whenever it changes (unless C3 contains characters which are invalid for a sheet name). Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo WCerr 'If the cell changed was C3, set the sheet name to the value in C3. If Target.Address = Range("C3").Address Then ActiveSheet.Name = Target.Value End If Exit Sub WCerr: MsgBox "Could not update sheet name" End Sub To add this code to a sheet, right-click on the sheet tab and select View Code. Paste the code into the sheet module in the Visual Basic Editor, which is displayed. Repeat for each sheet where you want this automatic name updating to occur. Hope this helps, Hutch "JICDB" wrote: In searching for some help I found the opposite of my questions but couldn't make it work here. I am developing a spreadsheet for volleyball stats. There is a tab for each player (18 of them) One of the tabs is the roster which contains among other things the player name. I want to set the name of the tab (worksheet) to the Roster tab cell C3. This would enable the user to change the roster each season and not have to change all 18 player tabs. Is there a way to do this in Excel with our without VBA? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I get ONLY new info from 1 Worksheet to another automatical | Excel Worksheet Functions | |||
fill cell with color from other worksheet | Excel Discussion (Misc queries) | |||
Conversion of Cell Contents into a Functional Worksheet name ? | Excel Worksheet Functions | |||
macro help | Excel Discussion (Misc queries) | |||
Refrencing another cell in a worksheet that "could" exist | Excel Worksheet Functions |