Workbook_BeforeClose

[Dave Unger]

Hello everyone,

I placed this post a few days ago, but my question remains
unaswered. This is a re-word of that post.

I have a large application that “hides” Excel on startup (gridlines,
headings, visibility, etc), then restores everything on exit.
Everything works fine if closing the workbook manually, the problem
appears when trying to close via a procedure. The following is a
simplified example of the code.

Sub CloseMe()
ThisWorkbook.Close savechanges:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wk As
Worksheet
Range("A1").Interior.ColorIndex = 4
Application.DisplayFormulaBar = True
ActiveWindow.DisplayGridlines = True
End Sub

If I step thru the code starting at CloseMe code, it steps thru to the
Workbook_BeforeClose procedure. There, the first line gets executed,
but nothing happens on the next two. Yet when I close the workbook
manually, it works as expected. I’ve tried this on Xl97 & 2007, with
the same results.

I have to admit, this one caught me by surprise. I can’t seem to find
anything regarding this in the groups/internet. Any advice would be
most appreciated.

Thanks for your help,

regards,

Dave U

 

[ryguy7272]

Not sure if this will help or not, but I use this kind of code when closing
my WB:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Call 'yourmacronamehere'
End Sub

That works fine for me. All I can think of is put the important code in
another Sub...End Sub and then call that Sub.

Regards,
Ryan---

 

[Tim Zych]

I can validate this. Looks like Excel does not make application or
format-related changes in this context. It will change the value of a cell,
but not change its color or perform other actions in the example.

Here's the example I used..modified from yours.

Sub CloseMe()
ThisWorkbook.Close savechanges:=True
End Sub
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Cancel = True
Dim cell As Range
With ActiveCell
.Value = 5 ' works
For Each cell In .Cells(2, 1).Resize(4, 4)
cell.Value = 3 ' works
Next
.Interior.ColorIndex = 4 ' doesn't work
.EntireColumn.Hidden = True ' doesn't work
.Font.Bold = True ' doesn't work
End With
End Sub

But you should be able to work around since CloseMe can run the code.

Tested using XL2003

--
Tim Zych
http://www.higherdata.com


Hello everyone,

I placed this post a few days ago, but my question remains
unaswered. This is a re-word of that post.

I have a large application that “hides” Excel on startup (gridlines,
headings, visibility, etc), then restores everything on exit.
Everything works fine if closing the workbook manually, the problem
appears when trying to close via a procedure. The following is a
simplified example of the code.

Sub CloseMe()
ThisWorkbook.Close savechanges:=True
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean) Dim wk As
Worksheet
Range("A1").Interior.ColorIndex = 4
Application.DisplayFormulaBar = True
ActiveWindow.DisplayGridlines = True
End Sub

If I step thru the code starting at CloseMe code, it steps thru to the
Workbook_BeforeClose procedure. There, the first line gets executed,
but nothing happens on the next two. Yet when I close the workbook
manually, it works as expected. I’ve tried this on Xl97 & 2007, with
the same results.

I have to admit, this one caught me by surprise. I can’t seem to find
anything regarding this in the groups/internet. Any advice would be
most appreciated.

Thanks for your help,

regards,

Dave U

 

[Dave Unger]

Hello,

Thanks RyGuy and Tim, for confirming this for me, I'd suspected that
I'd need a workaround. I guess the other thing that surprises me is
that I can't find any mention of this in the groups, you'd think
someone else would have run into this as well. Thanks again for your
help,

regards,

Dave U