Créer un formulaire avec des champs dynamiques et auto-générés sur Excel

Guide « 108 astuces pour grands débutants sur Excel ! » Offert
Booster votre productivité en téléchargeant gratuitement votre ebook PDF des meilleures astuces pour les débutants et les pros d'Excel
Excel formation - 00 Astuces - 100
 

Dans ce nouveau tutoriel, nous allons voir comment créer un formulaire pour saisir des informations dans une base de données à double entrée, dont les champs sont dynamiques. C’est-à-dire que dans ce formulaire, nous allons retrouver deux champs qui vont permettre d’identifier une cellule au sein d’une matrice en fonction des noms de lignes et de colonnes.

 

Pour suivre cet article dans les meilleures conditions, je vous invite à télécharger le fichier en suivant le lien disponible ci-dessous :

 

Téléchargement

 

Vous pouvez télécharger le fichier d'exemple de cet article en cliquant sur le lien suivant :

This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.
 

 

 

1. Présentation du cas

 

Pour illustrer ce tutoriel, nous disposons d’une base de données en deux dimensions qui reprend le montant des ventes réalisées par les commerciaux d’une société au cours de l’année 2018, réparties en fonction :

 

  • Chaque ligne reprend les ventes réalisées par chaque commercial,
  • Chaque colonne reprend les ventes réalisées au sein de chaque pays

Excel formation - Formulaire pour remplir une base - 01

 

Pour remplir et actualiser les données, nous pourrions effectuer des recherches manuelles (recherche de la ligne, puis de la colonne et saisie du montant des ventes), mais sur une base de données d’une certaine taille, cela peut vite devenir rébarbatif. Pour gagner en efficacité, nous allons préférer utiliser un formulaire, qui va se charger de lui-même de saisir l’information au bon endroit au sein de la base de données.

 

2. Création du formulaire

 

Le formulaire à utiliser pour remplir la base des données est le suivant :

Excel formation - Formulaire pour remplir une base - 02

Nous avons déjà vu dans un précédant article comment construire un formulaire de ce style (cliquez ici pour consulter l’article), les étapes sont également décrites dans la vidéo insérée ci-dessus.

Ce formulaire est composé de trois champs :

  • Le champ ComboBox_commercial est une liste dans laquelle nous allons venir sélectionner le nom du commercial concerné (nous allons voir juste après comment alimenter cette liste de manière automatique),
  • Le champ ComboBox_pays est une liste contenant cette fois-ci les différents pays dans lesquelles la société est amenée à avoir des affaires,
  • Et enfin le champs TextBox_ventes est une simple zone de texte qui va permettre à l’utilisateur de venir saisir le montant des ventes réalisées par un commercial donné, pour un pays donné également

Ensuite en bas de formulaire, nous retrouvons deux boutons permettant de valider la saisie du formulaire, et de quitter ce dernier.

 

3. Alimenter les listes déroulantes

 

Pour alimenter automatiquement les listes déroulantes, nous allons commencer créer deux zones de plages nommées pour les en-têtes de colonnes et de lignes de la base de données.

Pour cela, nous commençons par sélectionner l’ensemble des noms de la matrice :

Excel formation - Formulaire pour remplir une base - 03

Puis, nous venons saisir un nom à cette plage de cellule dans la zone de noms qui se trouve en haut à gauche de la barre des formules. Nous appelons cette plage « _baseNoms » :

Excel formation - Formulaire pour remplir une base - 04

(Attention de bien valider le nom en appuyant sur la touche [Entrée] du clavier).

Ensuite, nous faisons de même pour les cellules contenant le nom des pays :

Excel formation - Formulaire pour remplir une base - 05

Nous pouvons à présent lier ces noms de plages aux deux listes du formulaire.

Revenons dans notre projet, puis sélectionnons la liste ComboBox_commercial depuis l’outil de création du formulaire de VBA :

Excel formation - Formulaire pour remplir une base - 06

Ensuite dans la fenêtre des propriétés, nous allons rechercher la propriété qui porte le nom « RowSource » et simplement saisir le nom que nous avons attribué à la plage des cellules contenant les noms des commerciaux (« _baseNoms ») :

