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]

 

[Roderick_E]

without looking closely, perhaps use EXIT SUB after each msgbox?

 

[guelme]

Thanks Roderick, but unfortunately that did not work. When the user selects "No" on the msg box will not come back, however the "yes" option it does the command but the nsg box re-appears.... Any other idea?

 

[guelme]

Any help guys? please..

 

[Roderick_E]

Looking...but this is one reason why I avoid ELSEIF...easier to sort out if---endif


try this one....

Private Sub Worksheet_Calculate()
Static OldVal As Integer
Dim Output As Integer
If OldVal > 0 Then
If Range("H1").Value = 1 or Range("H1").Value = 2 Then
Sheets("Sum").Select
MsgBox "please read manual.", vbOKOnly + vbCritical, "Me"
Beep
endifIf 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
If Output = vbNo Then
MsgBox "Changes have been made"
End If
OldVal = Range("H1").Value
End If

 

[guelme]

Roderick, thanks again for your input. A little more detail, the formula (H1) cell will be almost all the time 0 unless the user make a selection that fits in one of these 3 scenarios. This is the reason that I implemented the OldVal as they do mathematical operation or constantly enter cell data and the message will re-appear.
Now, I used your approach for option "3" and if the user hits "YES" the message box will re-appear over and over again. Not sure if this is caused by the calculate option. Any more amazing ideas?

 

[Roderick_E]

I would use something other than Worksheet_Calculate, Is there some cell the user is going to type in that might trigger this? Look at Worksheet_SelectionChange We need something else to trigger and NOT trigger, otherwise yes, the Worksheet_Calculate will contantly execute this code

 

[Michael M]

I don'see any reason the keep selecting the sum sheet either.

Private Sub Worksheet_Calculate()
Static OldVal As Integer
Dim Output As Integer
If Range("H1").Value = 1 Then
MsgBox "please read manual.", vbOKOnly + vbCritical
Beep
ElseIf Range("H1").Value = 2 Then
MsgBox "Please read manual.", vbOKOnly + vbCritical
Beep
ElseIf Range("H1").Value = 3 Then
Output = MsgBox("Please read the manual version 2. Would you like to overwrite the data?", vbYesNo + vbCritical)
If Output = vbYes Then
Sheets("SUM").Range("c19").Formula = "=c4"
MsgBox "Formulas have been overwriting, no further alerts will be displayed on this sheet", vbInformation
End If
Else
If Output = vbNo Then
MsgBox "Changes have been made"
End If
End If
OldVal = Range("H1").Value
End Sub

 

[guelme]

Michael, thanks for the information, however, as the problem seems to be the "Worksheet_Calculate" that keeps bringing the msgbox over and over, I was thinking if the user selects "Yes" to overwrite also the "H1" cell were the calculation is processed. Will this will help???. My main problem is that the user will select "yes" and the message box will not go away, and unfortunately this is made based on calculations on multiple sheets, not possible to use the Worksheet_Change

 

[Michael M]

OK, as Roderick mentioned you either need to use something other than Worksheet_calculate
OR you need to use a counter in the worksheet to eliminate the need for the messages !!