Hi,

Excel 2002.

I like the Data Validation with the list option but I have come across
two problems with the way I want to use it.

1. The source reference cells must be on the same sheet as the data
entry cell(shame!).
2. After creating the validation rules, if I add an extra entry to my
array, I need to go back to the Data / Validation option and update
the value to include the new entry.

I can get round 1 by copying the table from Sheet2 to Sheet1 (not
ideal solution though).

I cannot see how to update the validation rules in VBA. I've looked at
the object model but cannot see what I need to update - if I can at
all.

Basically, I have Sheet2 where there is a list of companies. Users can
update this list at will.

On sheet1 I want them to enter data and only enter valid company names
(hence Data / Validation). However, when new companies are added they
need to be copied to Sheet1 then, somehow, I need to update the Data /
Validation programatically. How can I do this?

Is there are better way to do this than use the Data / Validation
route?

Look at Debra Dalgleish's site:
http://contextures.com/xlDataVal01.html

You'll see that you can name a range on another sheet and use that as the source
for the list in your data|validation dialog.

And you can use a range that grows and contracts depending on the entries:
http://contextures.com/xlNames01.html#Dynamic

John Google wrote:

Hi,

Excel 2002.

I like the Data Validation with the list option but I have come across
two problems with the way I want to use it.

1. The source reference cells must be on the same sheet as the data
entry cell(shame!).
2. After creating the validation rules, if I add an extra entry to my
array, I need to go back to the Data / Validation option and update
the value to include the new entry.

I can get round 1 by copying the table from Sheet2 to Sheet1 (not
ideal solution though).

I cannot see how to update the validation rules in VBA. I've looked at
the object model but cannot see what I need to update - if I can at
all.

Basically, I have Sheet2 where there is a list of companies. Users can
update this list at will.

On sheet1 I want them to enter data and only enter valid company names
(hence Data / Validation). However, when new companies are added they
need to be copied to Sheet1 then, somehow, I need to update the Data /
Validation programatically. How can I do this?

Is there are better way to do this than use the Data / Validation
route?

--

Dave Peterson

John

If you name the range you can have it on another worksheet.

See Debra Dalgleish's site for instructions.

http://www.contextures.on.ca/xlDataVal08.html#Refer

Also, you can use a Dynamic Range for the source, which will expand or decrease.

http://www.contextures.on.ca/xlNames01.html#Dynamic


Gord Dibben MS Excel MVP

On Sun, 04 Nov 2007 09:39:18 -0800, John Google
wrote:

Hi,

Excel 2002.

I like the Data Validation with the list option but I have come across
two problems with the way I want to use it.

1. The source reference cells must be on the same sheet as the data
entry cell(shame!).
2. After creating the validation rules, if I add an extra entry to my
array, I need to go back to the Data / Validation option and update
the value to include the new entry.

I can get round 1 by copying the table from Sheet2 to Sheet1 (not
ideal solution though).

I cannot see how to update the validation rules in VBA. I've looked at
the object model but cannot see what I need to update - if I can at
all.

Basically, I have Sheet2 where there is a list of companies. Users can
update this list at will.

On sheet1 I want them to enter data and only enter valid company names
(hence Data / Validation). However, when new companies are added they
need to be copied to Sheet1 then, somehow, I need to update the Data /
Validation programatically. How can I do this?

Is there are better way to do this than use the Data / Validation
route?

To both Gord and Dave,

I've tried it and it works! I didn't think about naming a range. I
didn't realise that you could name a range rather than an individual
cell.

Thanks for the tip regarding rows for named ranges.

This now does exactly what I want without any clumsy copying of data.

Thanks a lot!

Grateful John.

Thanks for the feedback John.


Gord

On Sun, 04 Nov 2007 10:25:55 -0800, John Google
wrote:

To both Gord and Dave,

I've tried it and it works! I didn't think about naming a range. I
didn't realise that you could name a range rather than an individual
cell.

Thanks for the tip regarding rows for named ranges.

This now does exactly what I want without any clumsy copying of data.

Thanks a lot!

Grateful John.