Comment créer un formulaire de saisie intégré dans une feuille de calcul sur Excel

Dans ce tutoriel, nous allons voir comment créer un formulaire de saisie de données, directement intégré dans une feuille de calcul. 

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 :

Tutoriel Vidéo

Vous trouverez très régulièrement de nouvelles vidéos sur la chaîne, alors pensez à vous abonner pour ne manquer aucune astuce et devenir rapidement un pro d’Excel (cliquez-ici) !

1. Présentation du cas

Comme nous venons de le voir dans l'introduction de ce tutoriel, nous allons voir comment utiliser directement les cellules d'une feuille de calcul en tant que champs de formulaire.

Pour illustrer ce tutoriel, nous allons suivre le stock d’une petite boutique de matériel informatique spécialisée dans la vente d’ordinateurs portables, et dont voici le listing de ses références de matériels :

Nous retrouvons ici un état des stocks très basic, avec pour chacune des référence une cellule contenant le stock de départ en début d'année, puis une cellules contenant le solde des mouvements réalisés (en positif pour les entrées, et en négétif pour les sorties). La somme de ces deux cellules permet d'obtenir le niveau des stocks actuels.

Le but de ce tutoriel va donc être d’insérer de nouvelles cellules, à la suite de ce tableau dans lesquelles nous allons pouvoir saisir les mouvements d’entrées et de sorties de matériels. Les valeurs saisies dans ces cellules permettra alors de modifier la valeur contenue dans la cellule « Mouvements ».

2. Création du tableau de données

Afin de faciliter la création du mini-formulaire, nous allons commencer par transformer cette base de données en un tableau de données. Ce tableau de données nous facilitéra grandement le travail par la suite lorsque nous attaquerons la partie développement VBA, en affectant directement un nom à la plage dynamique de la base de données. Pour tout savoir sur le tableau de données, vous pouvez consulter ce tutoriel.

Pour effectuer cette transformation, nous procédons de la manière suivante :

• Tout d'abord, nous sélectionnons une cellule du tableau,
• Puis nous nous rendons dans le menu Insertion du ruban > Tableau,

• Dans la fenêtre qui s’affiche, nous vérifions les coordonnées de la plage de cellules chargée par Excel (et effectivement notre tableau commence avec la cellule A5, puis se termine par la cellule E11), comme le montre l'encadrement en pointillés).
• Ensuite, le tableau ayant des titres au colonnes, nous laissons coché la case « Mon tableau comporte des en-têtes » :

• Et enfin nous validons en appuyant sur le bouton [OK]

De cette manière, nous allons pouvoir très simplement créer de nouvelles colonnes, que nous pourrons ensuite traiter en VBA.

3. Insertions des colonnes du formulaire

Maintenant, nous allons insérer directement à la suite du tableau deux nouvelles colonnes afin de pouvoir saisir les entrées et les sorties de stock.

Pour cela, il suffit de se placer dans la cellule située juste à droite du dernier titre de colonne (après « Stock fin », c’est-à-dire en cellule F5, puis G5), puis de saisir les titres de deux nouvelles colonnes : « Entrées » et « Sorties » :

Nous allons alors constater qu’Excel va automatiquement étendre la plage des cellules source du tableaux (jusqu’en colonne G).

Pour différencier ces colonnes, nous allons modifier la couleur du fond :

4. Un peu de VBA !

Avant de passer dans le vif du sujet, c’est-à-dire au développement du code VBA, nous allons donner un nom simple au tableau ce qui nous permettra par la suite de nous y retrouver facilement :

• Sélectionner l’une des cellules du tableau,
• Puis dans le menu Création du ruban (qui n’apparaît que lorsque le tableau est sélectionné), nous pouvons personnaliser le nom de ce dernier (ici : « _stocks ») :

• Puis nous validons la modification du nom en appuyant sur la touche [Entrée] du clavier

