Listes de validation dans OpenOffice Calc

Distribué par le projet Fr.OpenOffice.org

Révision [n° 0.2] – 05/08/04

Réalisé avec : OOo 1.1.1	Plate-forme / Os : Toutes

Table des Matières

1 But de ce how-to	3

2 Méthode n°1 : la fonction « Liste de sélection »	3

2.1 Principe	3

2.2 Mise au point	3

2.3 Comment empêcher les saisies directes ?	4

2.4 Conclusion	5

3 Méthode des champs de formulaire	5

3.1 Et pourquoi pas une base de données ?	5

3.2 Les avancées de la version 1.1.1 d'OpenOffice	6

3.3 Le complément proposé	6

3.4 Conclusion	8

4 Troisième méthode : une macro et c'est (presque) tout !	8

4.1 Principe	8

4.2 La macro :	10

4.3 Conclusion	13

5 Crédits	14

6 Licence	14

But de ce how-to

Ce how-to est destiné à fournir une illustration d’ensemble des possibilités d’utilisation de listes de validation dans OpenOffice.org Calc.

Excel dispose au sein de son menu Données/Validation, de la possibilité bien pratique de définir une liste de validation, dont on indique les coordonnées sur la feuille. Les cellules ainsi « verrouillées » se trouvent alors munies d’une flèche déroulante encadrant et facilitant la saisie. Tout en aidant l’utilisateur, on s’assure du même coup de l'homogénéité des données entrées ce qui garantit leur bonne exploitation ultérieure (consolidation et traitement). Il n’est pas étonnant dès lors que les tableaux Excel soient largement employés dans les organisations pour faire remonter des données lorsque les ‘serveurs de formulaire’ ne sont pas trop développés.

Si Calc comporte, comme Excel, la possibilité de définir des critères de validation sur les données entrées dans une plage de cellules (menu Données/Validité), les listes de validation ne sont hélas pas prises en charge pour l’instant, du moins pas de façon aussi simple et transparente qu’avec Excel.

En attendant une intégration dans une prochaine version (il semble que la v2 constituera l’avancée décisive...), nous allons voir comment parvenir dès maintenant à un résultat proche avec OpenOffice.org en utilisant des options existantes ou des macros. On souhaite notamment pouvoir traiter des plages entières de données.

Méthode n°1 : la fonction « Liste de sélection »

Principe

Cette commande OpenOffice.org Calc est associée à la combinaison de touches CTRL+D mais peut bien sûr être réaffectée. Lorsqu'on la déclenche dans une cellule, elle propose dans une liste toutes les valeurs textuelles de la colonne, qui auront été placées par exemple dans les premières lignes de cette colonne.

Mise au point

La commande comporte un certain nombre d'inconvénients, qui pour la plupart, peuvent être contournés. Le tableau suivant les présente.

Inconvénients relevés	Contournements proposés
La liste ne présente que des valeurs textuelles	Formater les nombres et les dates en tant que texte en les précédant de ' (apostrophe) Attention : il faut désactiver la correction automatique des guillemets simples ! On notera que ces valeurs de liste textuelles seront bien transformées en nombres ou dates à l'utilisation.
La liste est encombrante	Masquer les lignes concernées
Le titre de la colonne est pris comme valeur dans la liste	Remplacer les titres par des 'zones de texte' ou mieux, afin de conserver la notion de titre, les saisir en tant que formule par : ="TITRE"
La liste est triée par ordre alphabétique ce qui peut être gênant (ex : liste des jours...)	Pas de solution véritable ici mais on peut toujours forcer un ordre déterminé en faisant précéder les valeurs de la liste par un numéro, au prix cependant d'un embarquement de ces numéros dans les saisies.

Comment empêcher les saisies directes ?

Il faut empêcher l'utilisateur de contourner le cadrage établi en saisissant directement n'importe quelle valeur. C'est de plus vital ici car toute valeur saisie différente de celles présentes dans une liste s'ajoutera à cette liste dès la saisie suivante !

Pour établir ce blocage, on utilisera astucieusement les fonctionnalités offertes par le menu « Données/Validité ». On peut ainsi n'admettre par exemple que des valeurs de longueur de texte égale à 0 c'est à dire en fait aucune ! Ne pas omettre d'établir l'action 'STOP' et le message d'erreur. On notera cependant que la protection n’est pas parfaite puisqu’elle n’empêche pas les écritures par copier/coller ou recopie de série ; on ne peut pas en effet utiliser la protection de feuille qui bloquerait aussi...l’emploi de CTRL+D !

