Approvisionner les stocks en VBA Excel

Pour partager cette vidéo sur les réseaux sociaux ou sur un site, voici son url :


Sujets que vous pourriez aussi aimer :


Approvisionnement des stocks

Etape par étape, nous construisons notre application VBA Excel de gestion et de facturation des clients. Nous avons déjà construit le système permettant de désigner le client à facturer et de spécifier les articles achetés. Nous avons de même conçu le mécanisme pour créer un nouveau client à la volée. Avant de penser à alimenter la commande, nous souhaitons aboutir la gestion des ressources. Ici, l'objectif consiste à approvisionner les stocks des articles issus de la base de données.

Source et problématique
Avant toute chose, nous devons réceptionner les travaux au dernier indice.

• Télécharger le classeur approvisionner-stocks.xlsm en cliquant sur ce lien,
• Double cliquer sur le fichier téléchargé pour l'ouvrir dans Excel,
• Cliquer sur le bouton Activer la modification du bandeau de sécurité,

Un message d'erreur apparaît. Cette sécurité se déclenche constatant qu'il s'agit d'une source externe. Elle neutralise le code VBA et génère une erreur sans impact.

• Cliquer sur le bouton Fin pour l'ignorer,
• Fermer Excel puis rouvrir le classeur,

Ce dernier est désormais identifié. Tous les codes déclenchés au chargement opèrent sans conflit.

• En cellule G5, cliquer sur la flèche de la liste déroulante,
• Choisir une référence parmi les propositions déroulées,

Instantanément, toutes les informations attachées à l'article sont importées dans les zones prévues à cet effet. Elles sont toutes issues de la base de données archivée dans la feuille Catalogue.



Un bouton a été ajouté par rapport aux développements précédents.

Après la saisie d'une quantité en cellule I7, un clic sur le bouton +Com. doit alimenter la commande du nouvel article acheté. Ce bouton existait déjà sous une autre désignation. Il concerne un développement futur.

Après la saisie d'une quantité en cellule I7, un clic sur le bouton +Stock doit indexer la quantité en stock de l'article désigné par la liste déroulante. Cette actualisation correspond à l'approvisionnement. Elle doit intervenir dans la feuille Catalogue de ce classeur. Et c'est la fonctionnalité que nous souhaitons développer dans cette formation.

Valider la saisie d'une quantité
Avant d'enclencher l'exécution d'un code VBA Excel, des contrôles de validité peuvent être réalisés en amont. En I7, la donnée attendue est forcément numérique et entière. Nous proposons de régler la cellule pour que toute saisie ne correspondant pas à ces contraintes, soit refusée. Dès lors, pour s'exécuter, le code VBA saura que si la cellule n'est pas vide, l'information est conforme.

• Cliquer sur la case de la quantité pour sélectionner la cellule I7,
• En haut de la fenêtre Excel, cliquer sur l'onglet Données pour activer son ruban,
• Dans la section Outils de données du ruban, cliquer sur le bouton Validation des données,
• Dans la boîte de dialogue qui apparaît, activer l'onglet Options,
• Dans la zone Autoriser, choisir Nombre entier,
• Dans la zone Données, remplacer l'opérateur Compris entre par l'opérateur Supérieur à,
• Dans la zone Minimum, taper le chiffre 0,

De cette manière, nous indiquons explicitement que seule la saisie des nombres entiers strictement positifs est autorisée.

• Activer l'onglet Alerte d'erreur de la boîte de dialogue,
• Conserver le style réglé sur l'option Stop,
• Dans la zone Titre, saisir le texte : Attention,
• Dans la zone Message d'erreur, taper l'information : Une quantité est forcément un nombre entier et positif,

Le style Stop est impératif. Si nous le changions, il serait possible de saisir des données ne respectant les conditions imposées. Avec cette alerte personnalisée, nous guidons l'utilisateur en cas d'inscription non conforme.

• Valider ces réglages en cliquant sur le bouton Ok de la boîte de dialogue,
• En cellule I7, saisir une donnée interdite comme : 7,5 et valider par la touche Entrée,

Instantanément, l'alerte surgit. La route est barrée. Même en insistant, tant que l'information n'est pas corrigée pour être en conformité avec la règle, la saisie est refusée. Bref, nous verrouillons et sécurisons l'application.

Actualiser les quantités en stock
Désormais, au clic sur le bouton +Stock, la quantité de l'article spécifié par la liste déroulante, doit être indexée du nombre entier indiqué. Bien entendu, le code VBA a pour mission d'isoler le produit reconnu par sa référence, dans la base de données.

• En bas de la fenêtre Excel, cliquer sur l'onglet Catalogue pour activer sa feuille,

Comme vous pouvez le voir, la recherche de la référence doit être réalisée dans la colonne B, soit à l'indice 2. L'actualisation du stock correspondant doit être opérée en colonne G, soit à l'indice 7. Le code VBA doit débuter le traitement de sa recherche à partir de la ligne 3. Forts de ces acquis, nous sommes désormais en mesure d'amorcer le programme d'approvisionnement des stocks.

• Revenir sur la feuille Facture,
• En haut de la fenêtre Excel, cliquer sur l'onglet Développeur pour activer son ruban,
• Dans la section Contrôles du ruban, cliquer sur le bouton Mode création,

Nous basculons ainsi en mode conception des contrôles ActiveX placés sur la feuille. En d'autres termes, nous pouvons les manipuler.

