[VIDEO] Comment créer un formulaire pour remplir une base automatiquement ?
Aujourd'hui nous allons voir comment créer un formulaire de saisie afin d’alimenter simplement et surtout rapidement des données présentes dans un tableau déjà existant.
1. Présentation du cas d’étude
Pour illustrer ce tutorial, nous allons utiliser le cas concret d’une société de location saisonnière de VTT. La base de données utilisée est très sommaire, et est constituée de cinq colonnes :
- Colonne A : nous avons la date à laquelle le client souhaite réserver le vélo,
- Colonne B : le type de vélo choisi
- Colonne C : l'heure de départ, à laquelle le client vient récupérer le vélo,
- Colonne D : l'heure à laquelle il rend le vélo
- Colonne E : le nom et le prénom de la personne qui s'est chargée de réserver le vélo
Une table secondaire reprend l’ensemble des vélos disponibles à la location (nous y retrouvons des vélos enfants, des vélos adultes, deux tandems, et des rosalies de quatre et six places).
L’objectif ici est donc de mettre en place un formulaire de saisie qui permette à l’opérateur de pouvoir insérer rapidement une nouvelle entrée à la suite des données déjà existantes.
2. Conversion du fichier en *.xlsm
Par défaut, lorsque l’on créé un nouveau document sous Excel, celui-ci est enregistré sous le format *.xlsx, qui est le format de base d’Excel.
Pour enregistrer des informations des macro-commandes dans notre fichier (un formulaire est une macro-commande développée en VBA), nous allons avoir besoin de transformer notre fichier en *xlsm qui lui prend en charge l’enregistrement des macro-commandes.
Pour convertir notre fichier, nous allons cliquer sur :
- Fichier,
- Enregistrer sous,
- Parcourir,
- Dans la fenêtre qui s’affiche à l’écran, nous allons sélectionner à quel endroit nous souhaitons que notre fichier soit sauvegardé, et en bas de la fenêtre, dans le menu « Type », nous allons sélectionner « Classeur Excel (prenant en charge les macros) (*.xlsm) »
- Puis valider en cliquant sur le bouton « Enregistrer »
Dans la barre de titre (tout en haut de la fenêtre) nous pouvons maintenant constater que le nom de notre fichier a été modifiée, et porte désormais l’extension *.xlsm.
Nous pouvons maintenant intégrer des macros à notre fichier et ainsi ajouter le formulaire.
3. Création et personnalisation du formulaire
Pour créer une macro, nous devons nous rendre dans l'outil de développement et cliquant sur le bouton « Visual Basic » de l’onglet « Développeur ». Si cet onglet n’est pas disponible dans le menu Ruban, la démarche à suivre pour l’activer est la suivante :
- Fichiers,
- Options,
- Personnaliser le ruban,
- Dans la partie de droite, sélectionner « Développeur »
- Valider en cliquant sur le bouton « OK »
Après avoir cliqué sur le bouton « Visual basic » de ce nouvel onglet, nous nous retrouvons dans l'outil de développement d’Excel, c’est ici que nous allons pouvoir saisir nos macro-commandes, en utilisant le langage de programmation de Microsoft « VBA ».
Sur la partie gauche de la fenêtre, nous retrouvons nos deux feuilles de calcul (« Base location », et « Vélos »).
Ce qui va nous intéresser dans un premier temps, c'est de créer un formulaire. Pour cela :
- Effectuer un clic droit sur l’une des deux feuilles,
- Insertion,
- Userform
Le formulaire est ainsi généré.
Pour commencer, nous allons lui donner un nom, ce qui nous permettra d'identifier ce formulaire par la suite. Dans le champs « Name », saisissons par exemple « nouvelleLocation ».
Nous également modifier le « Caption » qui est le titre que nous retrouvons en haut de notre formulaire : « Enregistrer une nouvelle location ».
Les petits carrés qui se trouvent aux coins et au centre du formulaire permettent de modifier sa taille.
4. Ajouter les champs de saisie
Le formulaire est ainsi créé, nous allons pouvoir ajouter nos champs, qui vont permettre à l’opérateur d’entrer simplement des informations.
Nous allons donc en ajouter cinq qui correspondront aux cinq colonnes de notre base de données : pour la date, pour le vélo, pour l'heure d'arrivée, pour l'heure du départ et le dernier pour le nom du client.
Pour ajouter des champs de saisie de texte (qui permettra à l'utilisateur de venir saisir des informations), il suffit de les faire glisser directement depuis la barre d'outils. Puis les dimensionner à la taille voulue à l’aide des guides situés aux quatre coins de chaque TextBox, et au centre de chaque côté.
Pour appeler ces TextBox facilement, nous allons les renommer (propriété « Name » de la manièré suivante : TextBox_date, TextBox_velo, TextBox_depart, TextBox_retour, et TextBox_nom.
Pour l’instant, il ne s’agit que de simples champs de saisie, et rien ne renseigne l’utilisateur sur les informations qu’il doit saisir à l’intérieur. Nous allons devoir insérer des « champs d’étiquettes » face à nos zones de texte. Comme pour les TextBox, il suffit de glisser avec la souris l’élément à l’endroit désiré du formulaire.
Enfin, pour permettre de valider les informations saisies et fermer le formulaire, nous allons ajouter deux boutons en bas : « Valider » et « Annuler ».
Notre formulaire est maintenant designé, par contre il est strictement inutile car aucune action n’a été définie lorsque l’utilisateur clique sur les boutons.
5. Lancement du formulaire
La première chose à faire, avant même de mettre en place ces actions est de pouvoir lancer ce formulaire. Pour cela nous créer une macro qui va être exécutée lorsque nous allons cliquer sur un bouton situé sur la feuille de calcul.
Pour cela, nous allons créer un nouveau module (Insertion > Module) qui va nous permettre de saisir le code VBA de cette macro-commande :
Sub lancerFormulaire()
nouvelleLocation.Show
End Sub
- Sub lancerFormulaire : il s’agit du nom de notre macro
- nouvelleLocation.Show : « nouvelleLocation » correspond au nom que nous avions donné à notre formulaire lors de sa création. Nous demandons ici à excel de l’afficher grâce à linstruction .Show.
De retour dans notre feuille de calcul, il nous suffit d'ajouter un bouton (Onglet Développeur > Insérer > Contrôle de formulaire > Bouton), puis dans la fenêtre qui s’affiche de sélectionner la procédure que nous venons de créer : « lancerFormulaire ».
Il est possible de modifier le texte du bouton en effectuant un clic droit > Modifier le texte.
Nous pouvons déjà tester l’efficacité de ce bouton en cliquant dessus. Si tout s’est bien passé, le formulaire devrait se lancer, même s’il ne permet pas encore d’effectuer la moindre action.
6. Enregistrement des données
De retour dans Visual Basic, nous allons maintenant rendre nos boutons opérants, en commencant par le bouton « Annuler ». Pour ce faire, double cliquons sur celui-ci. Automatiquement Excel nous ouvre une feuille de code pour le formulaire et y insère l’évenement qui permet de capture le clique sur le bouton « Annuler ».
Il suffit d’insérer l’instruction « Unload me » à l’intérieur pour que le formulaire se masque automatiquement :
Pour ce qui concerne l’instruction du bouton « Valider », la procédure va être légèrement plus complexe, car c’est elle qui va se charger d’insérer l’enregistrement à la suite de notre base de données.
Pour l’instant, effectuons un simple copier-coller de ce qui suit (après avoir double cliqué sur le bouton « Valider » pour créer l’évenement), nous verrons ensuite le rôle de chacune des lignes de ce code :
Private Sub CommandButton1_Click()
' Saisie de la location dans la base de données
Dim ligne As Integer
ligne = Sheets("Base locations").[a5].End(xlDown).Row + 1
Sheets("Base locations").Range("a" & ligne) = CDate(TextBox_date)
Sheets("Base locations").Range("b" & ligne) = TextBox_velo
Sheets("Base locations").Range("c" & ligne) = CDate(TextBox_depart)
Sheets("Base locations").Range("d" & ligne) = CDate(TextBox_retour)
Sheets("Base locations").Range("e" & ligne) = TextBox_nom
Unload Me
End Sub
La première chose à effectuer pour ajouter un enregistrement à la suite de notre base de données, est de savoir sur quelle ligne nous allons devoir l’insérer.
Pour cela nous instancions une nouvelle variable, que nous appelons « ligne » et qui est un nombre entier (instanciée en tant qu’ « Integer »).
Pour récupérer le numéro de la ligne nous procédons de la manière suivante :
- Sheets("Base locations") : Nous nous plaçons au sein de la page « Base locations »
- Sheets("Base locations").[a5] : Puis nous nous placons sur la premier cellule de notre base de données (cellule ayant pour coordonnées « A5 »,
- Sheets("Base locations").[a5].End(xlDown) : Puis nous demandons à Excel de se déplacer sur la dernière cellule de la table qui n’est pas vide grace à l’instruction End() avec pour paramètre « xlDown » (vers le bas)
- Sheets("Base locations").[a5].End(xlDown).Row : Puis nous récupérons le numéro de cette ligne, grâce à l’instruction « Row »
- Sheets("Base locations").[a5].End(xlDown).Row + 1 : Et enfin, nous ajoutons « 1 » pour récupérer le numéro de la ligne juste en dessous
Ensuite, il ne nous reste plus qu’à insérer les textes saisis par l’utilisateur dans chacun des champs du formulaire dans la colonne correspondante.
Normalement, nous devrions préciser à chaque fois que nous modifions la valeur de la cellule pour lui affecter la valeur d’un champs, de la manière suivante :
Sheets("Base locations").Range("a" & ligne).Value = TextBox_date.Value
Mais pour gagner un peu de temps, Excel considère que la propriété par défaut est Value, et donc il n’est pas nécessaire de la préciser à chaque fois.
En d’autres termes « Range("a" & ligne).Value » et « Range("a" & ligne) » sont équivalents.
Une dernière petite remarque concernant ce que nous venons de saisir : pour convertir une chaine de caractères (que l’utilisateur a saisie dans le premier champs), nous utilisons l’instruction CDate(). Pour les heures nous procédons de la même manière, en effet, pour Excel une date et même une heure est un nombre (entier pour une date, décimal pour une heure) pour lequel une unité représente une journée. Ainsi, midi est exprimé par le nombre 0,50 (12/24 heures).
Nous pouvons tester le formulaire en cliquant sur le bouton que nous avons ajouté dans la partie précédente.
Il se peut que le formatage des cellules insérées soit différent de celui utilisé juste au-dessus, il suffit de le modifier en utilisant l'outil pinceaux.
Notre formulaire est maintenant fonctionnel, cela dit il n'est pas très pratique à utiliser (en raison de l’utilisation exclusive de champs de type Textbox), et nous allons voir dans la partie suivante comment faire pour l’améliorer.
7. Pour aller plus loin
7.1. Les listes déroulantes : ComboBox
En effet, l’inconvénient des Textbox est que la saisie est totalement manuelle, alors que nous pourrions facilement assister l’utilisateur dans sa saisie en lui proposant les données à saisir.
Par exemple, pour le champ « vélo », plutôt que de venir saisir manuellement la référence du vélo, nous allons créer une liste qui reprend l’ensemble des références.
Pour cela, nous commençons par supprimer la Textbox correspondant à la saisie du type de vélo (Textbox_velo), puis à sa place, nous allons insérer un champ de type Combobox, qui correspond à une liste de choix déroulante. Renommons ce champs « ComboBox_velo ».
La liste des références de vélos se trouve dans la feuille « Base Vélo ». Nous allons créer une plage nommée avec ces données, afin de pouvoir les utiliser simplement :
- Sélectionner la liste des vélos,
- En haut à gauche, dans la zone de noms, saisir le nom que nous souhaitons donner à notre liste : « listeVelos »
- Valider avec la touche Entrée du clavier
De retour dans Visual Basic, nous allons saisie le nom de cette nouvelle plage dans la propriété « RowSource » du Combobox_velo. Ainsi la source de données est connectée avec la liste déroulante.
Il ne nous reste plus qu’à modifier le comportement du bouton « Valider » pour récupérer non plus la valeur de la Textbox, mais celle de la Combobox :
Sheets("Base locations").Range("b" & ligne) = ComboBox_velo
7.2. Sélection de la date et de l’heure : DTPicker
De la même manière nous allons transformer les champs de saisie de la date et des horaires pour afficher un calendrier et des boutons de sélection des heures.
Pour cela, supprimons les Textbox correspondantes et remplaçons les par des champs de type DTPicker. Si l’icône DTPicker n’est disponibles dans la boîte à outils, il faut l’activer et effectuant une clic droit > Contrôles supplémentaires, et puis cocher la case qui se nomme « Microsoft Date and Time Picker Control 6.0 » et enfin valider avec le bouton Ok.
Par défaut, la propriété « Format » est paramètrée sur « 1 – dtpSortDate », ce qui permet d’afficher le calendrier de sélection de la date. Pour insérer un horaire, modifions cette propriété à « 2 – dtpTime ».
Il ne reste plus qu’à modifier le code de notre formulaire de la manière suivante :
Private Sub CommandButton1_Click()
' Saisie de la location dans la base de donnée
Dim ligne As Integer
ligne = Sheets("Base locations").[a5].End(xlDown).Row + 1
Sheets("Base locations").Range("a" & ligne) = CDate(DTPicker_date)
Sheets("Base locations").Range("b" & ligne) = ComboBox_velo
Sheets("Base locations").Range("c" & ligne) = CDate(DTPicker_depart)
Sheets("Base locations").Range("d" & ligne) = CDate(DTPicker_retour)
Sheets("Base locations").Range("e" & ligne) = TextBox_nom
Unload Me
End Sub
Nous pouvons maintenant tester et constater que le formulaire permet effectivement de nouveaux champs en bas de notre base.
8. Télécharger le fichier d'exemple
Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :