Exécution d'une macro après MAJ automatique d'un résultat de calcul

[Mister alex]

Bonjour à tous,

Je suis débutant en macro. J'ai mis en place un fichier permettant de gérer le stock de mon entreprise. Il est constitué de 3 feuilles, une feuille "reste en stock", une feuille "entrée en stock", et une feuille "sortie de stock". Le calcul du solde restant (compris dans la feuille "reste en stock"en colonne D) reprend les valeurs inscrites dans les feuilles "entrées" et "sorties de stock". Le calcul du solde est donc automatisé.
Toujours dans la feuille "reste en stock", on trouvera le point de commande, c'est à dire la quantité en stock où il faudra passer commande. Donc pour résumer, si le solde (colonne D) est inférieur ou égale au point de commande (colonne E) il faut passer commande

Je fais donc appel à vous car je cherche une macro qui m'alerterai avec une alerte msgbox que le point de commande a été atteint (la question serait: voulez vous commander: oui ou non). Par la suite un Clique sur oui déclencherait l'envoi d'un mail.

J'ai trouvé un code qui répondait à peu près à cette problématique sauf que la msgbox ne s'affiche seulement quand les valeurs comprises dans la colonne D étaient entrées manuellement. Je cherche donc une macro qui se lancerait dès que le résultat de la formule située en colonne D change et serait inférieure ou égale au point de commande en colonne E.

Je vous laisse le code (qui ne marche pas)

Code :
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Column <> 4 Then
GoTo endsub
End If
k = Target.Row
If Range("F" & k).Value <> "envoi mail" Then
GoTo endsub
End If
If Range("F" & k).Value = "envoi mail" Then
GoTo EnvoiMail
End If

EnvoiMail:
MsgBox "voulez-vous commander ?", vbYesNo, "Stock critique"
If MsgBoxResult = vbYes Then
P = ThisWorkbook.Path

ActiveWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=P & "\Commande.pdf" 'From:=1, To:=2

Dim oCDO

Set oCDO = CreateObject("CDO.Message")
With oCDO
With .Configuration.Fields ' Configuration du compte mail SMTP
.Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = "2"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "Adresse smtp du serveur"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = "numéro de port du serveur"

'----- Dans le cas ou le serveur de mail demande une authentification
.Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
.Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "adresse sur le serveur utilisée pour l'envoi"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "mot de passe pour cette adresse "
'----------------
.Update
End With
.From = "adresse sur le serveur"
.To = "misteralex@XLS.com"
.TextBody = "Texte du mail"
.AddAttachment P & "\Commande.pdf"
.Send
End With
Kill (P & "\Commande.pdf")
MsgBox "Une alerte mail a été envoyé au responsable des commandes"
GoTo endsub
End If
If MsgBoxResult = vbNo Then
GoTo endsub
End If
endsub:
End Sub

Voila, j'espère que mes explications sont compréhensibles...
Je vous laisse mon fichier en pièce jointe, ce sera peut etre plus clair?
Merci d'avance,
Alex

 

[Compte Supprimé 979]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

Salut Mister Alex,

J'ai ajouté une colonne dans ton stock et modifié le code et son emplacement
Le principe : lorsque tu saisis une valeur en entrée ou en sortie
Le code vérifie si tu as autant de ligne "Envoi mail" que de ligne FAIT = Oui

Si ce n'est pas le cas, le mail est envoyé et les lignes FAIT du stock existant sont mis à jour

Nota : l'export du fichier au format PDF ne fonctionne que sur Excel 2007 et ultérieur
c'est pourquoi je crée une copie de ta feuille en ".xls"

A toi de voir si cela te convient

A+

 

[Venitien]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

Salut mister alex et BrunoM45,

Sans parvenir à faire en sorte que la macro se lance au moment du recalcul comme le souhaitait mister alex, j'ai continué à plancher sur la macro et j'avais eu comme idée de placer

TextBody = Range("B" & k).Value

dans le message pour que la personne qui reçoit le message sache immédiatement de quel article il s'agit...

@BrunoM45: Il n'y à vraiment aucune méthode (même hors cadre du problème de mister alex) pour qu'une macro s'exécute automatiquement quand une cellule se met à jour automatiquement (quand elle contient le résultat d'une somme et qu'on modifie un des opérateurs par exemple?)

A+

 

[Mister alex]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

Salut BrunoM45,
Ton idée me convient parfaitement même si effectivement la macro ne se lance pas automatiquement après le recalcul de la colonne "reste en stock". Mais c'est une bonne alternative.
Par contre il y a un problème au niveau de la macro. La MsgBox s'affiche systématiquement lorsque je rentre n'importe quelle valeur dans la feuille "sortie", et je ne sais pas d'où cela peut venir.
Vénitien, ton idée me plait bien. Où dois-je mettre le code dans la macro?
Merci à vous deux pour votre aide,
A+,
Mister Alex

 

[Venitien]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

En fait c'est juste la partie text body du message, c'est sous l'adresse du destinataire, mais comme dans ma macro, k correspondait au numéro de la ligne qui changeait, cela ne marchera pas avec la macro de BrunoM45... et je ne vois pas comment l'adapter... mais lui saura sûrement.

désolé.

A+

 

[Compte Supprimé 979]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

Salut Miter alex, Venitien

Salut BrunoM45,
Ton idée me convient parfaitement même si effectivement la macro ne se lance pas automatiquement après le recalcul de la colonne "reste en stock". Mais c'est une bonne alternative.
Par contre il y a un problème au niveau de la macro. La MsgBox s'affiche systématiquement lorsque je rentre n'importe quelle valeur dans la feuille "sortie", et je ne sais pas d'où cela peut venir.

Le code que j'ai donné est dans l'évènement "Change" de ThisWorkbook

Donc à chaque modification d'une cellule de n'importe qu'elle feuille,

Si le nom de ligne dans la colonne F = "envoi mail" <> de la colonne G = "Oui"
Je demande si tu veux envoyer le mail, et ça jusqu'à ce que tu l'es fait ou qu'une ligne supplémentaire soit à "envoi mail"

J'espère être plus claire pour toi

A+

 

[Mister alex]

Re : Exécution d'une macro après MAJ automatique d'un résultat de calcul

Ok Bruno, c'est clair pour moi. Ca marche bien comme ça donc je vais garder ta méthode.
Et comme tu l'as dit dans tes commentaires (dans la macro), la méthode CDO ne fonctionne pas dans un réseau d'entreprise, donc jai opté pour un autre code et ça à l'air de fonctionner. Je le post si ca vous intéresse:

Sheets("Stock existant").Copy
With ActiveWorkbook
.ActiveSheet.Cells.Copy
.ActiveSheet.Cells.PasteSpecial Paste:=xlPasteValues
' .SaveAs sPath & "Commande.xls"
End With

Dim wbk As Workbook
Set wbk = ActiveWorkbook
ActiveWorkbook.SendMail "alexandre.martin@fr.issworld.com", "Objet du message"
ActiveWorkbook.Close savechanges:=False


Il y a peut être d'autres méthodes mais celle là à l'air de marcher.
Merci à vous deux, vous m'avez bien aidé.
Mister Alex,
A+