Forum at OOoForum.orgThe Forum
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

Calc 2.0: switching validation lists

Post new topic   Reply to topic Forum Index -> Code Snippets
View previous topic :: View next topic  
Author Message
Super User
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Tue Dec 20, 2005 12:47 pm    Post subject: Calc 2.0: switching validation lists Reply with quote

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

Sub DynValidation_Changed(oEv)
on error goto noRangeErr
Dim oDoc,oValRg,sList$,sTag$,oTarget as object,oProps
'myxray oEv.Source
   oDoc = thisComponent
   sList = oEv.Source.SelectedItem
   sTag = oEv.Source.Model.Tag
   'get the target-range from a single range-name (which may have a multiple ref)
   If oDoc.namedRanges.hasByName(sTag) then
      'should be an address or a list like $sheetX.A1:B100;C2:F100;sheet1.B8
      sTag = oDoc.namedRanges.getByName(sTag).Content
   oTarget = getRangesEnumFromString(oDOc,sTag)
   while oTarget.hasMoreElements
      oValRg = oTarget.nextElement
      oProps = oValRg.Validation
      oProps.Formula1 = sList
      oValRg.Validation = oProps
'      myXray oValRg.Validation
exit sub
   MsgBox Chr(34) & sTag & Chr(34) &" is not a valid range"
End Sub
Function getRangesEnumFromString(oDoc,s) as Object
Dim sAddr$,oRanges,sNames(),oSh
   oRanges = oDoc.createInstance("")
   sNames = Split(s,";")
   For each sAddr in sNames()
      oSh = getSheetFromAddressString(oDoc,sAddr)
      if ISNULL(oSh) then
         Dim oSheets
         oSheets = oDoc.Sheets.createEnumeration
         While oSheets.hasMoreelements
            oSh = oSheets.nextElement
   getRangesEnumFromString = oRanges.createEnumeration
end function
Function getSheetFromAddressString(oDOc,s$) as Object
Dim sShName$,oSh,iPos%
   iPos = instr(s,".")
   if iPos > 0 then
      sShName = left(s,iPos -1)
      if Left(sShName,1)= "$" then sShName = mid(sShName,2)
      getSheetFromAddressString = oDoc.Sheets.getByName(sShName)
End Function
Sub DynValidation_RightClick(oEv)
Dim sTag$,sArr(),sMsg$
if oEv.PopUpTrigger then
   sTag = oEv.Source.Model.Tag
   if thiscomponent.namedranges.hasbyName(sTAg) then
      sMsg = "Named Range "& sTag &":;"
      sMsg = sMsg & thiscomponent.namedranges.getbyName(sTAg).Content
   sArr() = Split(sMsg,";")
   sMsg = Join(sArr(),chr(10))
   MsgBox sMsg
end if
End Sub

Last edited by Villeroy on Sat Apr 05, 2008 5:09 am; edited 1 time in total
Back to top
View user's profile Send private message
General User
General User

Joined: 13 Dec 2005
Posts: 19

PostPosted: Mon Jan 02, 2006 9:34 am    Post subject: Great! This looks like it will solve a big problem for me Reply with quote

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

Many, many, thanks!

Very Happy
Back to top
View user's profile Send private message AIM Address
Super User
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Mon Jan 02, 2006 2:20 pm    Post subject: Reply with quote

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:
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.
Back to top
View user's profile Send private message
General User
General User

Joined: 13 Dec 2005
Posts: 19

PostPosted: Tue Jan 03, 2006 8:50 am    Post subject: Thanks! Reply with quote

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)
Back to top
View user's profile Send private message AIM Address
OOo Advocate
OOo Advocate

Joined: 20 Feb 2007
Posts: 256
Location: Italy

PostPosted: Sat Aug 18, 2007 4:48 am    Post subject: Reply with quote

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

sub Main
oDoc = ThisComponent
oSheet = oDoc.getSheets.getByIndex(0)


CellRange = oSheet.getCellRangeByName("B1:B999")
Validation = CellRange.Validation
Validation.Type =
Validation.ShowErrorMessage = True
Validation.ErrorMessage = "This is an invalid value!"
Validation.ErrorTitle = "ERROR"
Formula1 = "0.0"
Formula2 = "5.0"
Validation.ShowInputMessage = True
Validation.InputTitle = "RANGE IS:"
Validation.InputMessage = "Between " & Formula1 & " and " & Formula2
Validation.ErrorAlertStyle =
Validation.setFormula1 "0.0"
Validation.setFormula2 "5.0"
CellRange.Validation = Validation


Set CellRange = oSheet.getCellRangeByName("C1:C999")
Set Validation = CellRange.Validation
Validation.Type =
Validation.ShowErrorMessage = True
Validation.ErrorMessage = "Must be a value from the list!"
Validation.ErrorTitle = "ERROR"
Validation.ShowInputMessage = True
Validation.InputTitle = "INPUT VALUES:"
Validation.InputMessage = "Take a value from the list"
Validation.ErrorAlertStyle =
For z = 1 To 10
    oSheet.getCellByPosition(0, z).setString "ITEM" & z
Validation.setFormula1 "$A$2:$A$11"
CellRange.Validation = Validation
end sub

Back to top
View user's profile Send private message
Super User
Super User

Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Thu Aug 23, 2007 1:46 pm    Post subject: Reply with quote

Next release will give us some relief, so we do not need ugly macros for dynamic validation lists:

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.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic Forum Index -> Code Snippets All times are GMT - 8 Hours
Page 1 of 1

Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum

Powered by phpBB © 2001, 2005 phpBB Group