Code VBA qui prend en compte le résultat d'une formule

Bonjour tout le monde,

Je vais essayé d'être le plus clair possible car je découvre le VBA totalement (comme beaucoup d'autre qui pose des questions sur le forum). Comme vous pourrez le voir dans mon fichier

, quand j'ajoute quelque chose dans une colonne, la case correspondante dans la ligne 27 décompte avec la formule : =4-NBVAL(J10:J26) .

Avec un code VBA, j'ai cherché à afficher un message quand cette case sur la ligne 27 :

• Affiche 2 : "Attention il n'en reste que deux, ajouter un commentaire"
• Affiche 1 : "Attention il n'en reste que un, ajouter un commentaire"
• Affiche 0 : "Attention il n'en reste plus, ajouter un commentaire"

Et ces commentaires apparaissent dans leur ligne respective.

Voici le code :

Sub Worksheet_Change(ByVal Target As Range)
If Target.Row <> 27 Then Exit Sub
If Target.Value <= 2 And Target.Value >= 0 Then
If IsEmpty(Target.Offset(3 - Target.Value, 0)) Then
Target.Offset(3 - Target.Value, 0).Value = InputBox("attention : il n'en reste plus" & Choose(Target.Value + 1, "", " qu'1", " que 2") & " le " & Cells(7, Target.Column).Text & ", laissez un commentaire", "Attention", "Commentaire")
End If
End If
End Sub

Je ne l'ai pas inventé, une âme charitable me l'a fait. Mais ... ce code ne fonctionne pas dans mon cas. Pour avoir testé plusieurs trucs, il s'avère que ça fonctionne si on rentre directement un chiffre dans une case de la ligne 27 mais ça ne fonctionne plus si c'est une formule...

Je suis sûr que la solution doit être relativement simple mais j'avoue que je sèche.

Merci d'avance pour vos conseils et suggestions.

Bonjour,

Sauf erreur de ma part, le résultat de ta formule est impacté par une modification de la plage à laquelle elle fait référence. Hors, cette plage n'est pas la ligne 27 dans laquelle se trouve la formule (mais plutôt une modification de la ligne 10 à 26)...

Aussi, pourquoi ne pas faire directement le calcul sous VBA ?

=4-NBVAL(J10:J26)

Devient :

Résultat = 4 - Application.CountA(Range("J10:J26"))

Bonjour,

Code modifié :

Sub Worksheet_Change(ByVal Target As Range)
    Dim plage_congés As Range, ligne_reste As Range, reste_jours As Range

    Set plage_congés = Me.Range("H10:M24")
    Set ligne_reste = Me.Range("27:27")
    If Intersect(Target, plage_congés) Is Nothing Then Exit Sub

    Set reste_jours = ligne_reste.Columns(Target.Column)
    If reste_jours.Value <= 2 And reste_jours.Value >= 0 Then
        If IsEmpty(reste_jours.Offset(3 - reste_jours.Value)) Then
            reste_jours.Offset(3 - reste_jours.Value).Value = InputBox("attention : il n'en reste plus" & Choose(reste_jours + 1, "", " qu'1", " que 2") & " le " & Cells(7, reste_jours.Column).Text & ", laissez un commentaire", "Attention", "Commentaire")
        End If
    End If
End Sub

NB: ne pas oublier d'indenter le code (les If doivent être décalés et alignés avec les Else, End Sub correspondants) .

Merci Pedro et Thev.

Thev, j'ai repris ton code du coup, merci il fonctionne super bien. Juste avant de marqué le sujet comme résolu, j'ai une autre question mais qui fait peut être l'objet d'un autre sujet. En fait, je voulais que le commentaire se supprime automatiquement dans le cas où la valeur en 27 remonte, pour être plus clair :

• Si la valeur sur la ligne 27 passe de 0 à 1, le commentaire sur la ligne 30 se supprime automatiquement
• Si la valeur sur la ligne 27 passe de 1 à 2, le commentaire sur la ligne 29 se supprime automatiquement
• Si la valeur sur la ligne 27 passe de 2 à 3, le commentaire sur la ligne 28 se supprime automatiquement

Vous êtes plus habitués que moi, donc si vous pensez qu'il faut que je fasse un nouveau sujet pour cette question, dites le moi et je ferais le nécessaire.

Merci d'avance et encore merci pour vos réponses d'avant.

Bonjour,

Nouvelle version du code

Sub Worksheet_Change(ByVal Target As Range)
    Dim plage_congés As Range, ligne_reste As Range, reste_jours As Range

    Set plage_congés = Me.Range("H10:M24")
    Set ligne_reste = Me.Range("27:27")
    If Intersect(Target, plage_congés) Is Nothing Then Exit Sub

    Set reste_jours = ligne_reste.Columns(Target.Column)
    Select Case reste_jours.Value
        Case 0
            GoSub commentaire
        Case 1
            reste_jours.Offset(3).ClearContents
            GoSub commentaire
        Case 2
            reste_jours.Offset(2).Resize(2).ClearContents
            GoSub commentaire
        Case Else
            reste_jours.Offset(1).Resize(3).ClearContents
    End Select

    Exit Sub

commentaire:
    If IsEmpty(reste_jours.Offset(3 - reste_jours.Value)) Then
        reste_jours.Offset(3 - reste_jours.Value).Value = InputBox("attention : il n'en reste plus" & Choose(reste_jours + 1, "", " qu'1", " que 2") & " le " & Cells(7, reste_jours.Column).Text & ", laissez un commentaire", "Attention", "Commentaire")
    End If

    Return

End Sub

GENIAL ! Merci beaucoup thev, tu n'as pas idée à quel point ça m'est utile.