On peut améliorer le dispositif en prévoyant dans le même menu un message d'aide utile et du plus bel effet (affichage d'une infobulle).

On notera en passant que la rubrique « Action » peut référencer une macro ce qui permet d’envisager des développements intéressants sur le thème des procédures évènementielles de cellules...

Conclusion

Cette méthode un peu ‘artisanale’ paraît recevable en première approche à condition de préparer le tableau avec soin grâce aux astuces présentées plus haut.

Méthode des champs de formulaire

Des connaissances au niveau de la manipulation des champs de formulaire sont requises pour la mise en œuvre de cette méthode.

Et pourquoi pas une base de données ?

Compte tenu de la nature du problème posé, il est légitime de penser à l'utilisation d'une base de données couplée à un formulaire de saisie d'autant que cette voie est bien dans la philosophie d'OpenOffice. La base contiendrait à la fois la table recueillant les données saisies ainsi que la ou les tables servant de sources aux listes.

Toutefois cette voie est loin d'être simple tant à la conception qu'à l'utilisation :

connaissances requises en bases de données et en interface de formulaire, y compris pour contourner des anomalies existantes à ce niveau ;

installation d'un driver ODBC ou autre dans Windows ;

déclaration d'une source de données dans OpenOffice ;

problème du transport de la base d'un poste à l'autre, aggravé lorsque la base est répartie en plusieurs fichiers (cas de MySQL);

traitement ultérieur des données plus compliqué

rappelons également que l'emploi d'une feuille Calc comme source de données n'est pas possible car les données sont en lecture seule au travers du formulaire.

En définitive ce système semble davantage correspondre à des environnements dans lesquels la bureautique est étroitement intégrée à l’informatique générale quoique même dans ce cas, il serait sans doute aujourd’hui plus pertinent de s’orienter vers des solutions Web à base par exemple de serveurs de formulaires.

En conséquence, cette option ne sera pas présentée dans le cadre de cet How‑to.

Pour la méthode exposée dans cette partie, les champs de formulaire ne seront donc pas liés à une source de données ; c'est pourquoi lors de leur création à l'aide de la barre d'outils de formulaire, il faudra, si l'autopilote de contrôle de formulaire est actif (cas général), faire 'Annuler' au premier écran.

Les avancées de la version 1.1.1 d'OpenOffice

La possibilité de lier un champ de formulaire (ListBox ou ComboBox), en entrée avec une plage de cellules, et en sortie avec une cellule synchronisé au choix dans la liste, dont la perspective avait été annoncée dans le How‑to « Lier un contrôle de formulaire à une cellule de classeur », a été curieusement implémentée à l'occasion de la version mineure 1.1.1.

NB : La plage de cellules source peut être placée sur une autre feuille du classeur ; il suffit de préfixer l’adresse par le nom de la feuille (ex : Feuille2.B2:B5).

Hélas toutes ces possibilités, si elles peuvent convenir pour quelques cellules à servir, ne sont plus du tout utilisables dès qu'il s'agit de plages importantes (il faudrait mettre en place des centaines de champs de formulaire !). Sauf à compléter le dispositif avec une macro...

Le complément proposé

Une macro sera chargée de déterminer la cellule active pour y injecter la valeur choisie dans la liste. Les champs de formulaire pourront alors servir comme cartouche unique de saisie. On les positionnera par exemple en tête des colonnes dans un volet figé.

Volet figé

La propriété 'Cellule liée' sera laissée vide. Par contre, l'évènement 'Souris relevée' sera lié à la macro 'SertCellule' dont voici un code possible :

sub SertCellules (Event as object)

dim oCell as object, oCoord as object, oControl as object, oControlM as object

dim numChoixListe as integer, noCol as integer

oControl = Event.Source 'qui m'appelle ?

oControlM = oControl.Model

'calcul num de colonne (limité à 26 col...)

noCol = asc(ucase(oControlM.tag)) - 65 'récupérée de propriété "Info complémentaire"

if CalcQuelleTypeSelection() <> 1 then

msgbox ("Une seule cellule doit être sélectionnée !",48,"Attention:")

exit sub

end if

oCell = thisComponent.currentSelection

oCoord = oCell.cellAddress

if oCoord.column <> noCol then

msgbox ("Utiliser la bonne liste !",48,"Attention:")

exit sub

end if

if oControlM.supportsService("com.sun.star.form.component.ListBox") then

oCell.formula = oControl.selectedItem 'LIST SEUL

elseif oControlM.supportsService("com.sun.star.form.component.ComboBox") then

oCell.formula = oControl.selectedText 'COMBO

end if

