XL 2019 problème déclenchement macro avec cell.value et msgbox

[rh.finances]

Bonjour à tous les colistiers de ce superbe site d'échange.

je suis en train de confectionner un tableur excel comprenant des macros et n'étant pas vraiment aguerri sur ce dernier point, je me heurte à un problème insoluble qu'un connaisseur saura sans doute gérer.
dans l'onglet "tableau de saisie" du fichier joint, j'ai appliqué un nom ("total_journalier_en_heures") sur la plage$O$2:$O$500. il s'agit d'une plage de totalisation et si les données remplies dans les colonnes I, J, L et M conduisent à un résultat supérieur à 10.5, je souhaite qu'un msgbox de type VbRetryCancel s'affiche.
si on clique sur "réessayer", la dernière saisie ayant entraîné le dépassement s'efface afin de recommencer
si on clique sur "annuler", la saisie que l'on a faite est maintenue et la macro s'arrête.

j'ai donc essayé de bidouiller un code en fouillant dans différents forums. celui-ci ne semble pas présenter d'erreurs (tout du moins, je n'ai pas eu de message en ce sens) mais celui-ci ne s'active pas pour autant, d'où le problème que je rencontre.

je l'ai rédigé comme suit :

Sub comparer()
Dim a As Variant
Dim tabl As Range
Dim answer As Integer
 
Set tabl = Range("total_journalier_en_heures")
a = 10.5
answer = MsgBox("le nombre d'heures journaliers est supérieur à 10h30. Voulez-vous continuer ?", vbRetryCancel + Apparence + TypeDeBox, "total journalier")

For Each cell In tabl
    If cell.Value > a Then answer
 
If answer = vbRetry Then Application.Undo
Else: Exit Sub

End If
Next cell
End Sub

en revanche et ça, je ne l'ai pas indiqué dans le code susvisé, je souhaiterais simplement que la msgbox ne s'affiche qu'une seule fois lors de la survenance de l’événement, puis cesse ensuite de fonctionner et ce même si la condition déclenchant la macro demeure remplie.

d'avance merci beaucoup pour votre précieuse collaboration.

Alex

 

[frangy]

Bonjour,

Voici une proposition qui utilise l’évènement Worksheet.hange.

Si les données remplies dans les colonnes I, J, L et M conduisent à un résultat supérieur à 10.5, une MsgBox de confirmation de saisie s'affiche,
Si on clique sur "NON", la dernière saisie ayant entraîné le dépassement s'efface afin de recommencer,
Si on clique sur "OUI", la saisie que l'on a faite est maintenue et la macro s'arrête.

Je n’ai pas répondu à la condition suivante :

La MsgBox ne s'affiche qu'une seule fois lors de la survenance de l’événement, puis cesse ensuite de fonctionner et ce même si la condition déclenchant la macro demeure remplie.

Cette dernière condition pose problème car cela signifie que la survenance de l’évènement est mémorisée, et cela même après être sorti de la procédure. Cette mémorisation doit inclure chaque élément déclencheur (I, J, L, M) mais ne concerne qu’une ligne.
Je n’ai pas compris l’intérêt de cette condition puisque la MsgBox permet de sortir de la procédure en conservant la valeur > 10.5.

Cordialement.

 

[Laurent78]

Bonjour,
ci-joint modif du fichier de frangy pour prendre en compte la mémorisation de l'alerte (utilisation d'une colonne ajoutée dans le tableau principal)
A+
Laurent

 

[rh.finances]

Bonjour Frangy et Laurent,

tout d'abord un grand merci à vous pour vos réponses qui répondent très bien à mon souhait.
Dans la mesure du possible et sachant que ce tableau est susceptible d'évoluer dans le temps avec l'ajout et la suppression de colonnes, j'aurais voulu "automatiser" la macro en attribuant des noms de cellules ou colonnes plutôt que des numéros.

ainsi, plutôt que de mettre "Target, Range("I2:J500,L2:M500")", j'ai réécrit le code Target, Range("periode_1,periode_2"), les noms "periode_1" et "periode_2" reprenant les plages de cellules correspondantes.
j'ai fait un test et cela semble fonctionner sans problème.

en revanche, pour la fonction "Cells(Target.Row, 15)", j'ai voulu remplacer le 15 par un nom défini appelé "colonne_total_journalier" correspondant à la formule "=colonne($O:$O)".
voici le code qui en découle... mais là, c'est le bug complet !!

Private Sub Worksheet_Change(ByVal Target As Range)
Dim Answer As Long
    If Target.Count > 1 Then Exit Sub
    If Not Application.Intersect(Target, Range("periode_1,periode_2")) Is Nothing Then
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        If Cells(Target.Row, "colonne_total_journalier") <> "" Then
            If Cells(Target.Row, "colonne_total_journalier") > 10.5 And Not Memo Then
                Answer = MsgBox("Le nombre d'heures journalier (" & Format(Cells(Target.Row, "colonne_total_journalier") / 24, "hh""h""mm") & ") est supérieur à 10h30." & _
                Chr(10) & "Confirmez-vous cette valeur ?", vbYesNo, "Total journalier")
                If Answer = vbNo Then
                    Application.Undo
                End If
            End If
        End If
        Application.EnableEvents = True
        Application.ScreenUpdating = True
    End If
End Sub

aussi, vous serait-il possible de me préciser s'il est possible d'arriver à mes fins d'une autre manière.

encore une fois, mille merci à vous !!

Alex

 

[Laurent78]

Cells() attent un Numero de ligne et un numéro de colonne,
"colonne_total_journalier" n'est pas un numéro de colonne, donc c'est normal que ça ne compile même pas.

il faut plutôt utiliser ce genre de chose :
Range("colonne_total_journalier").Column à la place du 15 initial et pas "colonne_total_journalier" tout seul

 

[rh.finances]

Merci pour ce retour Laurent,

j'ai utilisé ta proposition et j'ai supprimé la formule colonne qui était associée au nom "colonne_total_journalier".
résultat : une macro qui fonctionne exactement comme je le souhaite !!

encore une fois, mille merci à Frangy et toi pour votre aide précieuse et salutaire !!

Bonne journée

Alex