before close event userform problem

[dlherr1988]

Alright so I've run into a problem with a before close event. Here is the very simple code as it stands

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
frmCloseSplash.Show
End Sub

So I'm calling a userform that I've created in which the user inputs the publishers name, publish date, last edited by, last edited date, data source, purpose, and company security level of the report they just created. The user then has two options on the user form "Save" and "Close."

If the user clicks save the code takes the user through the getsaveasfilename procedure, saves the file, and formats the headers based on the user inputs before closing.

If the user clicks Close the user is asked if they are sure they want to close the file without saving. If they click yes the file closes, and if they click no the user returns to the userform. (I used an if statement with a variable Answer which is defined by the message box and saying if Answer = vbYes Then 'close the workbook Else 'do nothing)

Here's my problem - if I take the Cancel = True out of the BeforeClose Event the userform is shown and the document closes without allowing the user to do any inputs if the document has not had any changes since the last save OR gives the "Save As" prompt. BUT, with the Cancel = True in the user goes through the user inputs on the userform and the file doesn't close once everything is finished

This problem has aroused because I'm trying to AVOID the stupid "Save As" prompt that Excel brings up when closing the file if changes have been made since the last save. I want my userform to be the only prompt for saving or simply closing the file.

I have also tried this code:

Activeworkbook.Saved = True
frmCloseSplash.Show

And a combination of the two without any luck as it does the same thing as if I take out the "Cancel = True."

I need to avoid the "Save As" prompt the Excel brings up when the user hasn't saved recently, and give the user the ability to use ONLY my form to save or not save the file before closing the file regardless if they have recently saved or not.

I need help as I've come against a brick wall here.

 

[dlherr1988]

For anyone who is curious I came up with my own - somewhat odd - workaround for this problem.

Before my userform ShowModal was set to False so users could still manipulate other screens and come back to that userform. I decided to reverse this and make it True. That half fixed the problem.

Then in the code for the "Save" and "Close" command buttons I added a redundant

Application.ThisWorkbook.Close

line of code at the end of the procedures that take place when the user clicks the "Save" or "Close" command buttons. This initially created a problem of a "cannot show this userform modally" error when the tried to return to the userform for any reason.

The final fix came by changing the before close event code to this:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
ActiveWorkbook.Saved = True
If frmCloseSplash.Visible = False Then
frmCloseSplash.Show
Else
End If
End Sub

So far this is working exactly the way I wanted it. Now I need to work on converting this code to work as an add - in that will be distributed across my workgroup for use in all our excel files.