Créer un formulaire avec des champs dynamiques et auto-générés sur Excel
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 :
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
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 :
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 :
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 » :
(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 :
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 :
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 ») :
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)
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 ») :
Et dans le second menu, nous sélectionnons « Initialize » afin de définir ce qui doit se passer lorsque le formulaire est lancé :
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 :
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 :
- Puis dans le second menu, nous choisissons l’action Change :
- 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 :
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 :
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) :
Puis à l’aide du clic-droit, nous sélectionnons Affecter une macro… :
Dans la zone Nom de la macro, nous saisissons le nom de la macro à créer (« lancerFormulaire »), puis nous cliquons sur le bouton [Nouvelle] :
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 :