Workbook_BeforeClose problem

[Jac Tremblay]

Hi everyone
I have an Excel application that is a workbook with a worksheet as frontend. When the workbook opens, the Workbook_Open event is triggered and does its job well. When the job is done, I close the application without saving changes and hopes that the Workbook_BeforeClose event will do its job too. But it does not. In debug mode, the lines of code go by but nothing happens really. I just want to restore the command bars, the formula bar and so on
I have checked the newsgroup similar problems and cannot find the problem
The code that closes the application is
' ****
Workbooks(strConstAppAlimenter).Close SaveChanges:=Fals
' ****
I tried to put this line before but it does not work either
' ****
Application.EnableEvents = Tru
' ****
Here is the code for the Workbook_BeforeClose event
' ******************************************************************
Private Sub Workbook_BeforeClose(Cancel As Boolean
Application.ScreenUpdating = Tru
Workbooks(strConstAppFeed).Activat
Range("A1").Selec
With Applicatio
.DisplayStatusBar = Tru
.StatusBar = "
.WindowState = xlNorma
.WindowState = xlNorma
.Width = 53
.Height = 53
.Left =
.Top =
End Wit
With ActiveWindo
.Width = 50
.Height = 42
.DisplayGridlines = Tru
.DisplayHeadings = Tru
.DisplayHorizontalScrollBar = Tru
.DisplayVerticalScrollBar = Tru
.DisplayWorkbookTabs = Tru
End Wit
If booCommandBarStandard The
Application.CommandBars("Standard").Visible = Tru
End I
If booCommandBarFormatting The
Application.CommandBars("Formatting").Visible = Tru
End I
Application.DisplayFormulaBar = Tru
ActiveWorkbook.Saved = Tru
End Su
' ******************************************************************
Thanks.

 

[Jac Tremblay]

Hi again
You probably understood that
Workbooks(strConstAppAlimenter).Close SaveChanges:=Fals
was meant to be
Workbooks(strConstAppFeed).Close SaveChanges:=Fals
Thanks.

 

[William]

Hi Jac

Why do you need the line below as the Workbook_BeforeClose event will
ultimately close your workbook.
Workbooks(strConstAppAlimenter).Close SaveChanges:=False

Also, try changing the references "Workbooks(strConstAppFeed)" to
"Workbooks(strConstAppFeed.xls)"
--
XL2002
Regards

William

(e-mail address removed)

| Hi everyone,
| I have an Excel application that is a workbook with a worksheet as
frontend. When the workbook opens, the Workbook_Open event is triggered and
does its job well. When the job is done, I close the application without
saving changes and hopes that the Workbook_BeforeClose event will do its job
too. But it does not. In debug mode, the lines of code go by but nothing
happens really. I just want to restore the command bars, the formula bar and
so on.
| I have checked the newsgroup similar problems and cannot find the problem.
| The code that closes the application is:
| ' *****
| Workbooks(strConstAppAlimenter).Close SaveChanges:=False
| ' *****
| I tried to put this line before but it does not work either:
| ' *****
| Application.EnableEvents = True
| ' *****
| Here is the code for the Workbook_BeforeClose event:
| ' *******************************************************************
| Private Sub Workbook_BeforeClose(Cancel As Boolean)
| Application.ScreenUpdating = True
| Workbooks(strConstAppFeed).Activate
| Range("A1").Select
| With Application
| .DisplayStatusBar = True
| .StatusBar = ""
| .WindowState = xlNormal
| .WindowState = xlNormal
| .Width = 530
| .Height = 530
| .Left = 1
| .Top = 1
| End With
| With ActiveWindow
| .Width = 500
| .Height = 420
| .DisplayGridlines = True
| .DisplayHeadings = True
| .DisplayHorizontalScrollBar = True
| .DisplayVerticalScrollBar = True
| .DisplayWorkbookTabs = True
| End With
| If booCommandBarStandard Then
| Application.CommandBars("Standard").Visible = True
| End If
| If booCommandBarFormatting Then
| Application.CommandBars("Formatting").Visible = True
| End If
| Application.DisplayFormulaBar = True
| ActiveWorkbook.Saved = True
| End Sub
| ' *******************************************************************
| Thanks.

 

[Jac Tremblay]

Hi William
The code
Workbooks(strConstAppAlimenter).Close SaveChanges:=Fals
should read
Workbooks(strConstAppFeed).Close SaveChanges:=Fals
and is at the end of the cmdOk button code (in a userform)
The strConstAppFeed is declared like this (in the modMain module)
Public Const strConstAppFeed As String = "Feed the sheets V11.xls
Thanks for your concern.