Need help with Workbook_BeforeClose event

[Bob]

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

 

[Bob]

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?"

 

[FSt1]

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

 

[Dave Peterson]

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>.

 

[Bob]

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

 

[Bob]

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]

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.

 

[Dave Peterson]

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.

 

[Dave Peterson]

ps. I still wouldn't do this <vbg>.

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