Workbook_BeforeClose 1

[ChrisBurch]

On my Workbook_BeforeClose I have the following code. This works as anticipated, except that I don't get the formulaBar or statusBar back.

If I put this code in a module and run it, it works fine. But if I call that same sub from the Workbook_beforeClose, I get the same problem with the 2 bars.

Has anyone any ideas?

Thanks, Chris
excel 97



Private Sub Workbook_BeforeClose(cancel As Boolean)
Dim my_val As Boolean

my_val = Sheet1.Cells(15, 52).Value 'Init val is -1 (true), set to 0 from button

cancel = my_val


If cancel = True Then 'stops close from close control
Exit Sub
Else 'My_Exit button has been pushed

With Application
.DisplayFormulaBar = True
.DisplayStatusBar = True
.AskToUpdateLinks = True
.DisplayCommentIndicator = 1
.DisplayFormulaBar = True
.DisplayStatusBar = True
End With
With ActiveWindow
.DisplayGridlines = True
.DisplayHeadings = True
.DisplayOutline = True
.DisplayHorizontalScrollBar = True
.DisplayWorkbookTabs = True
End With

EnableAllShortcutMenus 'Enable various command bars
Application.Quit

End If
End Sub Chris

It worked yesterday.
It doesn't work today.
That's Windows!

 

[RobBroekhuis]

Have you tried putting a breakpoint in the middle of your code and seeing if the statusbar appears when re-enabled? The only thing I can think of is that somehow (because of an unintentional sequence of events) it gets disabled again after you turn it on.
Rob

 

[ChrisBurch]

Thanks Rob,

I hadn't thought of that, and it was a good suggestion. It clearly shows that the formula bar is not being activated, at any stage. The weird thing is, if I activate it from a sub , from within vba editor, it works fine. Yet if I call the same sub from my exit button, I get a vb error, ie.

Run time error '1004'
Method 'DisplayFormulaBar' of Object '_Application' failed.

So at this point, unless anyone's got any bright ideas, I figure I've expended enough time on this, and am moving on. Chris

It worked yesterday.
It doesn't work today.
That's Windows!