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?

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?

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?

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?

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?

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?