BeforeSave and Close help

Alex,

Sorry I forgot to mention, ..the previous code sample should be called from
the BeforeClose event for your workbook. Also, using the BeforeSave event is
pointless for this situation so don't use it.

Regards,
GS

Thanks for your help, but I'm somewhat of a novice and this doesn't really
make sense to me. I tried the code the code you suggested, which doesn't
work. You told me not to use the BeforeSave code I have so where do I check
to see if my cells have the correct value? Thanks

Private Sub Workbook_BeforeClose(Cancel As Boolean)


Dim msg As String
Dim Ans As Variant
Dim wb As Workbook

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


"GS" wrote:

> Alex,
>
> Sorry I forgot to mention, ..the previous code sample should be called from
> the BeforeClose event for your workbook. Also, using the BeforeSave event is
> pointless for this situation so don't use it.
>
> Regards,
> GS

Hi Alex,

Sorry for the confusion. This code is an example that you could modify to
work with your Workbook_BeforeClose event. Later, I realized you might need
help with that so I created a drop-in function for you that works exactly how
you want. Unfortunately, I put it in the wrong place in this list of replies.
My last two are mute as a result of the drop-in function.

My second repy in this list has the answer your looking for. The third reply
merely explains that it was put in the wrong place.

Once again, I apologize for any confusion caused. Yesterday was just
"one-of-those-days" where it rained "interuptions & distractions" all day.

Regards,
GS

To answer your question about where to put your code to check those cells:

In the Select Case block:
Case vbYes
'means the user wants to save the file.
'doing your check here before saving the file will cancel closing the file
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 = "" _
Or .Range("C17").Value = "" _
Or .Range("F17").Value = "" Then

'display your message
MsgBox "You must complete the highlighted fields."

'set the value being passed to Workbook_BeforeClose() and exit
bBeforeShutDown = True
Exit Function
End If
End With

'If all the cells pass the test then we save.
Wbk.Save
'Placing your test "as is" in the BeforeSave event will handle when the
user saves.
'If the conditions pass your test in either place, normal behavior occurs.
'If the conditions fail the test in either place, the user is returned to
Excel to resume.

As I said previously, I don't normally handle Workbook events from the
ThisWorkbook module. I did earlier on but I switched to using Auto_Open and
Auto_Close procedures and thus, revised all my code snippets accordingly.
That's why the earlier example didn't work for you "as is", ..it was intended
as a suggestion of how you could handle the task. After reading your response
to my reply, I realized you needed more explicit help with it. I hope that
has been accomplished!

Regards,
GS