Excel formation - Formulaire pour remplir une base - 07

Malheureusement cette technique qui permet d’alimenter rapidement une liste dans formulaire ne fonctionne que lorsque la base d’origine est liste verticale. Les noms de pays étant saisis horizontalement, nous allons être dans l’obligation d’utiliser une manière un peu plus complexe. Mais pas de panique, cela reste assez simple à mettre en place.

Pour cela, nous allons entrer dans le code du formulaire (clic-droit sur le nom du formulaire depuis la fenêtre du projet > Code)

Excel formation - Formulaire pour remplir une base - 08

La feuille blanche qui s’affiche est la feuille de code, au sein de laquelle nous allons saisir toute la « mécanique » de fonctionnement du formulaire.

Dans le menu déroulant au-dessus de cette feuille, nous choisissons de saisir du code directement dans le code du formulaire (« UserForm ») :

Excel formation - Formulaire pour remplir une base - 09

Et dans le second menu, nous sélectionnons « Initialize » afin de définir ce qui doit se passer lorsque le formulaire est lancé :

Excel formation - Formulaire pour remplir une base - 10

De cette manière, VBA ajoute automatiquement une nouvelle instruction dans la feuille de code :

Private Sub UserForm_Initialize()
 
End Sub

Nous pouvons alors spécifier la plage des cellules à utiliser, en transformant celle-ci en une plage verticale, grâce à l’instruction Transpose :

Private Sub UserForm_Initialize()
    ' Chargement de la liste des pays
    ComboBox_pays.List = Application.WorksheetFunction.Transpose([_basePays])
End Sub 

À présent, nous pouvons tester le formulaire en appuyant sur le triangle vert, situé sur le menu en haut de la feuille de code. Si tout se passe comme nous venons de le faire, les deux listes devraient alors être connectées aux plages des cellules :

Excel formation - Formulaire pour remplir une base - 11

 

4. Identification de la cellule cible

 

Lorsque les deux listes ont été correctement sélectionnées, nous allons vouloir dans un premier temps récupérer la valeur de la cellule correspondante, dans le cas où celle-ci a déjà été remplie. Nous allons donc ajouter une fonction à notre feuille de code :

 

Function trouveCellule() As Range
    ' Enregistrement de la ligne et de la colonne
    Dim ligne As Integer, colonne As Integer
    Dim c As Range
    
    ' Balayage des noms de commerciaux
    For Each c In [_baseNoms]
        If c = ComboBox_commercial Then ligne = c.Row
    Next
    
    ' Balayage des noms de pays
    For Each c In [_basePays]
        If c = ComboBox_pays Then colonne = c.Column
    Next
    
    ' Si une ligne ET une colonne ont été trouvées, nous retournons les coordonnées de la cellule de jonction
    If ligne > 0 And colonne > 0 Then
        Set trouveCellule = Cells(ligne, colonne)
    ' Sinon, nous retournons une cellule d'erreur
    Else
        Set trouveCellule = Nothing
    End If
 
End Function

Pour résumer cette fonction :

  • Nous créons une fonction qui porte le nom de « trouveCellule » que nous typons en tant que plage de cellule (as Range),
  • Ensuite, nous créons deux variables de type Integer qui vont nous permettre de stocker le numéro de la ligne au sein de laquelle se trouve les données du commercial sélectionné, ainsi que le numéro de la colonne du pays sélectionné,
  • Pour retrouver ces numéros de ligne et de colonne, nous balayons successivement les deux plages de cellules (_baseNoms et _basePays) et lorsqu’une cellule a une valeur égale a été sélectionné dans les liste déroulante, alors nous pouvons considérer que la cellule a été identifié,
  • Enfin, nous regardons si nous avons bien trouvé un numéro de ligne ET un numéro de colonne. Si tel est le cas, alors nous retournons la cellule qui a pour coordonnée chacun de ces paramètres. Dans le cas contraire, nous retournons une valeur Nothing
  • La ligne End Function marque la fin de la fonction

 

À partir de maintenant, nous avons codé la partie la plus complexe de ce tutoriel.

