Worksheet name = cell name

[JICDB]

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?

[Bob Phillips]

Not without VBA.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"JICDB" <[email protected]> wrote in message
news:[email protected]...
> 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?

[JICDB]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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?
>
>
>

[Tom Hutchins]

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?

[JICDB]

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?

[Bob Phillips]

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" <[email protected]> wrote in message
news:[email protected]...
> 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?