De nouvelles listes pour vos formulaires Access
Date de publication : 17/02/2008 , Date de mise à jour : 17/02/2008
Par
Philippe JOCHMANS (home page de Starec)
Le but de cet article est de vous montrer comment réaliser de nouvelles listes dans vos formulaires
Access. Des zones de listes avec mise en forme conditionnelle et case à cocher, et des listes
déroulantes avec mise en forme conditionnelle.
I. INTRODUCTION
II. PREREQUIS
III. UNE ZONE DE LISTE AVEC DES CASES A COCHER ET AVEC UNE MISE EN FORME CONDITIONNELLE ILLIMITE
III-A. Cahier des Charges
III-B. Préparation du terrain
III-B-1. Le contrôle OWC
III-B-2. Les boutons de commandes
III-C. Le code
III-C-1. Déclaration des variables
III-C-2. Préparation de la liste
III-C-3. Ajout des données
III-C-4. Finalisation de la liste
III-C-5. Les cases à cocher
III-C-6. Remise à zéro
III-C-7. Récupération de la sélection
IV. ZONE DE LISTE DEROULANTE AVEC UNE MISE EN FORME CONDITIONNELLE ILLIMITE
IV-A. Cahier des Charges
IV-A-1. Le contrôle OWC
IV-A-2. Le bouton de commande
IV-B. Le code
IV-B-1. Déclaration des variables
IV-B-2. Préparation de la liste
IV-B-3. Ajout des données et finalisation
IV-B-4. Dérouler et réduire la liste
IV-B-5. Récupération de la sélection
V. CONCLUSION
VI. TELECHARGEMENT
VII. REMERCIEMENTS
I. INTRODUCTION
Cet article a pour but de vous montrer comment réaliser une zone de liste avec des cases à
cocher et un formatage particulier, ainsi qu'une liste déroulante avec formatage. Nous allons
donc répondre à deux questions qui reviennent régulièrement dans le forum Access.
- Comment mettre des cases à cocher dans une zone de liste.
- Comment formater les lignes d'une zone de liste ou d'une liste déroulante.
En effet le contrôle zone de liste d'Access ne permet pas de réaliser les deux fonctions
demandées, et les zones de listes déroulantes ne permettent pas le formatage conditionnel.
 |
