Hi Joel,
Sorry if I didn't explain well. I'm using VBA to control a worksheet
where a set of cells are configured for Validation with a listbox (AKA
"drop-down".) [Using VBA] it IS possible to use any sheet when 'Add'ing the
listbox - this works for me:

[during Workbook open-event handling]
ActiveWorkbook.Names.Add Name:="myrange", _
RefersToR1C1:="='Settings'!R10C3:R57C3"
'NOTE: The listbox choices come from the sheet named "Settings"
[/]
[executed once during Worksheet init...]
Public Sub Range_Validation(rRange As Range)
'NOTE: rRange is NOT on the "Settings" sheet
With rRange.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="=myrange"
[/]

My original question related to how the listbox choices are stored AFTER the
..Add method. If a "reference" can be used, then specifying Validation for a
single cell might cost as much memory as the reference - perhaps four or
eight bytes. If the choices are always stored as actual data, there are
memory-use consequences. Imagine one string is 10 characters, there are 100
strings (100 choices) and there are 1000 cells where each cell needs a copy
of the list. That's a meg of data Excel would have to manage. My experience
with Excel 2002 and VBA leaves me skeptical that this will work well.

I'm definately an Excel/VBA nube, but have used the Macro-recorder. With
respect to Validation source, I never figured-out how to select the listbox
choices from a different sheet (as required and shown in the working code
above. )

Thanks/Cheers!
"Joel" wrote:

The source box in a validation list can either contain an array of values or
use a range of cells in the worksheet when you have the option set to "LIST".
If you set up a validation list manually on the worksheet you can select the
source to be a range of cells on the same worksheet as the validation list.
Excel doesn't allow you to use a different worksheet.

If you need the VBA code for settting up the range just record a macro while
you perform the operation manually.

"tbd" wrote:

Greetings,
With VBA I'm currently specifying a long list of xlValidateList items
(formula1:= CSVlist) for each of many cells. There is only one list
(constructed at run-time), but there could be hundreds of values, and there
may be hundreds of cells for which the user will select a unique value. I'm
not sure, but I think Excel typically keeps a seperate xlValidateList for
each cell - if so, this will be very inefficient.

1) Is it possible for Excel 2002 to implement the Validation list by
reference to a data-structure or range - so that the "one list" only ever
exists in one place, but many cells refer to it?

2) Please confirm: when VBA executes the following assignment:

formula1:="=$n$1:$n$18"

, the expression "=$n$1:$n$18" is evaluated immediately(?)

Any help is appreciated,
Cheersr!