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 pour saisir des données directement intégrées 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 :

 

 

1. Présentation du cas

 

Pour illustrer le cas d’étude, nous allons suivre le stock d’une petite boutique de matériel informatique spécialisée dans la vente d’ordinateurs portables :

 

Excel formation - Comment créer un formulaire de saisie intégré - 01

Le but de ce tutoriel va donc être d’insérer des colonnes, au début ou à la fin du tableau afin de pouvoir saisir les mouvements d’entrées et de sorties de matériels.

 

2. Création du tableau de données

 

 

Afin de faciliter la création du mini-formulaire, nous allons commencer par transformer le document en un tableau de données :

 

  • Sélectionner une cellule du tableau,
  • Menu Insertion du ruban > Tableau,

Excel formation - Comment créer un formulaire de saisie intégré - 02

  • Dans la fenêtre qui s’affiche, vérifier les coordonnées de la plage de cellules chargée par Excel, puis cocher la case « Mon tableau comporte des en-têtes » :

Excel formation - Comment créer un formulaire de saisie intégré - 03

  • Enfin valider avec le bouton [OK]

Excel formation - Comment créer un formulaire de saisie intégré - 04

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

Excel formation - Comment créer un formulaire de saisie intégré - 05

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 :

Excel formation - Comment créer un formulaire de saisie intégré - 06

 

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 ») :

Excel formation - Comment créer un formulaire de saisie intégré - 07

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

 

Excel formation - Comment créer un formulaire de saisie intégré - 08

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 :

Excel formation - Comment créer un formulaire de saisie intégré - 09

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

Excel formation - Comment créer un formulaire de saisie intégré - 10

 

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

Excel formation - Comment créer un formulaire de saisie intégré - 11

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 :

Excel formation - Comment créer un formulaire de saisie intégré - 12

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 :

Excel formation - Comment créer un formulaire de saisie intégré - 13

Excel formation - Comment créer un formulaire de saisie intégré - 14

 

 

 



Articles qui pourraient vous intéresser

Les 3 types de menus déroulants d’Excel

Comment regrouper des dates par périodes dans un tableau croisé dynamique sur Excel

Comment filtrer des données en VBA sur Excel

Comment afficher plusieurs onglets d'un même classeur grâce au multi-fenêtrage d'Excel

Commentaires

Il n'y a aucun commentaire...

Poster un commentaire

Pseudo
Commentaire

Contact - Plan du site - À propos - Contact

© Excel-Formation 2018 - Excel, simplement

Haut de la page

Excelformation.fr propose de nombreuses formations pratiques et gratuites sur Microsoft Excel : des cours pour apprendre à structurer et analyser les données, des tutos sur les fonctions avancées, les formules et les formules avancées d'Excel, les formules matricielles, les mise-en-formes conditionnelles, les graphiques Excel (xls, xlsx, xlsm,…). Des tutoriels pour apprendre à automatiser les tâches répétitives (via des macro-commandes en VBA) de vos classeurs Excel.