dlherr1988
New Member
- Joined
- Jun 11, 2009
- Messages
- 4
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.
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.