Rename sheet tab based on value in a cell

  • Thread starter Thread starter Dr.Indera
  • Start date Start date
D

Dr.Indera

hello,

i'm working on a student grade book and use a separate sheet in the workbook
for each student. i type the students name in cell b1 and then have to
manually rename each sheet with the students name. i was wondering if there
was a way to have each tab automatically renamed based on the value in cell
b1?

thanks
indera
 
Indera,

Put this code in the ThisWorkbook code module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

If Target.Address = "$B$1" Then
Sh.Name = Target.Value
End If

End Sub


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
hi bob,

i'm sorry for taking so long to get back to this newsgroup.
i will try the code shortly.

thank you.
indera
 
hi don,

thank you. i will try out the code.

indera


Don Guillett said:
how about
for each ws in worksheets
ws.name =[b1]
next

--
Don Guillett
SalesAid Software
(e-mail address removed)
Dr.Indera said:
hello,

i'm working on a student grade book and use a separate sheet in the workbook
for each student. i type the students name in cell b1 and then have to
manually rename each sheet with the students name. i was wondering if there
was a way to have each tab automatically renamed based on the value in cell
b1?

thanks
indera
Click to expand...
Click to expand...
 
Indera,

FYI, Don's solution will go through your workbook and change the existing
sheet names to whatever is in B1 of that sheet. My solution changes the
sheet name whenever B1 in any sheet is changed. So Don's solution handles
what is today, mine handles what happens tomorrow.

You might want a combination of both.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Dr.Indera said:
hi don,

thank you. i will try out the code.

indera


Don Guillett said:
how about
for each ws in worksheets
ws.name =[b1]
next

--
Don Guillett
SalesAid Software
(e-mail address removed)
Dr.Indera said:
hello,

i'm working on a student grade book and use a separate sheet in the workbook
for each student. i type the students name in cell b1 and then have to
manually rename each sheet with the students name. i was wondering if there
was a way to have each tab automatically renamed based on the value in cell
b1?

thanks
indera
Click to expand...
Click to expand...
Click to expand...
 
Back
Top