Message box keep coming back?

guelme

New Member
Joined
Feb 26, 2015
Messages
5
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. :banghead::banghead:


[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]
 
I'm not sure what you want to happen. This is a bit of a guess.

Code:
[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]
 
Upvote 0

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top