Need help with Workbook_BeforeClose event
in [Programming]
|
From: Bob on 18 Apr 2010 20:10 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 > . >
From: Dave Peterson on 18 Apr 2010 21:29 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
From: Dave Peterson on 18 Apr 2010 21:34 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
From: Dave Peterson on 18 Apr 2010 21:34
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 |