À présent, rendons-nous dans l’outil de développement d’Excel en effectuant un clic-droit sur l’onglet de la feuille de calcul > Visualiser le code :

Cela a pour effet de lancer Visual Basic pour Application (VBA), directement dans la feuille de code de la feuille de calcul active.

La première chose à faire est d’initialiser l’évènement de modification de cellule :

• Dans le menu déroulant situé en haut de la feuille (dont la valeur est par défaut à « (Général) »), nous choisissons Worksheet :

• Puis dans le menu situé juste à droite, nous choisissons l’évènement Change :

Cela a pour effet d’insérer l’évènement Worksheet_Change() qui va être instancié à chaque fois qu’une cellule de la feuille de calcul va voir sa valeur modifiée (la ou les cellules concernée vont alors être retournée au sein de la variable Target) :

Private Sub Worksheet_Change(ByVal Target As Range)
End Sub

Pour le bon fonctionnement du formulaire, nous allons contrôler que la cellule modifiée appartient bien aux colonnes nouvellement insérées (les colonnes Entrées et Sorties), pour cela nous utilisons l’instruction Intersect(). Cette dernière permet de récupérer la ou les cellules qui se trouve à l’intersection de deux ou plusieurs cellules données en paramètre. Lorsqu’aucune cellule n’est détection à la jonction des plages, l’instruction Intersect() retourne la valeur nothing :

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [_stocks[Entrées]]) Is Nothing And Intersect(Target, [_stocks[Sorties]]) Is Nothing Then
        Exit Sub
    End If
    
    MsgBox "Cellule du formulaire modifiée"
End Sub

Dans cette partie de code, si Target se trouve à la fois en dehors de la colonne Entrées et de la colonne Sorties du tableau, afin de quitter l’instruction lorsque tel est le cas.

Si par contre Target se trouve dans l’une des deux colonnes, alors nous pouvons afficher le message « Cellule du formulaire modifiée ». Nous pouvons tester l’instruction en modifiant une cellule de l’une de ces colonnes :

Normalement, le message devrait s’afficher.

Ensuite, il ne nous reste plus qu’à additionner la valeur de Target à la valeur du mouvement :

    …
    Dim mouvement As Range ' [1]
    Set mouvement = Intersect(Target.EntireRow, [_stocks[Mouvements]]) ' [2]
    
    mouvement = mouvement + Target ' [3]
    Target.ClearContents ' [4]

Pour cela :

• [1] : nous instancions une nouvelle variable de type Range (c’est-à-dire qui va représenter une ou plusieurs cellules) à laquelle nous donnons le noms de « mouvement »,
• [2] : nous affectons la cellule qui se trouve à l’intersection de la ligne modifiée (EntireRow permet d’utiliser toute la ligne de la cellule Target) et de la colonne « Mouvements » du tableau à cette cellule,

• [3] : nous modifions la valeur de la cellule « mouvement » pour y ajouter la valeur de la cellule Target,
• [4] : en enfin nous supprimons la valeur saisie dans le formulaire grâce à l’instruction ClearContents

Attention, si nous testons le code à ce moment-ci de son développement, nous allons nous retrouver dans une boucle sans fin, car lorsque nous supprimons le contenu de Target, cela a pour effet de modifier sa valeur, et donc de relancer l’évènement ! Pas de panique, il suffit alors de modifier la première condition de notre code afin de quitter la procédure évènementielle lorsque la cellule Target ne contient aucune valeur (« … Or Target = "" ») :

    …
    If Intersect(Target, [_stocks[Entrées]]) Is Nothing And Intersect(Target, [_stocks[Sorties]]) Is Nothing Or Target = "" Then
        Exit Sub
    End If 
    …

Et voilà, notre macro est maintenant terminée, nous pouvons alors tester de saisir des valeurs positives dans la colonne Entrées et des valeurs Négatives dans la colonne Sorties :

Article publié le 12 février 2019, puis modifié le 7 août 2019.