I know, I will create several named ranges per department consisting of
a number of employees but what I don't want is to manually change the
named range each time an employee is added. For that specific part I
want something in vba.


Bob Phillips wrote:

That is what I was saying in my earlier response, so you need to maintain
separate lists IMO.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ixtreme" wrote in message
oups.com...
Bob,

thanks for your reply. What I mean is that I create name ranges today
for each employee that belongs to eg department AAA. Tomorrow I will
add a new user that also belongs to department AAA. I want something in
the workbook change event or deactivate event that will automatically
update my previous defined range. On the other sheet I use the listbox
to refer to the named range and if everything is OK, I can select the
new collegae as well.


Bob Phillips schreef:

Do you mean that you want defined names for Department AAA, and BBB,
etc.?

If so, I think that you will need to create new independent lists of each
with the associated employees, name these and use them in the Data
Validation.

--
---
HTH

Bob

(change the xxxx to gmail if mailing direct)


"Ixtreme" wrote in message
ups.com...
I have a sheet with employees and department on it. What I need is a
pice of vba code that does the following:

Update defined name ranges automatically upon adding new employees.

The sheet looks like this

A B C
1 Employee 1 Department AAA
2 Employee 2 Department BBB
3 Employee 3 Department AAA
4 Employee 4 Department CCC
5 Employee 5 Department AAA
6 Employee 6 Department DDD

The code should automatically update the values of the defined named
ranges (created via Insert, Name, Define) based on Department if a new
employee is added to this list in cell A7.

On another sheet I have a cell with a list box (via Data, Validation,
allow LIST, where source refers to the (updated) Defined range.

Mark