Pop Up Form Based Workbook Close()

[Guest]

I want to create a user form based on Workbook_BeforeClose statement. If the
If statement below finds a "No" in the cell x2 it will open a "Report Does
Not Reconcile" user form with two options based on closing the workbook. The
"Yes" button will allow the user to go straight to a SaveAs option that will
use the filename from cell a2. The "No" button will prevent closing the
workbook and go to cell V105 (which I want to give a Name Range of
"OutcomeTotal") to allow the numbers to be adjusted and reconciled.

Currently I am using the following routine which prevents the workbook
closing if it doesn't reconcile. Sometimes the user needs to be able to close
without reconciling.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
If Worksheets("DIOU Stats").Range("x2").Value = "no" Then
Application.Goto Worksheets("DIOU Stats").Range("u105")
Cancel = True
End If
End Sub

 

[Guest]

Possibly something like this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Worksheets("DIOU Stats").Range("x2").Value = "no" Then
ans = Msgbox("Report does not reconcile",vbyesNo)
if ans = vbYes then
ThisWorkbook.SaveAs FileName:= _
Worksheets("DIOU stats").Range("A1").value
else
Application.Goto Worksheets("DIOU Stats").Range("u105")
Cancel = True
end if
End If
End Sub

 

[Guest]

Tom, the functionality of the routine works except if I say "yes" and then
pick "no" or "Cancel" when the message "A file named XXX already exists in
this location. Do you want to replace it?". If I press either "No" or
"Cancel", then I get a "run time error 1004" message and then if I click
"End" it closes. Any way of avoiding the Run Time error message?

 

[Guest]

Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Worksheets("DIOU Stats").Range("x2").Value = "no" Then
ans = Msgbox("Report does not reconcile",vbyesNo)
if ans = vbYes then
Application.DisplayAlerts = False
ThisWorkbook.SaveAs FileName:= _
Worksheets("DIOU stats").Range("A1").value
Application.DisplayAlerts = True
else
Application.Goto Worksheets("DIOU Stats").Range("u105")
Cancel = True
end if
End If
End Sub

Doesn't offer a choice. If you want a choice, then use Dir to see if such
a file already exists and if it does offer your own choice which you design
into and handle with your own code. If the user chooses to overwrite, you
can then use the KILL command to remove the file before you write a new
version (or use the displayalerts approach I have already shown).