Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(?)
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(?)
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! |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(
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. "tbd" wrote: 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! |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(
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" "Joel" wrote: 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. "tbd" wrote: 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! |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
How to assign a reference to [Validation .add] xlValidateList(
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! "K_Macd" wrote: 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" "Joel" wrote: 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. "tbd" wrote: 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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Lookup numbers in sheet and assign it's cell reference | Excel Discussion (Misc queries) | |||
Populate xlValidateList; Please Comment | Excel Programming | |||
Assign macro broken after reference removed | Excel Programming | |||
code to add a name to the names collection and assign a reference | Excel Programming | |||
Assign relative cell reference to variable | Excel Programming |