Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
After I Save a workbook, I then Close it. But when I do so, Excel prompts me
to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
Forgive me, I should have said, "Is there some code I can add to the
Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
hi
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
Sometimes, the workbook isn't active when it's being closed.
Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here me.Close True '************** End Sub ======== But as a user, I wouldn't want the developer to decide for me whether the workbook should be saved when it's closed. And I wouldn't want the developer to discard my changes, either. I've never understood how developers keep breathing when users have this kind of thing forced on them <vbg. FSt1 wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
Thanks for your help! I really appreciate it.
BTW, if I can impose on you one more time, can you tell me the difference between using ActiveWorkbook.Close True versus using ThisWorkbook.Save? Thanks again. Regards, Bob "FSt1" wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
Dave - Good point! Thanks for the additional info. I greatly appreciate it.
FYI, I also utilize the Workbook_BeforeSave event to check to ensure that all required fields have been inputted. So by the time a user is ready to Close the workbook, he/she has already performed an intentional/deliberate Save. I am not making any decisions on behalf of the user about what to (and what not to) save. I am merely using the Workbook_BeforeClose event to perform some very minor housekeeping. But at the same time, since the user has already performed an intentional/deliberate Save, I want to save (no pun intended) him/her the chore of having to deal with yet another dialog box when they Close. Hence, the reason why I want to employ "me.Close True". Regards, Bob "Dave Peterson" wrote: Sometimes, the workbook isn't active when it's being closed. Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here me.Close True '************** End Sub ======== But as a user, I wouldn't want the developer to decide for me whether the workbook should be saved when it's closed. And I wouldn't want the developer to discard my changes, either. I've never understood how developers keep breathing when users have this kind of thing forced on them <vbg. FSt1 wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob -- Dave Peterson . |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
If the user saves the file exactly with the data the way it should be and then
destroys it by accident and wants to close without saving, doesn't your code just save and close the file? It still sounds dangerous to me. But if you want to do this, then you may want to avoid the _beforesave event. Private Sub Workbook_BeforeClose(Cancel As Boolean) application.enableevents = false 'stop the beforesave event from firing me.save 'just save it application.enableevents = true End Sub You may have noticed that the me.close statement caused the _beforeclose event to fire again. By using the me.save, you'll let excel close the file itself. I think the original suggestion had a problem and my suggestion didn't help with this bug. pps. If you're really only doing house keeping in this last save (and I still wouldn't approach it this way!), then maybe you could put all that housekeeping stuff into the workbook_open or auto_open procedure. It'll be set up the way you want when the user opens the workbook -- not when they close. So your code doesn't have to make a choice (good or bad) when the user is closing the workbook. Bob wrote: Dave - Good point! Thanks for the additional info. I greatly appreciate it. FYI, I also utilize the Workbook_BeforeSave event to check to ensure that all required fields have been inputted. So by the time a user is ready to Close the workbook, he/she has already performed an intentional/deliberate Save. I am not making any decisions on behalf of the user about what to (and what not to) save. I am merely using the Workbook_BeforeClose event to perform some very minor housekeeping. But at the same time, since the user has already performed an intentional/deliberate Save, I want to save (no pun intended) him/her the chore of having to deal with yet another dialog box when they Close. Hence, the reason why I want to employ "me.Close True". Regards, Bob "Dave Peterson" wrote: Sometimes, the workbook isn't active when it's being closed. Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here me.Close True '************** End Sub ======== But as a user, I wouldn't want the developer to decide for me whether the workbook should be saved when it's closed. And I wouldn't want the developer to discard my changes, either. I've never understood how developers keep breathing when users have this kind of thing forced on them <vbg. FSt1 wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob -- Dave Peterson . -- Dave Peterson |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
There are two parts to this question.
The first is what workbook should be used. If you want to close the activeworkbook -- no matter what that is, then use Activeworkbook. But my bet is that you really want to close the workbook that owns the code. And in this case, you could use Me or ThisWorkbook. Me is a keyword that represents the object that owns the code. Since the procedure is in the ThisWorkbook module, Me represents ThisWorkbook. If your line of code were in a worksheet module, then Me would represent that worksheet. (And Me.Parent would represent the workbook that owns the worksheet that owns the code.) If the code were in a General module, then you'd use ThisWorkbook. Me isn't a valid keyword in those general modules. ========= And I'd suggest that you use "me.save" in the code (see the other post). The "me.close savechanges:=true" will actually fire the _beforeclose event (again!). And you don't want that. Bob wrote: Thanks for your help! I really appreciate it. BTW, if I can impose on you one more time, can you tell me the difference between using ActiveWorkbook.Close True versus using ThisWorkbook.Save? Thanks again. Regards, Bob "FSt1" wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob -- Dave Peterson |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Need help with Workbook_BeforeClose event
ps. I still wouldn't do this <vbg.
Bob wrote: Thanks for your help! I really appreciate it. BTW, if I can impose on you one more time, can you tell me the difference between using ActiveWorkbook.Close True versus using ThisWorkbook.Save? Thanks again. Regards, Bob "FSt1" wrote: hi Private Sub Workbook_BeforeClose(Cancel As Boolean) 'some code here ActiveWorkbook.Close True '************** End Sub when you run code on the before close event, that triggers excel into thinking something as changed therefore you need to save the book. again. so you will have to resave the workbook . the above does that automaticly. no extra clicking on extra popups. regards FSt1 "Bob" wrote: Forgive me, I should have said, "Is there some code I can add to the Workbook_BeforeClose event that will auto-save the workbook when I Close it?" "Bob" wrote: After I Save a workbook, I then Close it. But when I do so, Excel prompts me to Save the workbook again. Is there some code I can add to the Workbook_BeforeClose event that will negate the prompt to re-save it (even after I just Saved it!)? Thanks in advance for any help. Bob -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Workbook_BeforeClose Event (help!) | Excel Programming | |||
Problem with Workbook_BeforeClose event | Excel Programming | |||
Workbook_BeforeClose event is not canceled | Excel Programming | |||
Workbook_Beforeclose vs BeforeClose Event | Excel Programming | |||
Workbook_BeforeClose Event | Excel Programming |