La zone de liste Ms Forms 2.0 ListBox permet l'utilisation des cases à cocher.
|
Pour cela, nous allons utiliser un complément fourni gratuitement par Microsoft
Microsoft Office Web Components.
Ce composant permet de faire 3 choses :
En fait, nous allons détourner le composant de sa fonction initiale pour lui faire faire
d'autres choses, car ce composant est une feuille de calcul. Nous allons donc profiter
d'une partie des propriétés héritées d'Excel.
Tous les essais et codes que vous trouverez ont été réalisés avec Access 2002.
II. PREREQUIS
Une bonne connaissance de VBA est nécessaire pour comprendre l'essentiel de cet article.
Bien que cet article soit pour une utilisation de notre composant sous Access,
une bonne partie de code que vous verrez comprend du VBA Excel, une bonne connaissance
est donc nécessaire pour la compréhension.
III. UNE ZONE DE LISTE AVEC DES CASES A COCHER ET AVEC UNE MISE EN FORME CONDITIONNELLE ILLIMITE
III-A. Cahier des Charges
Réaliser une zone de liste représentant le personnel d'une société,
cette liste devra permettre une sélection multiple (par case à cocher).
De plus, cette liste devra permettre de visualiser facilement les éléments suivants :
- Les noms devront être classés par ordre alphabétique.
- Chaque service devra avoir sa couleur.
En plus de la case à cocher, chaque sélection devra être mise en surbrillance et de couleur
jaune (comme avec un surligneur).
On pourra grâce à un bouton remettre la liste dans sa position initiale.
|
Vous trouverez cette zone de liste et tout son code dans le formulaire
frm_ListeCaseCocher qui se trouve dans la base en téléchargement
en bas de cet article.
|
III-B. Préparation du terrain
Le formulaire sera composé du composant OWC et de deux boutons de commandes.
III-B-1. Le contrôle OWC
Nous allons paramétrer le contrôle manuellement,
cependant, dans le code du formulaire, vous trouverez le code pour le faire par
programmation, il est toujours utile de connaître les deux.
Pour accéder aux propriétés du contrôle, il faut faire un clic droit (menu contextuel),
et sélectionner Microsoft Office SpreadSheet xx.0, et dans le sous-menu
sélectionner Commands et Options.
Onglet Feuille : Dans cet onglet nous allons décocher :
- En-têtes de lignes (DisplayRowHeadings de l'objet Windows).
- En-têtes de colonnes (DisplayColumnHeadings de l'objet Windows).
- Quadrillage (DisplayGridlines de l'objet Windows).
- Pour figer les volets, nous allons nous positionner sur la cellule A2, et cliquer sur l'icône Figer les volets.
Onglet Classeur : Dans cet onglet nous allons décocher :
- Barre de défilement horizontale (DisplayGridlines de l'objet Windows).
- Sélecteur de feuille (DisplayWorkbookTabs de l'objet Windows).
- Barres d'outils (DisplayToolbar de l'objet SpreadSheet).
- Nous allons supprimer les feuilles 2 et 3 pour ne garder que la feuille 1.
De plus nous nommerons le contrôle : spreadListe
III-B-2. Les boutons de commandes
Nous allons donc mettre deux boutons de commandes standard sur notre formulaire.
Réinitialisation de la liste :
- Nom : cmdReset
- Caption : RAZ
Affichage de la sélection :
- Nom : cmdAffichageSelection
- Caption : Affichage de la sélection
III-C. Le code
Nous allons maintenant décortiquer tout le code qui nous permettra de réaliser la zone de liste.
III-C-1. Déclaration des variables
Nous allons déclarer deux variables en entête du module de formulaire (elles seront donc accessibles
partout dans celui-ci).
- Public wksOwc As OWC10.Spreadsheet : Cette variable va représenter le composant feuille de calcul du composant OWC.
- Public wksSheet As OWC10.Worksheet : Cette variable va représenter la feuille en elle-même.
Les affectations de ces deux variables se feront dans l'évènement Load de notre
formulaire avant l'appel des routines qui permettront la réalisation de notre zone de liste.
| VBA |
Private Sub Form_Load()
Set wksOwc = Me.spreadListe.Object
Set wksSheet = wksOwc.Worksheets("Feuille1")
Call PrepaOWC
Call AjoutDonnees
Call FinalisationListe
End Sub
|
|
Le fait de déclarer ces variables nous permettra de bénéficier de l'Intellisense.
|
III-C-2. Préparation de la liste
Nous allons ici voir le code qui permettra de préformater la liste.
| VBA |
Public Sub PrepaOWC()
With wksOwc
.DisplayToolbar = False
With .Windows(1)
.DisplayHorizontalScrollBar = False
.DisplayWorkbookTabs = False
.DisplayColumnHeadings = False
.DisplayRowHeadings = False
.DisplayGridlines = False
End With
.Worksheets("Feuille2").Delete
.Worksheets("Feuille3").Delete
.Windows(1).ViewableRange = "A1:G200000"
End With
With wksSheet
.Range("A1").Value = "Choix"
.Range("B1").Value = "ID"
.Range("C1").Value = "Civilité"
.Range("D1").Value = "Nom"
.Range("E1").Value = "Prénom"
.Range("F1").Value = "Service"
.Range("G1").Value = "Fonction"
End With
With wksOwc.Range("A1:G1")
.Interior.Color = RGB(220, 200, 250)
.Borders.LineStyle = xlContinuous
.Font.Bold = True
End With
End Sub
|
Ce code nous permet donc de :
- Préparer l'aspect de notre liste (barres, etc. ) par le code.
- Mettre les entêtes de colonnes
- Formater cette entête (couleur, police, cadrage).
|
Nous avons figé les volets manuellement, ce qui permettra de laisser toujours notre entête visible.
|
III-C-3. Ajout des données
Nous allons maintenant ajouter les données à notre liste, pour cela nous allons
utiliser la technologie
DAO,
et réaliser le remplissage de notre feuille.
|
Vous trouverez les données dans la base en téléchargement.
|
| VBA |
Public Sub AjoutDonnees()
Dim rst As DAO.Recordset
Dim strSql As String
Dim i As Long
strSql = "SELECT tbl_Personnel.IdPersonnel, tbl_Civilite.strCivAbrege, tbl_Personnel.strNom, tbl_Personnel.strPrenom, " & _
" tbl_Service.strService, tbl_Fonction.strFonction, tbl_Service.lngCouleur " & _
"FROM tbl_Service INNER JOIN (tbl_Fonction INNER JOIN (tbl_Civilite INNER JOIN tbl_Personnel " & _
"ON tbl_Civilite.IdCivilite = tbl_Personnel.IdCivilite) " & _
"ON tbl_Fonction.IdFonction = tbl_Personnel.IdFonction) " & _
"ON tbl_Service.IdService = tbl_Personnel.IdService " & _
"ORDER BY tbl_Personnel.strNom;"
Set rst = CurrentDb.OpenRecordset(strSql)
i = 2
While Not rst.EOF
With wksSheet
With .Range("A" & i)
.HorizontalAlignment = xlHAlignCenter
.Font.Bold = True
End With
.Range("B" & i).Value = rst("IdPersonnel")
.Range("C" & i).Value = rst("strCivAbrege")
.Range("D" & i).Value = rst("strNom")
.Range("E" & i).Value = rst("strPrenom")
.Range("F" & i).Value = rst("strService")
.Range("G" & i).Value = rst("strFonction")
.Range("A" & i & ":G" & i).Interior.Color = rst("lngCouleur")
.Range("A" & i & ":G" & i).Borders.LineStyle = xlContinuous
End With
i = i + 1
rst.MoveNext
Wend
rst.Close
Set rst = Nothing
End Sub
|
Des petits commentaires s'imposent :
- La requête SQL nous permet de récupérer toutes nos données dans les tables
composants la base, vous noterez que nous récupérons le champ lngCouleur de la
table tbl_Service. En effet, chaque service ayant sa propre couleur.
- Ensuite grâce à une boucle While .. Wend, nous remplissons notre grille,
et vous noterez donc que nos dernières lignes intègrent la couleur,
et réalisent le quadrillage.
III-C-4. Finalisation de la liste
Nous allons maintenant finaliser notre liste.
| VBA |
Public Sub FinalisationListe()
Dim lngDerRow As Long
lngDerRow = wksSheet.Range("B1").End(xlDown).Row
wksOwc.Windows(1).ViewableRange = "A1:G" & lngDerRow
wksSheet.Range("B:B").ColumnWidth = 0
wksSheet.Protection.Enabled = True
End Sub
|
Ce code permet donc de (dans l'ordre) :
- Récupérer la dernière ligne.
- Enlever les lignes et les colonnes non remplies pour ne permettre
que l'affichage des lignes nécessaires.
- Mettre la largeur de la colonne B à 0 (pour cacher le champ ID qui est la clé).
- Protéger la feuille pour éviter toute saisie.
Voici maintenant l'aspect final de notre liste.
|
La largeur finale de la liste a été faite manuellement.
|
III-C-5. Les cases à cocher
Le but de notre liste (en plus de la mise en forme conditionnelle) est de nous permettre
de faire des sélections qui doivent être représentées par des cases à cocher et
les lignes doivent être mises en surbrillance (comme avec un surligneur).
Pour cela nous allons agir sur l'évènement Click du composant.
Voici donc le code qui va nous permettre de réaliser cette opération.
| VBA |
Private Sub spreadListe_Click()
Dim lngRow As Long
lngRow = wksOwc.ActiveCell.Row
If wksSheet.Range("A" & lngRow).Value = "x" Then
wksSheet.Protection.Enabled = False
With wksSheet
.Range("A" & lngRow).Value = ""
With .Range("A" & lngRow & ":G" & lngRow)
.Interior.Color = CLng(DLookup("lngCouleur", "tbl_Service", "strService='" & wksSheet.Range("F" & lngRow).Value & "'"))
End With
End With
wksSheet.Protection.Enabled = True
Else
wksSheet.Protection.Enabled = False
With wksSheet
.Range("A" & lngRow).Value = "x"
With .Range("A" & lngRow & ":G" & lngRow)
.Interior.Color = vbYellow
End With
End With
wksSheet.Protection.Enabled = True
End If
End Sub
|
Quelques commentaires sur le principe :
Nous allons donc dans la colonne A mettre la lettre x qui représentera une croix,
donc de visualiser que la case est cochée.
Nous détectons si la cellule contient cette lettre x.
Si elle contient cette lettre :
- On enlève le x et on met une chaîne vide.
- On remet la couleur du service en cherchant dans la table tbl_Service
la couleur correspondante à l'aide de la fonction de domaine DLookup
Si la cellule ne contient pas de x
- Nous mettons un x dans la cellule A de la ligne
- Nous mettons la couleur de fond de toute la ligne en jaune.
|
Avant de réaliser les opérations de modification, nous
déprotégeons la feuille, et ensuite nous la reprotégeons.
|
Voici donc le résultat visuel avec des lignes cochées.
III-C-6. Remise à zéro
Nous allons maintenant créer un code qui nous permettra de ré initialiser notre liste.
Le code est le suivant :
| VBA |
Private Sub cmdReset_Click()
Dim lngDerRow As Long
Dim i As Long
lngDerRow = wksSheet.Range("B1").End(xlDown).Row
wksSheet.Protection.Enabled = False
For i = 2 To lngDerRow
With wksSheet
.Range("A" & i).Value = ""
With .Range("A" & i & ":G" & i)
.Font.Color = vbBlack
.Interior.Color = CLng(DLookup("lngCouleur", "tbl_Service", "strService='" & wksSheet.Range("F" & i).Value & "'"))
End With
DoEvents
End With
Next i
wksSheet.Protection.Enabled = True
End Sub
|
Nous allons tout simplement parcourir la liste et remettre la couleur en fonction
du service grâce à la fonction de domaine DLookup, et remplacer la croix par
une chaine vide.
III-C-7. Récupération de la sélection
Maintenant que nous avons coché nos lignes, il nous faut les récupérer,
cela sera réalisé avec le code suivant.
| VBA |
Private Sub cmdAffichageSelection_Click()
Dim lngDerRow As Long
Dim i As Long
Dim strMessage As String
lngDerRow = wksSheet.Range("B1").End(xlDown).Row
For i = 2 To lngDerRow
If wksSheet.Range("A" & i).Value = "x" Then
strMessage = "Identifiant : " & wksSheet.Range("B" & i).Value & _
" Nom : " & wksSheet.Range("D" & i).Value & vbCrLf & strMessage
End If
Next i
MsgBox "Les lignes sélectionnées" & vbCrLf & vbCrLf & strMessage, vbOKOnly
End Sub
|
Nous allons juste parcourir les lignes, détecter la présence du x,
stocker les données dans une chaîne et les afficher dans une msgbox
(bien sûr en fonctionnement normal, on affiche pas de boites de dialogue).
IV. ZONE DE LISTE DEROULANTE AVEC UNE MISE EN FORME CONDITIONNELLE ILLIMITE
Nous allons voir comment, sur le même principe, réaliser une liste déroulante (Combobox)
qui possède bien sûr une mise en forme conditionnelle.
IV-A. Cahier des Charges
Réaliser une liste déroulante représentant le personnel d'une société.
Cette liste devra permettre de visualiser facilement les éléments suivants (Nom, Prénom).
- Les noms devront être classés par ordre alphabétique.
- Chaque service devra avoir sa couleur.
|
Vous trouverez cette liste déroulante et tout son code dans le formulaire
frm_LiseDeroulante qui se trouve dans la base en téléchargement en bas de cet article.
|
IV-A-1. Le contrôle OWC
Nous allons préparer ce contrôle de la même manière que la zone de liste,
ce contrôle sera nommé spreadCombo.
IV-A-2. Le bouton de commande
Nous allons donc mettre un bouton de commande standard sur notre formulaire,
celui-ci simulera la flêche d'une liste déroulante.
Nous nommerons notre bouton cmdDropDownListe.
Pour avoir une flêche vers le bas, nous allons tout simplement utiliser la police
Wingdings 3 et la lettre q en minuscule.
|
Vous avez d'autres possibilités avec des images, etc.
|
IV-B. Le code
IV-B-1. Déclaration des variables
La déclaration est identique à la zone de liste.
IV-B-2. Préparation de la liste
Voici le code qui permet de préparer la liste déroulante, celui-ci est en tout point
similaire à celui de la zone de liste.
| VBA |
Public Sub PrepaCombo()
With wksSheet
.Range("A1") = "Id"
.Range("B1") = "Nom"
.Range("C1") = "Prénom"
With .Range("A1:C1")
.Interior.Color = RGB(180, 180, 240)
.Borders.LineStyle = xlContinuous
End With
End With
End Sub
|
IV-B-3. Ajout
