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

--
Regards,
Tom Ogilvy


"TonyD" wrote:

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