Nous allons pouvoir récupérer simplement la valeur de la cellule lorsque les listes sont modifiées :

  • Dans le menu de sélection de l’élément, nous choisissons ComboBox_commercial :

Excel formation - Formulaire pour remplir une base - 12

  • Puis dans le second menu, nous choisissons l’action Change :

Excel formation - Formulaire pour remplir une base - 13

  • Ainsi, dans la procédure ComboBox_commercial_Change() nous pouvons saisir l’instruction suivante :
Private Sub ComboBox_commercial_Change()
    If Not trouveCellule Is Nothing Then
       TextBox_ventes = trouveCellule
    End If
End Sub

Lorsque la sélection de la liste ComboBox_commercial est modifiée, nous regardons s’il est possible d’identifier une cellule pour le commercial et le pays sélectionné (si les deux listes ont été renseignées), et dans ce cas nous récupérons la valeur de cette cellule dans la zone de texte (TextBox_ventes). Dans le cas contraire, nous ne faisons rien.

Nous réitérons l’opération pour la base des pays :

Private Sub ComboBox_commercial_Change()
    If Not trouveCellule Is Nothing Then
       TextBox_ventes = trouveCellule
    End If
End Sub

 

5. Enregistrer les modifications

 

À présent, nous allons coder l’enregistrement du montant des ventes, lorsque l’utilisateur clique sur le bouton Valider :

Excel formation - Formulaire pour remplir une base - 14

Private Sub CommandButton_valider_Click()
    If Not trouveCellule Is Nothing Then
       trouveCellule = CDbl(TextBox_ventes)
    End If
End Sub

Cette fois-ci, nous effectuons l’opération inverse : nous enregistrons le contenu de la zone de texte TextBox_ventes dans la cellule correspondante aux ventes du commercial sélectionné, pour le pays sélectionné également !

Nous convertissons au passage la valeur de TextBox_ventes en tant que variable de type Double pour être certain que la variable enregistrée est une valeur numérique.

Il ne nous reste plus qu’à implémenter le bouton Annuler, afin de quitter le formulaire :

Excel formation - Formulaire pour remplir une base - 15

Private Sub CommandButton_annuler_Click()
    Unload Me
End Sub

 

6. Lancer le formulaire

 

 

Pour finir, nous allons ajouter un bouton sur la feuille de calcul pour lancer le formulaire (Insertion > Zone de texte) :

 

Excel formation - Formulaire pour remplir une base - 16

Excel formation - Formulaire pour remplir une base - 17

Puis à l’aide du clic-droit, nous sélectionnons Affecter une macro… :

Excel formation - Formulaire pour remplir une base - 18

Dans la zone Nom de la macro, nous saisissons le nom de la macro à créer (« lancerFormulaire »), puis nous cliquons sur le bouton [Nouvelle] :

Excel formation - Formulaire pour remplir une base - 19

Il nous suffit alors de lancer le formulaire, grâce à l’instruction Show :

Sub lancerFormulaire()
    ufRemplirBase.Show
End Sub

Et voilà, notre macro est terminée, vous devriez pouvoir la tester en cliquant simplement sur le bouton que nous venons d’insérer :

Excel formation - Formulaire pour remplir une base - 20

 

 

 



Articles qui pourraient vous intéresser

Comment connecter Excel sur Internet, sans VBA ?
Comment traduire automatiquement des fonctions Excel dans leur version originale ?
Comment analyser les résultats d’un sondage ou questionnaire avec Excel ?
Comment utiliser la fonction SOMME.SI pour effectuer des recherches sur des textes sur Excel ?
Comment calculer et étudier des écarts budgétaires avec Excel ?
Comment verrouiller et protéger un objet (graphique, image, zone de texte…) sur Excel ?
Comment formater des dates correctement dans Excel ?
Comment protéger le formatage des cellules tout en autorisant la saisie de données dans Excel ?
Comment transformer une photo en tableau Excel ?
Comment créer un publipostage automatique avec Excel ? (sans Word !)
Comment créer un graphique Gaufre sur Excel ?
Comment créer un graphique en cascade dans Excel ?