Suite aux nombreuses questions qui ont suivi après la publication du tutoriel Créer un formulaire personnalisé pour saisir des données sur Excel, j'ai décidé de réaliser une sorte de suite afin d'expliquer le code utilisé.
Je n'avais pas expliqué le code lors de ce tutoriel, car c'était ma première expérience du langage VBA et j'ai donc testé différentes choses jusqu'à arriver au résultat escompté, mais sans vraiment savoir pourquoi. J'ai commencé à l'apprendre grâce à l'élaboration de ce cours, puis des commentaires qui ont suivi. Désormais, la création de formulaire ou autre en langage VBA fait partie des prestations que je propose. Si vous n'avez aucune connaissance en VBA, je vous conseille fortement de commencer par un tutoriel tel que celui de la création d'un formulaire de saisie de coordonnées afin de vous mettre le pied à l'étrier :).
Je vous rappelle que le code VBA utilisé dans le tutoriel en question se trouve chez Excel-Plus puisqu'il s'agit d'un article invité. À la fin de ce dernier, vous pouvez copier-coller le code pour n'avoir aucune erreur.
Pour suivre ces explications, je vous invite à vous rendre dans la fenêtre Visual Basic dans l'onglet Développeur (si vous ne savez pas comment l'afficher, je vous invite à revoir le début du tutoriel de création) > Visual Basic
Dans la fenêtre de l' Explorateur de projet (si elle n'apparaît pas, cliquez sur Affichage > Explorateur de projets ou faites Ctrl + R), vous avez par défaut un dossier Microsoft Excel Objets qui contient tous vos onglets et ThisWorkbook. Si vous souhaitez créer une action sur double-clic d'une cellule dans un de vos onglets, vous écrirez le code dans l'onglet correspondant, si vous souhaitez pouvoir effectuer cette action dans n'importe quel onglet, vous l'écrirez dans ThisWorkbook qui peut être aussi utilisé pour faire une action à l'ouverture d'un fichier comme nous l'avons fait dans le tutoriel Créer un message d'alerte à l'ouverture d'Excel.
Dans notre exemple, nous avons ensuite ajouté un UserForm et un Module. Les premiers se trouvent toujours dans un dossier nommé Feuilles et les seconds dans un dossier nommé Modules.
Cliquez droit sur UserForm1 de l' Explorateur de projets > Code. J'avais placé un commentaire devant chaque évènement tel que l'initialisation du formulaire (son ouverture), le changement au niveau de la ComboBox et les clics sur les différents boutons. Ces commentaires sont en vert et commencent toujours par une apostrophe ('), c'est-à-dire que tout ce qui se trouve derrière une apostrophe ne sera jamais interprété dans votre code. Si vous voulez retirer une action temporairement, au lieu de l'effacer, vous pouvez tout simplement la mettre en commentaire.
Je n'avais pas modifié les noms des différents contrôles (ComboBox1, TextBox1...), mais si vous le faites n'oubliez pas de le modifier dans l'userform et dans le code.
Mon code commence par Option Explicit ce qui veut dire que je vais devoir déclarer toutes mes variables. De cette manière, si vous ne déclarez pas une variable, le mode débug que nous allons voir juste après, vous le signalera et vous évitera de découvrir des erreurs une fois que votre programme sera terminé et qu'il contiendra énormément de données. Si cette ligne vous gêne ou que vous l'oubliez régulièrement, vous pouvez vous rendre dans le menu Outils > Options et cocher la case Déclaration des variables obligatoire dans l'onglet Éditeur :
Cette case sera cochée par défaut pour tous vos nouveaux projets.
Il est ensuite suivi de qui est une déclaration de variable que j'aurais pu placer après Private Sub UserForm_Initialize(). Mais une déclaration de variable doit toujours se trouver avant l'utilisation de son nom. Cette déclaration va me servir à utiliser mon onglet Clients.
Private Sub UserForm_Initialize() :
À l'ouverture du formulaire, j'ai déclaré J et I comme étant des valeurs numériques. Je peux donner n'importe quel nom à une variable. Un nom, que ce soit de variable ou de macro, peut contenir des lettres minuscules ou majuscules et des chiffres, mais pas d'espaces, de points, de virgules, de traits d'union ou de slashs (évitez les accents) qui seraient mal interprétés par Excel. En clair, restez simple.
ComboBox2.ColumnCount = 1 indique qu'il n'y aura qu'une colonne dans la ComboBox2. Si j'avais souhaité avoir 2, 3, 4 colonnes, il aurait fallu écrire les items de la première colonne, puis la deuxième, etc. pour avoir une liste déroulante du même type que nous avons réalisé dans le tutoriel sur la facturation avec Access.
ComboBox2.List() = Array(" ", " M. ", " Mme ", " Mlle ") permet de spécifier les données à afficher dans la ComboBox2. Faites attention à la méthode utilisée pour vos formulaires : si les données n'ont pas besoin d'être modifiées et ne sont pas nombreuses, vous pouvez utiliser la méthode Array, mais dans le cas où les données sont amenées à changer souvent, il est préférable qu'elles soient accessibles à une personne novice en VBA et qui pourra les modifier dans son classeur Excel grâce à la méthode AddItem qui suit. Ici, nous avons, la possibilité de ne rien afficher ou de choisir M., Mme ou Mlle.
Set Ws = Sheets(" Clients ") me permet d'attribuer une valeur à la variable que j'ai déclarée plus haut, à savoir que Ws ne traitera que de l'onglet Clients (modifiez-le si vous renommez vos onglets).
Nous avons ensuite un bloc d'instruction qui contient une boucle qui indique que dans la ComboBox1, nous voulons récupérer à partir de la 2 e ligne (la première étant la ligne de titre) toutes les cellules se trouvant dans la colonne A de l'onglet Clients. Cette méthode est préférable à Range(" A65536″) que nous allons voir juste après, car ici nous allons partir de la dernière cellule de l'onglet qui était la 65 536 e sur les versions antérieures à 2007, mais a augmenté depuis, puis nous remontons jusqu'à la première cellule non vide. End(xlUp) est préférable, car si nous partions du haut et qu'une cellule était vide dans le tableau, le programme ne prendrait pas en compte les suivantes. Comme il s'agit d'une boucle, si vous lancez le mode débug, vous verrez que l'action se répète le nombre de fois nécessaire pour afficher tous les items (ici, 20 fois pour les 20 lignes du tableau).
En réalisant ce tutoriel, je me suis aperçue que certaines choses n'étaient pas nécessaires dans mon code comme :
Il s'agit d'une boucle pour les 7 TextBox (d'où l'intérêt de modifier ce chiffre si vous en ajoutez ou supprimez) qui va les rendre visibles... Or, elles sont, par défaut, visibles. Par la même occasion, la déclaration de I n'est plus indispensable.
Donc si ce code était à refaire, je supprimerais cette boucle ainsi que ComboBox2.ColumnCount = 1 puisqu'une ComboBox contient par défaut au moins une colonne et je supprimerais également le Me. dans With Me.ComboBox1 puisque la ComboBox1 est dans ce même formulaire.
Private Sub ComboBox1_Change() :Sur changement de la ComboBox1, les données vont se mettre à jour dans les autres contrôles du formulaire. Je déclare Ligne et I comme étant des valeurs numériques.
If Me.ComboBox1.ListIndex = -1 Then Exit Sub permet de sortir de la procédure dans le cas où vous ne sélectionnerez aucun numéro client.
Ligne est égale à la ligne de la ComboBox1 (si vous modifiez + 2 en + 1 ou + 3, vous verrez qu'il y a un décalage au niveau des données affichées).
ComboBox2 = Ws.Cells(Ligne, " B ") va ensuite récupérer les données de la ligne en cours à la colonne B (vous remarquerez que je n'ai pas eu besoin de déclarer à nouveau Ws, car il n'est pas dans un Private sub contrairement à I que j'ai dû déclarer de nouveau).
Je fais ensuite une boucle sur toutes les TextBox (7) pour récupérer toutes les données dans chaque contrôle. Chaque contrôle (du 1 er au 7 e) sera égal à sa ligne et sa colonne dans l'onglet. Je n'ai pas créé de boucle pour la ComboBox2 puisqu'il n'y avait qu'une ComboBox.
On peut faire en sorte que notre liste déroulante se mette à jour lorsque l'on ajoute un numéro client, mais comme l'action devra être utilisée après confirmation de l'ajout d'un client, on va l'ajouter dans l'évènement suivant.
Sur clic du bouton CommandButton1, je vais ajouter un contact. Pour cela, je déclare L comme étant une valeur numérique dont je donne la valeur juste après, à savoir que c'est la dernière cellule de la colonne A de l'onglet Clients + 1, c'est-à-dire que je me place après la dernière cellule non vide. Comme indiqué plus haut, je remplacerais " a65536″ par " A " & Rows.Count qui signifie exactement la même chose, mais qui pourra être transposable d'un ordinateur à un autre s'ils n'ont pas les mêmes versions.
J'ajoute ensuite une condition grâce à la fonction If qui est si je réponds Oui à mon MsgBox qui est une boîte de dialogue qui me demandera Confirmez-vous l'insertion de ce nouveau contact ?, qui contiendra les boutons Oui et Non et qui aura pour titre Demande de confirmation d'ajout, alors on va effectuer l'action qui se trouve, ici, avant End If.
Personnellement, je rajouterais :
ou (puisque de toute façon l'instruction est terminée après)
avant pour plus de clarté, mais ça fonctionne quand même sans.
Si je réponds Oui alors ma ComboBox1 va recopier sa valeur dans la colonne A à la dernière ligne non vide + 1, ComboBox2 va recopier dans B et ainsi de suite. Faites attention à l'ordre : c'est toujours la destination qui est égale au départ. L'ordre des propriétés n'a pas d'importance, mais les noms de TextBox et de colonnes oui.
Si vous souhaitez mettre à jour la liste déroulante après ajout, ajouter le code suivant :
Ce code correspond au même code que celui de la liste déroulante à l'ouverture avec ComboBox1.Clear. Ce dernier est important, car à la confirmation, la liste des codes client va être rechargée, or si elle n'est pas vidée ( Clear), à chaque nouvel ajout, vous aurez votre liste précédente + la nouvelle liste, donc plein de doublons.
Sur clic du bouton CommandButton2, je veux mettre à jour les données existantes. Pour cela, je déclare une nouvelle fois Ligne et I comme étant des valeurs numériques et je crée une boîte de dialogue Oui/ Non qui déclenchera l'action si je clique sur Oui. Le code est quasiment similaire à celui du changement de liste déroulante donc je le simplifie en retirant If Me.Controls(" TextBox " & I).Visible = True Then suivi de son End If puisque les TextBox sont toutes visibles.
Ici aussi, on peut ajouter :
Private Sub CommandButton3_Click() :
Sur clic du bouton CommandButton3, le formulaire en cours ( Me) va être déchargé, c'est-à-dire fermé, ce qui libère par la même occasion la mémoire du programme.
Retirer la saisie semi-automatique d'une ComboBox sur Excel :Par défaut, une ComboBox possède la saisie semi-automatique, il s'agit de la propriété MatchEntry. Cela est très pratique lorsque vous avez une liste de noms de clients par exemple pour les retrouver facilement, mais, dans ce tutoriel, la ComboBox1 servait à saisir un numéro client et, forcément, la saisie fait appel aux données déjà enregistrées si l'on commence par un chiffre déjà attribué.
Pour modifier cette propriété, cliquez sur la ComboBox1 en mode Afficher l'objet (clic droit sur l' UserForm1 dans l' Explorateur de projets) et, dans le panneau Propriétés, recherchez la ligne MatchEntry et sélectionnez l'option qui vous intéresse :
- 0 - fmMatchEntryFirstLetter : affiche le premier item de la liste commençant par la lettre saisie et si l'on appuie sur la lettre, sur le clavier, de manière répétée, les autres items de la liste commençant par cette lettre s'affichent successivement ;
- 1 - fmMatchEntryComplete : affiche une première suggestion qui peut être modifiée au fil de la saisie ;
- 2 - fmMatchEntryNone : pas de saisie semi-automatique.
Cette propriété peut être ajoutée via le code en ajoutant cette ligne dans l'initialisation du formulaire par exemple (le code remplace la propriété) :
ComboBox1.MatchEntry = fmMatchEntryNoneAttention : cette propriété ne fonctionne pas sur Mac et provoque une erreur.
Explication du code du Module1 :Ce module est une macro créée dans le but d'appeler le formulaire de saisie. Celle-ci débute par Sub et se termine par End Sub. Dans cet exemple, j'avais appelé cette macro Lancer_formulaire. C'est ce nom que vous retrouverez dans l'onglet Développeur > Macros. Il est également possible de créer un bouton sur votre fichier Excel pour lancer ce formulaire si vous ne souhaitez pas passer par un raccourci. Pour cela, créez une forme soit en passant par l'onglet Développeur > Insérer
UserForm1.Show affiche le formulaire UserForm1. Étant donné que je n'avais pas changé le nom du formulaire, c'est donc le nom de base, les formulaires suivants seraient UserForm2, UserForm3 ... sauf si vous les renommez.
vbModeless permet d'ouvrir le formulaire en non modal, c'est-à-dire que vous pourrez cliquer en dehors du formulaire, contrairement à vbModal où un clic en dehors du formulaire est impossible tant que celui-ci est ouvert. vbModal n'est pas utile à écrire puisque c'est le fonctionnement pas défaut.
Enfin, vous pouvez parfois avoir une erreur d'exécution sur la ligne affichant le formulaire dans le module. Si le nom de votre formulaire est bien saisi, le problème vient surement de l'initialisation du formulaire dans le code de l' Userform.
Utilité du mode débogage :
Le mode débogage permet de vérifier que votre code est bien écrit (absence de fautes de frappe, toutes les variables sont déclarées...). Vous pouvez directement passer par la mise en pratique en lançant votre formulaire et en le testant, mais si beaucoup de scénarios sont possibles, cela peut vous prendre du temps alors qu'il est préférable de ne le faire qu'à la dernière étape de la création de votre programme afin de vérifier que les données sont bien traitées de la manière que vous souhaitez.
Pour lancer ce mode, dans la fenêtre VBA, cliquez sur Débogage > Compiler suivi généralement de VBAProject qui est le nom donné par défaut à votre projet. Il peut être modifié en cliquant droit sur VBAProject (Le nom de votre fichier avec son extension) dans la fenêtre Projet > Propriétés de VBAProject.... Dans le champ Nom du projet, vous pouvez modifier le nom par défaut.
Une fois le mode lancé, s'il ne se passe rien, c'est que votre code est correct. Dans le cas contraire, une fenêtre s'ouvre avec l'erreur. Si par exemple, je supprime la ligne de déclaration de J dans l'initialisation du formulaire ( Dim J As Long) et que je lance le mode débug, j'ai bien l'erreur de variable non définie.
Pour terminer, n'hésitez pas à voir ou revoir le tutoriel Comment vider (effacer) un userform après validation sur Excel ? utilisant le même formulaire de saisie.
Téléchargez " Explication du code VBA utilisé dans le tutoriel Créer un formulaire personnalisé pour saisir des données sur Excel " au format PDFJe vous propose un fichier annexe dans l'Espace Membres contenant le code VBA rajouté.
Jeu de briques
Snake
Pacman
Bubble