Calc 2.0: switching validation lists

[Villeroy]

Obsolete since version 2.3
OOo2 only
On the calc-forum there where several questions about dynamic validation lists.
In XL you can calculate the validation-list of a cell range like
=IF(someValue=1;list1;list2). When you apply such a formula in Calc's
validation-dialog, you get an empty list of valid entries.
This is a very simple set of routines, which assignes a validation-list,
selected by some listbox, to a target range. In fact testing took more time than writing this stuff.
-----------------------
Install the macro:
Tools > Macros > Organize Macros > OpenOffice.org Basic
In container "my macros" select some library (eg. "Standard" and some
module. The organization is up to you). Edit the module.
Paste the code into your module.
----------------------
Assumed we have two validation lists @ $sheet1.$A$1:$A$7 and $sheet1.$B$1:$B$7
and an input range @ $sheet2.$A$1:$A1000:
1. Turn on design-mode (Toolbar "Form Design"). The current Calc-document must
be saved, before we can get into design-mode.
2. Draw a listbox (Toolbar "Form Controls")
When a wizzard pops up: cancel it, since no databases are involved(well, not
in this example)
3. Call the properties-dialog of the new listbox
4. Fill the listbox with selectable items:
In tab "General", property "List Entries" type/paste $sheet1.$A$1:$A$7, press
Shift+Enter, type/paste $sheet1.$B$1:$B$7.
5. There is a property "Additional Info"(3rd last property)
Set this property to the address of the target range $sheet2.$A$1:$A100 (the
address of the range which will get the selected validation list).
6. Select tab "Events", assign the macro "DynValidation_Changed" to the
action-event (the first event in the dialog) by clicking on the first
[...]-button and navigating to the place, where you put the macro.
7. [optional] Assign event "Mouse pressed" to macro "DynValidation_RightClick"
This will show a message about the target-range(s) when you right-click on the
listbox.
8. turn off design-mode and enjoy.
--------------------
The macro sets the validation-type of the target-range to "List" and the
validation-list is set to the selected range-address or range-name. It will not change/set any other properties like info-message or warning-message.
Remarks on validation-lists
This is one of OOo's new features and it is not well documented.
Using a relative address (A1:A10) and assigning this address as
validation-list of B1:B100, you get B2:B11 in B2, B3:B12 in B3,... which is
propably not what you want. A mixed ref like $A1:$A10 could be useful, but in
general you want absolute addressing with explicit sheet name
($sheet1.$A$1:$A$10). Omitting the sheet-name sets the validation-list to the
declared range of the current sheet, where the validation takes place.
You may use a range name instead of the address. In the example above, fill
your listbox with items "myList1" and "myList2", where "myList1" refers to
$sheet1.$A$1:$A$7 and "myList2" refers to $sheet1.$B$1:$B$7.
Remarks on the selectable validation lists implemented by this macro
Of course you can put your list of validation-lists into a cell range and
assign this range as source-range of your listbox. Use the "Source
Range"-property from tab "Data" of the listbox-properties in step 4, instead
of setting the single list-items.
Once you have successfully created your listbox, you can copy the listbox in
design-mode. Several copies of the same listbox can be synchronized by setting
the "Linked Cell"-property. Unfortunatly, the macro won't be triggered by
changing this cell.
Surprise: Selecting an empty item in your listbox, your target-range gets a
validation-list with all text-values of current column.
Remarks on target ranges
You can apply the selected validation-list on multiple ranges.
In step 5. use a semicolon-separated list like
sheet1.A1:A1000;sheet2.B1:B20;sheet3.D4:E123. Or use a single range name referring to such a range.
The target-range is evaluated by it's address-string. Relative addressing of the target-range has no effect at all. A single target-range without a sheet-name applies to each sheet of the document.
-------------------------------------------------------
FINALLY ...
... all this is obsolete?
Well, you can not calculate the position of a validation-list by some formula.
But you can calculate the content of a validation-list!
Define some named ranges:
"Items" refers to A1:A100 and has "Item1","Item2",..."Item100"
"Weekdays" refers to B1:B7 and has "Monday","Tuesday",...
"Validation" refers to C1:C100 and has an array formula:
{=CHOOSE($D$1;Items;Weekdays)}
D1 is 1
Set validation of some unused range to range "Validation".
Change D1 to value 2.
-------------------------------------------------------
Anyway. Here is the code for changing the position of a validation-list:

[controlc]

I'll need to play with this, but it looks really promising...

Many, many, thanks!

[Villeroy]

Hi controlc,
You are the one from the calc-forum, who asked for fruits and vegetables.
Please let me know, if the macro works for you.

I prefer doing things without macros.
May be you are interested in this:
http://www.oooforum.org/forum/viewtopic.phtml?t=28935
It provides a solution for a more complex task:
How to modify a validation list, so it contains names of a sorted category/name list, depending on selected category.

[controlc]

Today is my first day back in the office after the holidays, so I'll need a couple of days before I can dig into this, but I am very interested in trying this and appreciative of the help.

^C (Eric)

[vitcaro]

This snippet was very usefull for me.
Validity can really be a powerfull tool.
I want to give my contribution with a simple example:

[Villeroy]

Next release will give us some relief, so we do not need ugly macros for dynamic validation lists: http://blogs.sun.com/GullFOSS/entry/ensuring_unique_entries_with_cell

Spreadsheet validation is rather primitive. It does not prevent cells from pasting over, removal nor clearing. When you need a more serious validation, you should use a database.