Excel 2010 Private Sub Workbook_BeforeClose(Cancel As Boolean)

[PWD]

Good afternoon.

I have a workbook where I have hidden the Ribbon & most eveerything else on opening. Everything gets returned to its previous state with:-

Private Sub Workbook_BeforeClose(Cancel As Boolean)

What I'd like to be able to do is have a 'Close' button that saves & closes the workbook. In fact I do have that;

ActiveWorkbook.Close SaveChanges:=True

but it doesn't trigger the Before_Close macro.

Any idea what I need to do?

Many thanks,
D€$

 

[MarineMike1985]

Can you not call the BeforeClose Sub before you run the close command?

Call Workbook_BeforeClose(True)
ActiceWorkbook.Close SaveChanges:=True

 

[PWD]

Dang:

"Sub or Function not defined"

But that's the sort of thing I hoped might work Mike.

Many thanks,
D€$

 

[SkipVought]

You cannot CALL an event.

Someone can PERRFORM the event, like Close the workbook, which the user does when they, er, uh, hmmmmm, well...... CLOSE the workbook. So in the CLOSE event you SAVE the workbook.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

Hi Skip; so does this mean that I can't have a big, obvious button that the use can click to close the workbook AND use Workbook_BeforeClose?

BTW if I put a 'break' in my 'Close' code it appears to run through the Workbook_BeforeClose code, but it doesn't actually do anything.

Rats!!!

Many thanks,
D€$

 

[SkipVought]

Why would you?

I suppose you ought to state WHAT it is that you are trying to accomplish.

For instance, I have a workbook that I want to FORCE the user to enable macros, so I save it with ALL sheets VeryHidden, except for a Splash sheet that reminds them to enable macros. If they do NOT, then all the have access to is the Splash sheet. If they enable macros then OPEN event configures the workbook for them to use. When they CLOSE, it configures it back to ONLY the Splash visible and SAVES it. That's the WHAT for my needs.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[PWD]

Hi Skip, it's because I've hidden all the tabs & Ribbon. The user will click between sheets with hyperlinks & I just fancied having a big CLOSE button on the "Contents" sheet.

Many thanks,
D€$

 

[SkipVought]

Well that's fine if your want a button.

So all the button need do is CLOSE the workbook!!!

But the SAVE code MUST be in the workbook_close event.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]