I was surprised that JE's suggestion worked--I wouldn't have guessed that it
would. (Thanks JE for the suggestion.)

But personally, I'd drop the:
dim Departments as worksheet
set departments = worksheets("departments")
just so you could use: departments.cells(1,1)

You could have also used: departments_.cells(1,1)

But I'd just change the codename to Departments--There's nothing wrong with
using the same name for the codename and sheet name. It might make the code
easier to understand, too.

And there's nothing wrong with using variable names like:
dim PriceList as range
set pricelist = worksheets("prices").range("PriceList")

(It's kind of the same philosophy.)

GeorgeJ wrote:

I'd like to thank both of you guys for your very helpful info. Here's what
I've discovered, based upon your suggestions

1) Each worksheet has both a Name and a (Name) property. If this isn't
wierd enough, these properties correspond to the reference to the sheet seen
in the VBA project explorer in the *opposite* way from what one would expect,
e. g. the sheet described in the project explorer window as Sheet1
(Departments) has

Name = Departments

and

(Name) = Sheet1

go figure!

2) If you select a particular sheet in the project explorer window and then
right-click and then select VBA properties, you *don't* get the display
needed to change Name or (Name). To get this display you need to press F4,
as Dave mentions. I'm grateful to Dave for pointing this out to me. I never
would have guessed to do this. I think Msft should make this operation more
intuitive. Where is it discussed?

3) Contrary to JE's suggestion (as I understood it), if you change both the
Name and the (Name) of a sheet to Departments, and if you also declare a
global variable Departments of type worksheet, then a reference to

Departments.sub1

will cause VBA to interpret this a a field of the object variable
Departments and flag an error. JE's suggestion that Departments be declared
simply a variable of type object appears to work, but I'm not sure whether or
not there are disadvantages to doing this

FYI, here's how I've decided to handle the situation

For a worksheet

Sheet1 (Departments)

I will use F4 to change it to

Departments_(Departments)

Then the complier will understand that

Departments_.sub1

refers to a subroutine on the code module associated with
Departments_(Departments)

and

Departments.cells(1,1) represents the upper left cell on the worksheet
referenced by the VBA worksheet variable Departments, which has been set by
the code

Set Departments = ActiveWorksheet.Sheets("Departments")

Once again, thanks to you both for your help



--

Dave Peterson