I hate to be a pest, but, I think I'm nearing completion of my project.
Since my workbook is being used as a data entry front-end, I do not want
Excel to ask the user about saving changes (to the workbook). In my code, I
know if the user has saved his data to the database. If the user tries to
close or exit without having saved the data, I want to give him a chance to
save it, then close or exit. This is the code I've tried and the results.
If someone can see that I'm doing something wrong, or, better yet, if anyone
knows how to do this correctly, I would be greatly appreciate some feedback.

In "ThisWorkbook"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer

intResponse = MsgBox("Data Not Saved, Quit anyway?", vbYesNoCancel, "Not
Saved")
If intResponse < vbYes Then
Cancel = True
Exit Sub
Else
'Prevents this event from being triggered a second time
Application.EnableEvents = False
'Don't display the save changes dialog
ThisWorkbook.Close savechanges:=False
Application.Quit
End If

This works the first time, but, if I open the workbook again, without having
exited Excel, this code does not run and I get prompted by Excel about
saving changes. Also, if the user makes a change and then tries to exit
Excel, the workbook closes, but the application continues to run, forcing
them to click 'exit' again.
Thanks in advance for any help you can give.

--
Bob Holmes

If you close the workbook how can the macro continue to fire?

--
Don Guillett
SalesAid Software

"Bob Holmes" wrote in message
...
I hate to be a pest, but, I think I'm nearing completion of my project.
Since my workbook is being used as a data entry front-end, I do not want
Excel to ask the user about saving changes (to the workbook). In my code,
I
know if the user has saved his data to the database. If the user tries to
close or exit without having saved the data, I want to give him a chance
to
save it, then close or exit. This is the code I've tried and the results.
If someone can see that I'm doing something wrong, or, better yet, if
anyone
knows how to do this correctly, I would be greatly appreciate some
feedback.

In "ThisWorkbook"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer

intResponse = MsgBox("Data Not Saved, Quit anyway?", vbYesNoCancel, "Not
Saved")
If intResponse < vbYes Then
Cancel = True
Exit Sub
Else
'Prevents this event from being triggered a second time
Application.EnableEvents = False
'Don't display the save changes dialog
ThisWorkbook.Close savechanges:=False
Application.Quit
End If

This works the first time, but, if I open the workbook again, without
having
exited Excel, this code does not run and I get prompted by Excel about
saving changes. Also, if the user makes a change and then tries to exit
Excel, the workbook closes, but the application continues to run, forcing
them to click 'exit' again.
Thanks in advance for any help you can give.

--
Bob Holmes

I found my own solution that seems to work.
When data on the sheet has changed, the user is notified, in the beforeclose
event of the workbook, that changes were not saved. If they want to save
the data before leaving, I exit the beforeclose event. If they do not want
to save the changes, I set the "saved" property to "True" and let the
routine continue. If the user wanted to close the workbook, it closes, if
they wanted to Exit Excel, then it will exit.
Thanks to anyone who put any thought into this.

--
Bob Holmes MCNGP #31
"Bob Holmes" wrote in message
...
I hate to be a pest, but, I think I'm nearing completion of my project.
Since my workbook is being used as a data entry front-end, I do not want
Excel to ask the user about saving changes (to the workbook). In my code,
I
know if the user has saved his data to the database. If the user tries to
close or exit without having saved the data, I want to give him a chance
to
save it, then close or exit. This is the code I've tried and the results.
If someone can see that I'm doing something wrong, or, better yet, if
anyone
knows how to do this correctly, I would be greatly appreciate some
feedback.

In "ThisWorkbook"
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intResponse As Integer

intResponse = MsgBox("Data Not Saved, Quit anyway?", vbYesNoCancel, "Not
Saved")
If intResponse < vbYes Then
Cancel = True
Exit Sub
Else
'Prevents this event from being triggered a second time
Application.EnableEvents = False
'Don't display the save changes dialog
ThisWorkbook.Close savechanges:=False
Application.Quit
End If

This works the first time, but, if I open the workbook again, without
having
exited Excel, this code does not run and I get prompted by Excel about
saving changes. Also, if the user makes a change and then tries to exit
Excel, the workbook closes, but the application continues to run, forcing
them to click 'exit' again.
Thanks in advance for any help you can give.

--
Bob Holmes