Workbook.close & beforeclose

[Dave Unger]

Hello,

I’ve searched the groups, but can’t seem to find anything that quite
addresses this, hoping someone can tell me where I’m going wrong.

I have the following code in the BeforeClose event :

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Dim wk As Worksheet

For Each wk In ThisWorkbook.Worksheets
wk.Visible = xlSheetVisible
Next wk

End Sub

When I close the workbook manually, it works as expected. But if I
run
“ThisWorkbook.Close savechanges:=False” from a procedure, it doesn’t.
I can step thru the code manually, but nothing happens. I’m getting
the same results with XL97 & 2007, so obviously I’m missing something
basic here. Any help would be very much appreciated.

regards

Dave U

 

[Mike H]

Hi,

It looks like Excel is doing exactly what your telling it to do. In your
'other' sub your saying close without saving the changes. the workbook before
close event executes and unhides all your sheets but then these changes
aren't saved so the workbook stays in the state it was before the code
executed.

Mike

 

[Dave Unger]

Hi Mike,

Thanks for your reply.

I realize my workbook isn't getting saved, but that's not the issue
here.

Let's add a Stop to the end of the beforeclose event:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim wk As Worksheet
For Each wk In ThisWorkbook.Worksheets
wk.Visible = xlSheetVisible
Next wk

STOP

End Sub

Now, when I close the workbook manually, when the code hits the Stop,
the sheets are visible. When I run “ThisWorkbook.Close
savechanges:=False” (or true) from a procedure, the sheets are NOT
visible at the stop.

I've played around with this for couple of days, and searched the
Internet, so far no luck.