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

Excel formation - video comment crer un moteur de recherche sur excel

  • 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).

Excel formation - video comment crer un moteur de recherche sur excel

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 »

Excel formation - video comment crer un moteur de recherche sur excel

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 »

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

Excel formation - video comment crer un moteur de recherche sur excel

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.

Excel formation - video comment crer un moteur de recherche sur excel

 

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 :

Excel formation - video comment crer un moteur de recherche sur excel

 

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.

Excel formation - video comment crer un moteur de recherche sur excel

 

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

Excel formation - video comment crer un moteur de recherche sur excel

Excel formation - video comment crer un moteur de recherche sur excel

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 :

Excel formation - video comment crer un moteur de recherche sur excel

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

 

Excel formation - video comment crer un moteur de recherche sur excel

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.

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

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.

Excel formation - video comment crer un moteur de recherche sur excel

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

 

Excel formation - video comment crer un moteur de recherche sur excel

 

   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.

Excel formation - video comment crer un moteur de recherche sur excel

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.

Excel formation - video comment crer un moteur de recherche sur excel

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

Excel formation - video comment crer un moteur de recherche sur excel

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 :

 



Articles qui pourraient vous intéresser

[VIDEO]Comment ajuster automatique la taille cellule en fonction de son contenu

Comment récupérer le nom de la feuille dans une cellule avec et sans macro VBA

[VIDEO]Comment surligner la ligne de la cellule sélectionnée

[VIDEO]Comment créer des logins et des mots de passe sur Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire