How to assign a reference to [Validation .add] xlValidateList(?)

[tbd]

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!

 

[Joel]

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]

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]

I don't know how smart macrosoft programmers are. I know there are a lot of
poor descions where made by programmers at miicrosoft in developing different
products incluing windows and office. there are lots of problems that
microsoft never fies becauwe it would affect million of users if bugs were
corrected because customers softwae would also have to be fixed. sio I don't
know if names cells in excel uses links when using named ranges which will
use less memory and would be fster to execute; then performing a lookup of
the named ranges. I would like to think a link was used.

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!

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.

 

[K_Macd]

tbd

The way to go is to set up your validation list in another sheet then give
it a range name (anchor it beyond the last row so you can add new items
without having to redefine the range name) then refer to the sheet and range
name from the validation dialog. By the way I only found this the other day
after looking for about 5 years.

Joel

You were a little bit unkind to the fine folks at MS but what an interesting
problem to have - fix the faults and alienate those who have developed
work-arounds or continue to alienate the new-be's who rely on the
documentation or good programming practice to get things done.

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"

I don't know how smart macrosoft programmers are. I know there are a lot of
poor descions where made by programmers at miicrosoft in developing different
products incluing windows and office. there are lots of problems that
microsoft never fies becauwe it would affect million of users if bugs were
corrected because customers softwae would also have to be fixed. sio I don't
know if names cells in excel uses links when using named ranges which will
use less memory and would be fster to execute; then performing a lookup of
the named ranges. I would like to think a link was used.

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!

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.

:

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!

 

[tbd]

Hi Ken,
Thanks for the feedback. Using named-range reference - which I've
been fighting all day - seems to solve two problems: I was seeing an error
when defining more that 20 items on the pull-down; and it seems the list is
truely a reference! - excellent. On the other hand, Excel/VBA syntax is
definately "quirky" - fortunately there are lots of good people & examples on
the web. ;^) BTW, I vote for fixing the problems.

Hey, just noticed your "signature" - dBaseIII was my second language! Man,
after doing file handling under IBM Basic, I thought Ashton-Tate rulled!
Those were the days...

Cheers!

tbd

The way to go is to set up your validation list in another sheet then give
it a range name (anchor it beyond the last row so you can add new items
without having to redefine the range name) then refer to the sheet and range
name from the validation dialog. By the way I only found this the other day
after looking for about 5 years.

Joel

You were a little bit unkind to the fine folks at MS but what an interesting
problem to have - fix the faults and alienate those who have developed
work-arounds or continue to alienate the new-be's who rely on the
documentation or good programming practice to get things done.

--
Ken
"Using Dbase dialects since 82"
"Started with Visicalc in the same year"

I don't know how smart macrosoft programmers are. I know there are a lot of
poor descions where made by programmers at miicrosoft in developing different
products incluing windows and office. there are lots of problems that
microsoft never fies becauwe it would affect million of users if bugs were
corrected because customers softwae would also have to be fixed. sio I don't
know if names cells in excel uses links when using named ranges which will
use less memory and would be fster to execute; then performing a lookup of
the named ranges. I would like to think a link was used.

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!
:

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.

:

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!