'numChoixListe = oControl.selectedItems(0) ' le n° de l'item choisi LIST

'oCell.formula = oControl.stringItemList(numChoixListe) 'LIST

end sub

function CalcQuelleTypeSelection as integer

dim oSelection as object, nReturn as integer

oSelection = thisComponent.currentSelection

If oSelection.supportsService("com.sun.star.sheet.SheetCell") Then

nReturn = 1 ' une cellule

ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRange") Then

nReturn = 2 ' une zone

ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then

nReturn = 3 ' plusieurs zones

End If

CalcQuelleTypeSelection = nReturn

end function

Ce programme vérifie qu'il n'y a pas de sélection dans la feuille (une seule cellule active !) et contrôle également qu'on utilise la bonne liste dans la bonne colonne. C'est pour permettre ce dernier contrôle qu'il faut indiquer le numéro de colonne (en lettre) dans la propriété « Info complémentaire » de chaque champ liste.

Le choix de l'évènement « souris relevée » permet d'injecter la valeur en seul clic.

Le verrouillage de la zone de saisie est assuré par la méthode expliquée au 2.3 Comment empêcher les saisies directes ?.

Conclusion

Pour le concepteur, cette méthode a l'inconvénient de requérir des manipulations sur les champs de formulaires pour être mis en place, en plus de la macro. La place occupée en haut de la feuille peut gêner mais sera réduite en employant des ComboBox au lieu de ListBox.

Pour l'utilisateur, le principe du cartouche de saisie peut dérouter mais la vitesse de saisie est très rapide si on s'organise bien (sélection de la zone avec la cellule active en haut à gauche, utilisation de la touche TAB dans la main gauche et souris dans la main droite sur les listes, le contraire pour un gaucher avec la touche ENTER).