• Cliquer sur le bouton +Stock pour le sélectionner,
• Dans la section Contrôles du ruban Développeur, cliquer sur le bouton Propriétés,

Cette action a pour effet d'afficher la feuille de propriétés du contrôle sélectionné, soit du bouton.

• Dans la propriété (Name), remplacer l'intitulé CommandButton1 par : Stock,

Nous attribuons ainsi un nom d'objet explicite au bouton à manipuler.

• Désormais, double cliquer sur le bouton +Stock,

Instantanément, nous basculons entre les bornes de la procédure évènementielle Stock_Click. Son code sera exécuté au clic sur le bouton. Et il consiste à actualiser la quantité en stock de la référence mentionnée.

• Entre les bornes de la procédure, ajouter les déclarations et affectations suivantes :

...
Dim continuer As Boolean: Dim la_qte As Integer
Dim la_ligne As Integer: Dim la_ref As String
Dim avt_stock As Integer: Dim aps_stock As Integer

continuer = True: la_qte = Range('I7').Value
la_ligne = 3: la_ref = Ref.Value

If (Range('I7').Value <> '') Then
While continuer = True

Wend
End If
...

La variable continuer doit servir d'indicateur pour poursuivre le traitement. Par défaut, nous l'initialisons à True. Cette valeur indique que le traitement peut débuter ou se poursuivre. Nous déclarons les variables la_qte et la_ref pour stocker les informations de la feuille Facture. C'est pourquoi nous les affectons avec les valeurs des cellules correspondantes de la quantité et de la référence. La variable la_ligne doit servir à parcourir les références de la base de données. Nous l'initialisons donc à l'indice 3, soit le point de départ des références dans la feuille Catalogue.

Ensuite, nous enclenchons une boucle While qui doit se poursuivre tant que l'indicateur vaut True. Mais comme vous le remarquez, ce traitement récursif est conditionné. La valeur de la quantité doit être mentionnée. Grâce à la règle de validité, à partir du moment où elle n'est pas vide, nous savons qu'elle est conforme. Les deux variables avt_stock et aps_stock doivent rendre compte de l'actualisation.

A l'intérieur de cette boucle, nous devons chercher la référence désignée. Si elle est trouvée, son stock doit être mis à jour.

• Entre les bornes de la boucle While, ajouter les instructions VBA suivantes :

...
If (Sheets('Catalogue').Cells(la_ligne, 2).Value = Ref.Value) Then
avt_stock = Sheets('Catalogue').Cells(la_ligne, 7).Value
Sheets('Catalogue').Cells(la_ligne, 7).Value = Sheets('Catalogue').Cells(la_ligne, 7).Value + la_qte
aps_stock = Sheets('Catalogue').Cells(la_ligne, 7).Value
continuer = False
chercher Ref.Value, 'Catalogue'
MsgBox 'Le stock de la référence ' & Ref.Value & ' est passé de ' & avt_stock & ' à ' & aps_stock
End If

la_ligne = la_ligne + 1
If (la_ligne > 1000) Then continuer = False
...

Si la référence en cours d'analyse correspond, nous commençons par mémoriser le stock dans la variable avt_stock. Ensuite, en colonne G, soit à l'indice 7, nous effectuons la mise à jour selon la quantité saisie à ajouter. Puis, nous mémorisons le stock après actualisation dans la variable aps_stock. De fait et dans la foulée, nous basculons l'état de l'indicateur à False. Grâce à lui, le traitement récursif sera interrompu. Avec la fonction MsgBox, nous informons l'utilisateur du bon déroulement de l'opération.

Après l'instruction conditionnelle, nous n'oublions pas d'incrémenter la variable la_ligne. En effet, tant que la référence n'a pas été trouvée, l'analyse doit se poursuivre sur la ligne du dessous. Nous ajoutons néanmoins une sécurité pour limiter le traitement à 1000 passages.

• Enregistrer les modifications et basculer sur la feuille Facture,
• Dans le ruban Développeur, cliquer sur le bouton Mode création pour le désactiver,
• Avec la liste déroulante en G5, choisir une référence,
• Puis, saisir une quantité à ajouter en cellule I7,
• Dès lors, cliquer sur le bouton +Stock,

Aussitôt le message de succès apparaît. Il confirme l'actualisation du stock pour la référence désignée. Le code complet de la procédure évènementielle de mise à jour est le suivant :

Private Sub Stock_Click()
Dim continuer As Boolean: Dim la_qte As Integer
Dim la_ligne As Integer: Dim la_ref As String
Dim avt_stock As Integer: Dim aps_stock As Integer

continuer = True: la_qte = Range('I7').Value
la_ligne = 3: la_ref = Ref.Value

If (Range('I7').Value <> '') Then
While continuer = True
If (Sheets('Catalogue').Cells(la_ligne, 2).Value = Ref.Value) Then
avt_stock = Sheets('Catalogue').Cells(la_ligne, 7).Value
Sheets('Catalogue').Cells(la_ligne, 7).Value = Sheets('Catalogue').Cells(la_ligne, 7).Value + la_qte
aps_stock = Sheets('Catalogue').Cells(la_ligne, 7).Value
continuer = False
chercher Ref.Value, 'Catalogue'
MsgBox 'Le stock de la référence ' & Ref.Value & ' est passé de ' & avt_stock & ' à ' & aps_stock &' unités'
End If

la_ligne = la_ligne + 1
If (la_ligne > 1000) Then continuer = False
Wend
End If

End Sub

Dans une prochaine étape, nous bâtirons le mécanisme de construction de la facture.