Clif McIrvin explained :
wrote in message
...
Paul,
Thanks for the quick response. I copied the code you replied with
into the VBA editor and place it on the Master Sheet object. I saved
and then started to enter lastnames from B13 to B30. The names of the
Stud1 - Stud18 worksheets didn't change. Was there a part of your
code that I needed to edit or customize for my workbook? Oh, I am
using Excel 2007. Any suggestions?
Sounds like you want the sheet name change to happen "automagically" whenever
you change the student last names on your master sheet.
What Paul gave you is macro code that you have to invoke for it to run (he
told you how in his original post.) There are very good reasons for
preferring a macro that the user must invoke over using magic code .... not
the least of which is that (often) the user of the worksheet knows nothing of
the magic, and when something breaks it sometime in the future it can be a
nightmare to find and fix. Heck -- I forget about the magic that I write into
my own worksheets that will never be used by anyone else. (Never is a looong
time!)
Will you ever want to change a sheet name a second (or third, etc.) time?
(For instance, if the name was typed incorrectly and needs to be changed, or
if a student has a legal name change.) If you ever want to change a sheet
that is NOT named Stud1 - Stud18 Paul's code will not handle that.
The macro could be converted to a worksheet change event procedure to make it
"automagic," but it would still only work to rename a sheet named Stud1 -
Stud18.
If you want the code to also handle future name changes this becomes a
problem with a much more involved solution.
I'm thinking that these sheets pre-exist and so is why the rename is
required. I'd be more inclined to just copy a 'master' sheet for each
student and rename it as I do that.
I'm curious why a separate sheet for each student Normally, student
score data uses 1 sheet per class/course and contains a list of all
students in that class/course. Subject details are then stored in
columns, followed by total/final grade scores/averages. IOW, if the
only difference on the sheets will be the student name then 1 sheet
would suffice regardless of subject detail.
--
Garry
Free usenet access at
http://www.eternal-september.org
ClassicVB Users Regroup! comp.lang.basic.visual.misc