BeforeSave and Close help

[Alex]

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

[GS]

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
>

[Alex]

Thanks, but it didn't help. The exact same thing happens with the following
code:

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

Application.DisplayAlerts = False
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
MsgBox "You must complete the highlighted fields."
Cancel = True
End If
End With
Application.DisplayAlerts = True
End Sub

"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
> >

[GS]

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
> >

[GS]

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

[GS]

Hello again,

You got me going today because I usually do this differently, and the code I
provided doesn't exactly suit your scenario.
(All my projects are add-ins so I rarely use ThisWorkbook events because I
find it more suitable to use a class module for handling events.)

I reworked this a bit to use as a drop-in function, including all the
necessary declarations and assignments I omitted previously. <in the case of
"Option Explicit" being used (highly recommended)>
Using it requires the following line in the Workbook_BeforeClose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Not Me.Saved Then Cancel = bBeforeShutDown
End Sub

Normally, the event procedure would require the statement "Cancel = True" to
suppress the alert if the user cancels our replacement message.
This combination just gets it all done neatly because the cancel value
passes back to the Workbook_BeforeClose event.
If the workbook has been saved, then everything behaves normally, so there's
no need to intervene.
Although, you could to do something similar in the Workbook_BeforeSave
event if you want to intervene there. Obviously, the "If" statement wouldn't
be necessary.


The revised code: (Place in standard module)

Function bBeforeShutDown() As Boolean
' Replaces/suppresses the default close alert message
' Allows you to do anything you want by just adding more code in the Case
blocks.
' Called by Workbook_BeforeClose()
'
' Returns: TRUE if the user cancels

Dim wbk As Workbook
Dim msg As String
Dim Ans As Variant
Dim Cancel As Boolean

Set wbk = ThisWorkbook
On Error Resume Next

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

Ans = MsgBox(msg, vbQuestion + vbYesNoCancel)

Select Case Ans
Case vbYes
wbk.Save
' more code here (optional)

Case vbNo
wbk.Saved = True 'set bogus property value
' more code here (optional)

Case vbCancel: bBeforeShutDown = True
End Select

End Function


Enjoy...
GS

[GS]

Alex,

The above reply is in the wrong place. It should have followed the next two.
Sorry for any confusion caused!

Regards,
GS

[Alex]

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

[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

[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