excel2000- modify msgbox

[lunker55]

I currently have the code:

Private Sub Worksheet_Calculate()
Dim cell As Range
Dim Rng As Range

Set Rng = Range("D31000")
For Each cell In Rng
If cell.Value >= 96 Then
MsgBox "You have exceeded the daily limit!"
End If
Next
End Sub


And want a message box where the person has the option to override the cell
value.
The following code was in the help section. Can I somehow add this into my
current macro?

Dim Msg, Style, Title, MyString
Msg = "Do you want to continue ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Exceeded daily limit"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
MyString = "Yes"
Else
MyString = "No"
End


Joe

 

[Frank Kabel]

Hi
not fully tested but try

Private Sub Worksheet_Calculate()
Dim cell As Range
Dim Rng As Range
Dim Msg, Style, Title, MyString

Set Rng = Range("D31000")
For Each cell In Rng
If cell.Value >= 96 Then
application.enablevents=false
Msg = "Do you want to continue ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Exceeded daily limit"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
'do nothing
Else
cell.value=95
End
End If
Next
application.enablevents=true
End Sub

 

[lunker55]

Frank, I get the error:
"Compile error: Next without For"

any ideas?

Joe

 

[Frank Kabel]

Hi
sorry, copy/paste error. Try

Private Sub Worksheet_Calculate()
Dim cell As Range
Dim Rng As Range
Dim Msg, Style, Title, MyString

Set Rng = Range("D31000")
For Each cell In Rng
If cell.Value >= 96 Then
application.enablevents=false
Msg = "Do you want to continue ?"
Style = vbYesNo + vbCritical + vbDefaultButton2
Title = "Exceeded daily limit"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then
'do nothing
Else
cell.value=95
End if 'the if was missing
End If
Next
application.enablevents=true
End Sub

 

[lunker55]

Frank, another error:
"Run-time error '438' object doesn't support this property or method"

When I debug, "Application.enablevents=True" is highlighted in yellow.

Joe

 

[Frank Kabel]

Hi
change this (due to a missing 'e')line to
Application.EnableEvents = True

and also change
application.enablevents=false
to
Application.EnableEvents = False

I should quit working now, sorry fot the typos

 

[lunker55]

Thank you Frank. Works great. If only I knew VBA better, I could have
figured it out myself.
Now I just have to decide what to put in the cell if they click "Yes".

Joe

 

[Frank Kabel]

Hi
good to hear it works for you