Rappelons également que cette méthode ne fonctionne que depuis la version 1.1.1 d’OpenOffice. Avec une version antérieure, il faudrait également gérer par macro le remplissage des listes (voir le How‑to « Lier un contrôle de formulaire à une cellule de classeur ».

La méthode des champs de formulaire ne sera donc pas conseillée d’autant qu’on va le voir, le système exposé au point suivant combine tous les avantages. Mais outre son caractère illustratif des outils d’OpenOffice, elle pourra servir de point de départ à des développements alternatifs : on citera comme exemple la génération de champs de formulaires par programmation et leur positionnement sur la cellule active.

Troisième méthode : une macro et c'est (presque) tout !

Principe

La macro, appelée par une touche, va se charger de présenter la liste adéquate dans un « pop‑up » et d'injecter la valeur choisie dans la cellule active.

Un seul clic sur un item de liste suffit à injecter la valeur ce qui procure la même rapidité que la méthode précédente. Les boutons OK a cependant été maintenu par sécurité (dans ce cas, on emploiera le clavier pour sélectionner l’item).

On nommera ‘Liste_Validation_XXX’ chaque zone abritant une liste (qui peut se trouver sur n’importe quelle feuille), le « XXX » correspondant au nom de la zone à saisir. Voici un exemple :

	Menu : Insertion/Noms/définir ou CTRL + F3 La zone de liste doit être sur une colonne (mais on peut en faire plusieurs dans la même colonne) Elle peut se trouver d’autre part sur n’importe quelle feuille. Exemple ci-contre : Zone de liste Liste_Validation_Jours

	...qui correspond à la zone de saisie nommée : Jours La casse des noms donnés n’a pas d’importance.

Le verrouillage de la zone de saisie est assuré cette fois par la protection de feuille (sans mot de passe), que la macro va gérer si elle existe (protection retirée avant l’écriture et replacée après). On obtient ainsi une sécurité renforcée contre les saisies directes. Notez qu’il peut s’avérer alors utile d’inclure une cellule vide (ou munie d’un libéllé ayant cette signification) dans les zones de liste.

Le menu Données/Validité conserve cependant une utilité pour disposer d’une infobulle d’aide sur la zone de saisie (onglet [Aide à la saisie]).

La macro :

'************************************************

'Copyright (C) 2004 MINEFI DGDDI

'Ecrit par Bruno Moutouh

'bruno.moutouh@douane.finances.gouv.fr

'This library is free software; you can redistribute it and/or

'modify it under the terms of the GNU Lesser General Public

'License as published by the Free Software Foundation; either

'version 2.1 of the License, or (at your option) any later version.

'This library is distributed in the hope that it will be useful,

'but WITHOUT ANY WARRANTY; without even the implied warranty of

'MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU

'Lesser General Public License for more details.

'You should have received a copy of the GNU Lesser General Public

'License along with this library; if not, write to the Free Software

'Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA

'************************************************

REM ***** BASIC *****

Option Explicit

' variables publiques

dim oDlgListeValidation as object

dim bClicDansListe as boolean

Sub SaisieListe

' tous les noms de zones de liste commencent par :

Const NOM_COMMUN_LISTES = "Liste_Validation_"

' suivi du nom de la zone de saisie (casse indifférente)

Const MOT_PASSE_FEUILLE = ""

dim oCellSaisie as object, oCellListe as object

dim oListeBox as object

dim oNames as object, oName as object, cName as string

dim oNameSaisie as object, cNameSaisie as string

dim oNameListe as object, cNameListe as string

dim oZoneSaisie as object, oSheetSaisie as object

dim oZoneListe as object, oSheetListe as object

dim oCoordSaisie as object, oCoordListe as object

dim numChoixListe as integer, noCol as integer

dim sRef as string, aRef() as string

dim sRefSheet as string, sRefZone as string

dim oCoordZoneListe as object, nbLignesZoneListe as integer

dim aListe(), i as integer

dim nm as integer, cNomListe as string

dim nRetourDlg as integer

dim bProtected as boolean

if CalcQuelleTypeSelection() <> 1 then

msgbox ("Une seule cellule doit être sélectionnée !",48,"Attention:")

exit sub

end if

oCellSaisie = thisComponent.currentSelection ' où est-on ?

' Recherche sur les noms normalisés de liste

' et récup du nom de zone de saisie correspondant

' puis on vérifie que la cellule active est bien dans cette zone de saisie

' si c'est le cas le popup se déclenche

oNames = thisComponent.namedRanges

for nm=0 to oNames.getCount()-1

oName = oNames.getByIndex(nm)

cName = oName.getName

if left(cName,len(NOM_COMMUN_LISTES))= NOM_COMMUN_LISTES then

cNameListe = cName

oNameListe = oName

cNameSaisie = mid(cNameListe,len(NOM_COMMUN_LISTES)+1)

if not oNames.hasByName(cNameSaisie) then

msgbox("Le nom de zone de saisie '"+cNameSaisie+"' est inconnu !",48,"Attention : problème potentiel")

exit sub

end if

oNameSaisie = oNames.getByName(cNameSaisie)

' recherche coord zone saisie

sRef = oNameSaisie.getContent()

aRef = split(sRef,".")

sRefSheet = right(aRef(0),len(aRef(0))-1) 'enlever le $ devant pour la suite !!!

sRefZone = aRef(1)

oSheetSaisie = thisComponent.sheets.getByName(sRefSheet)

oZoneSaisie = oSheetSaisie.getCellRangeByName(sRefZone)

oCoordSaisie = oZoneSaisie.RangeAddress

if CalcIsInZone(oCellSaisie,oZoneSaisie) then

' déclencher le popup

' recherche coord zone liste

sRef = oNameListe.GetContent()

aRef = split(sRef,".")

sRefSheet = right(aRef(0),len(aRef(0))-1)

sRefZone = aRef(1)

oSheetListe = thisComponent.sheets.getByName(sRefSheet)

oZoneListe = oSheetListe.getCellRangeByName(sRefZone)

oCoordListe = oZoneListe.RangeAddress

if oZoneListe.columns.count >1 then

msgBox("Zone mal définie: ne doit contenir qu'une seule colonne !",48,"Attention:")

exit sub

end if

redim aListe(oCoordListe.startRow to oCoordListe.endRow)

for i = oCoordListe.startRow to oCoordListe.endRow

oCellListe = oSheetListe.getCellByPosition(oCoordListe.startColumn, i)

aListe(i) = oCellListe.formula

next i

' appel du dialog DLG_ListeValidation et remplissage de la liste LB_ListeValidation

DialogLibraries.LoadLibrary("Standard")

oDlgListeValidation = createUnoDialog(DialogLibraries.Standard.DLG_ListeValidation)

oListeBox = oDlgListeValidation.GetControl("LB_ListeValidation")

oListeBox.addItems(aListe(),0)

bClicDansListe = false 'init

nRetourDlg = oDlgListeValidation.execute()

if bClicDansListe or nRetourDlg=1 then

' on écrit

if oSheetSaisie.isProtected() then	bProtected = true

if bProtected then	oSheetSaisie.unprotect(MOT_PASSE_FEUILLE)

oCellSaisie.formula = oListeBox.selectedItem

if bProtected then oSheetSaisie.protect(MOT_PASSE_FEUILLE)

endif

oDlgListeValidation.dispose()

exit for 'popup actionné, terminé

end if	'cell in zone saisie

endif 'nom de liste à nous repérée

next 'autre nom à examiner

end Sub

function CalcQuelleTypeSelection as integer

dim oSelection as object, nReturn as integer

oSelection = thisComponent.currentSelection

If oSelection.supportsService("com.sun.star.sheet.SheetCell") Then

nReturn = 1 ' une cellule

ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRange") Then

nReturn = 2 ' une zone

ElseIf oSelection.supportsService("com.sun.star.sheet.SheetCellRanges") Then

nReturn = 3 ' plusieurs zones

End If

CalcQuelleTypeSelection = nReturn

end function

function CalcIsInZone (cell as object, zone as object) as boolean

' détermine si une cellule fait partie d'une zone nommée

dim oCoordCell as object, oCoordZone as object

oCoordCell = cell.cellAddress

oCoordZone = zone.rangeAddress

if oCoordCell.sheet = oCoordZone.sheet and _

oCoordCell.column >= oCoordZone.startColumn and _

oCoordCell.column <= oCoordZone.endColumn and _

oCoordCell.row >= oCoordZone.startRow and _

oCoordCell.row <= oCoordZone.endRow then

CalcIsInZone = true

else

calcIsInZone = false

endif

end function

sub ChoixListeDirect (oEvent as object)

' lancée par evenement mouseUp du control de liste LB_ListeValidation

if oEvent.source.selectedItemPos >=0 then

oDlgListeValidation.endExecute()

bClicDansListe = true

endif

end sub

La boite de dialogue « DLG_ListeValidation » avec une zone de liste « LB_ListeValidation » doit être construite au niveau du module abritant la macro.

La macro sera enfin associée à une touche, par exemple F2 (associer SaisieListe) ou F12 pour un gaucher.

Conclusion

C'est une méthode :

transparente : on est libéré de la gestion des champs de formulaire ou des « bricolages » du CTRL+D ;

simple à mettre en oeuvre : si on utilise un modèle (comme CalcListesValidationMacro.stc mis en ligne sur l’espace documentaire fr.openoffice.org), les éléments techniques sont automatiquement mis en place ; tout ce qui est requis du concepteur est ensuite de savoir nommer des zones, activer la protection de feuille et l’infobulle d’aide à la saisie ;

universelle puisqu’on est libre de définir les zones qui conviennent ;

rapide : grâce à l’injection sur simple clic, deux actions élémentaires seulement sont requises (une frappe clavier et un clic de souris) ;

la plus puissante potentiellement : on peut disposer de toutes les variantes imaginable en retouchant la macro (par exemple, pour distinguer ce qui est présenté dans le « pop‑up » et ce qui sera injecté dans la cellule)

C'est donc cette voie qui a largement ma préférence ce qui n’empêche pas d’espérer par ailleurs une intégration totalement ‘Excel‑like’ dans la prochaine version 2 de votre suite bureautique préférée, qui semble devoir nous réserver une très bonne surprise à ce niveau...

Crédits

Auteur : Bruno Moutouh

Remerciement : tous les contributeurs du projet documentation, qui par leurs remarques pertinentes, m’ont permis d’améliorer sensiblement le modèle « CalcListesValidationMacro.stc ».

Intégré par : Sophie Gautier

Dernière modification : [date]

Contacts : Projet Documentation OpenOffice.org - Fr.OpenOffice.org

Traduction :

Licence

Appendix

Public Documentation License Notice

The contents of this Documentation are subject to the Public Documentation License Version 1.0 (the "License"); you may only use this Documentation if you comply with the terms of this License. A copy of the License is available at http://www.openoffice.org/licenses/PDL.html.

The Original Documentation is Listes de validation dans OpenOffice Calc The Initial Writer of the Original Documentation is Bruno Moutouh Copyright (C) 2004 MINEFI DGDDI. All Rights Reserved. (Initial Writer contact(s): bruno.moutouh@douane.finances.gouv.fr).

Contributor(s): ______________________________________.

Portions created by ______ are Copyright (C)_________[Insert year(s)]. All Rights Reserved. (Contributor contact(s):________________[Insert hyperlink/alias]).

NOTE: The text of this Appendix may differ slightly from the text of the notices in the files of the Original Documentation. You should use the text of this Appendix rather than the text found in the Original Documentation for Your Modifications.

OpenOffice.org Documentation Project How-To

Fr.OpenOffice.org	3 / 14

Listes validation dans Calc

Sophie Gautier

2004-07-13T11:04:47

Bruno Moutouh