Hi Alex,

My previous suggestion may not be the best. Here's how I handle this
scenario. I call a shutdown procedure that checks the user response to the
duplicate alert I display. The code handles suppressing the default alert
message properly, and allows the user to cancel normally as well. Try this:

Sub ShuttingDown()

Dim msg as String
Dim Ans as Variant

msg = "Do you want to save the changes to "
msg = msg & wb.Name & "?"

Ans = MsgBox(msg, vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
wb.Save 'suppresses default alert
wb.Close
Case vbNo
wb.Saved = True 'suppresses default alert
wb.Close
Case vbCancel
Cancel = True 'returns to Excel
End Select

End Sub

Regards,
GS
"GS" wrote:

Hi Alex,

Basically you are replacing the default "alert" message with your own
message. In this case, you can suppress the default message by wrapping your
code in these statements:

Application.DisplayAlerts = False
'your code here
Application.DisplayAlerts = True

Hope this helps!
GS


"Alex" wrote:

I have the following code in This Worksheet, which works fine. The problem
is that if someone makes a change to the file and clicks the "close" button,
they receive the normal, "do you want to save changes to the file . . " If
they click "yes", and the below test is true then the below msg, "You must
complete the highlighted fields" appears, which is also fine. But if a user
clicks, "OK" to that message, the close prompt, "do you want to save.. comes
up again, the user clicks Yes, then the message below comes up again, and
they both keep appearing.

How can I change or add to the code that will allow a user to click Yes when
asked if he or she wants to save changes and OK to the below message only
once?

Thanks for your help.




Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

With Worksheets("Drawing Notice (Main Sheet) 1")

If .Range("C16").Value = "0" Or .Range("C16").Value = "" _
Or .Range("E16").Value = "1" Or .Range("E16").Value = "" _
Or .Range("G16").Value = "1" Or .Range("G16").Value = "" _
Or .Range("I16").Value = "1" Or .Range("I16").Value = "" _
Then
MsgBox "You must complete the highlighted fields."
Cancel = True
End If
End With
End Sub