Message box keep coming back?

[guelme]

Good afternoon gentleman's.

I have created the following script to run different messages based on the result of a formula on the worksheet. My problem is that I have implemented the vbYes/Vbno message box on one selection to change the value of a cell if the user clicks "Yes. This is performed but the message box keeps coming back. Any help on this matter will be greatly appreciated.


[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Private Sub Worksheet_Calculate()

Static OldVal As Integer
Dim Output As Integer


If OldVal = 0 Then
ElseIf Range("H1").Value = 1 Then
Sheets("Sum").Select
MsgBox "please read manual.", vbOKOnly + vbCritical, "Me"
Beep
ElseIf Range("H1").Value = 2 Then
Sheets("Sum").Select
MsgBox "Please read manual.", vbOKOnly + vbCritical, "Me"
Beep
ElseIf Range("H1").Value = 3 Then
Sheets("Sum").Select
Output = MsgBox("Please read the manual version 2. Would you like to overwrite the data?", vbYesNo + vbCritical, "Me")
If Output = vbYes Then
Range("c19").Formula = "=c4"
MsgBox "Formulas have been overwriting, no further alerts will be displayed on this sheet", vbInformation, "Me"
End If
Else
If Output = vbNo Then
MsgBox "Changes have been made"
End If
OldVal = Range("H1").Value
End If
End Sub
[/TD]
[/TR]
[TR]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AlphaFrog]

I'm not sure what you want to happen. This is a bit of a guess.

[COLOR=darkblue]Private[/COLOR] [COLOR=darkblue]Sub[/COLOR] Worksheet_Calculate()
    [B][COLOR=darkblue]Static[/COLOR] [COLOR=darkblue]Output[/COLOR] [COLOR=darkblue]As[/COLOR] [COLOR=darkblue]Integer[/COLOR]
    [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Output[/COLOR] = vbYes [COLOR=darkblue]Then[/COLOR] [COLOR=darkblue]Exit[/COLOR] [COLOR=darkblue]Sub[/COLOR][/B]
    [COLOR=darkblue]If[/COLOR] Range("H1").Value = 1 [COLOR=darkblue]Or[/COLOR] Range("H1").Value = 2 [COLOR=darkblue]Then[/COLOR]
        MsgBox "Please read manual.", vbOKOnly + vbCritical
        Beep
    [COLOR=darkblue]ElseIf[/COLOR] Range("H1").Value = 3 And [COLOR=darkblue]Output[/COLOR] <> vbYes [COLOR=darkblue]Then[/COLOR]
        [COLOR=darkblue]Output[/COLOR] = MsgBox("Please read the manual version 2. Would you like to overwrite the data?", vbYesNo + vbCritical)
        [COLOR=darkblue]If[/COLOR] [COLOR=darkblue]Output[/COLOR] = vbYes [COLOR=darkblue]Then[/COLOR]
            Sheets("SUM").Range("C19").Formula = "=C4"
            MsgBox "Formulas have been overwriting, no further alerts will be displayed on this sheet", vbInformation
        [COLOR=darkblue]Else[/COLOR]
            MsgBox "Changes have been made"
        [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
    [COLOR=darkblue]End[/COLOR] [COLOR=darkblue]If[/COLOR]
End [COLOR=darkblue]Sub[/COLOR]