Updating macros

[Guest]

I have a template in which each worksheet is named, "Assignee 1", "Assignee
2", and so on. (8 total). When information is input into the sheets, they
are renamed with the assignee's name.

The macro on the template, however, is coded to reference "Assignee 1",
"Assignee 2" and so on.

I was wondering if there's a way to either:
1. Set up the macro to refresh along with the worksheet name changes in the
workbook, or

2. Reference the sheets in the macro by their position, not by their names.

Please help!!

And thanks in advance to anyone who offers any explanation!

 

[Frank Kabel]

Hi
you could use the sheet codename instead of the name

 

[Dave Peterson]

Next time you're in the VBE, hit ctrl-r to see the project explorer.

Locate your project/workbook.
It'll look like:
VBAProject (workbooknamehere.xls)
Expand it (using those +'s--like in windows explorer)

Under microsoft excel objects, you'll see things like:

Sheet1(Assignee 1)
Sheet2(Assignee 2)
....
Sheet8(YourNameHere)

The stuff in parentheses is the name you see on the worksheet tab in excel.

The sheet1, sheet2, ..., sheet8 stuff is called the CodeName.

This is pretty difficult for the average user to change (but easy for the
developer to change).

You can protect the project (inside the VBE):
tools|VBAProject properties|protection tab
that makes it even more difficult.

But the good thing is that you can use that codename in your, er, code!

instead of:
with worksheets("assignee 1")

you can use
with sheet1

So the user can rename that worksheet's tab to anything they want and your code
doesn't break.

====
If you want to change the codename, select one of the worksheets (in the project
explorer).
Hit F4 (to see the properties)
and type over the (Name) property (yeah, the one with parentheses.)

 

[Guest]

Dave,

Thanks you very much. I applied your advice, and it works well. I wasn't
able to figure out how to refer to the sheet by name with the Microsoft help,
but you're explanation made sense.

Thanks again,
Jason