Difference between before_close and auto_close

[phcvergouwe]

Dear group,

I have a weird problem concerning the difference between before_close
and auto_close. I used to use an auto_close macro which does a lot of
things (calling subroutines contained in separate modules!) and tried
recently to copy everything to the workbook_beforeclose-macro.
Furthermore, I made a toolbar-button with has only the
"thisworkbook.close" statement in it. If I close Excel through the "X"
in the right-above corner, all is fine. If I close using the
toolbar-button, it seems that "some" of my code in the
workbook_beforeclose-macro does not run (it doesn't run the
screenupdating=false setting, it doesn't unprotect my sheets etc.,
although this code is in it and it does run when I close Excel through
the "X" in the right-above corner. I am puzzled! Any suggestions?

 

[Bob Phillips]

You probably shouldn't be using ThisWorkbook.Close, but
Activeworkbook.Close.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"phcvergouwe" <[email protected]>
wrote in message
news[email protected]...

 

[phcvergouwe]

Thanks a lot for your (very swift!) reply. I tried your solution, but it
did not make any difference: the "X" works great, both the
"Activeworkbook.close" and "thisworkbook.close" seem to work only
partially. Any more suggestions?

 

[Don Guillett]

Perhaps you should post your coding efforts for comments.

--
Don Guillett
SalesAid Software
(e-mail address removed)
"phcvergouwe" <[email protected]>
wrote in message
news[email protected]...

 

[Bob Phillips]

Maybe post all the code.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"phcvergouwe" <[email protected]>
wrote in message
news[email protected]...

 

[phcvergouwe]

Don, Bob, thanks for replying. Here is (part of) my code (apologies fo
some names, but I use some Dutch in names, to avoid confusing wit
keywords):

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim intOpslaan As Integer
On Error GoTo ErrorHandler
StapAantal = 17
Call ProgrammaBegin
Call CopyrightCheck
Call WerkboekBerekenen
Call WerkboekStoppen
ErrorHandler:
If Err Then Call FoutAfhandeling("Auto_Close")
On Error GoTo Einde
Call WerkBalkenTerugzetten
Call ProgrammaEinde
Call CommandBarButton_Click
If ThisWorkbook.ReadOnly Then
ThisWorkbook.Saved = True
End If
If ThisWorkbook.Saved = False Then
intOpslaan = MsgBox("De wijzigingen in " & ThisWorkbook.FullName
vbCrLf & _
"zijn nog niet opgeslagen." & vbCrLf & vbCrLf & _
"Wilt u alsnog opslaan? ", vbYesNo + vbInformation
vbMsgBoxSetForeground, ThisWorkbook.Name)
If intOpslaan = vbYes Then Call mnuOpslaan
End If
ThisWorkbook.Saved = True
End
Einde:
If Err Then Call FoutAfhandeling("Auto_Close")
Erase aRow()
Erase aColumn()
Erase aRange()
Set OldCell = Nothing
Set HuidigBlad = Nothing
Set HuidigeCel = Nothing
Set PB = Nothing
Set colCBS = Nothing
End Sub

This is in the "ThisWorkbook" module: ProgrammaBegin contains setting
and is in a separate module:
Sub ProgrammaBegin()
Dim Teller As Integer
Call mnuActiveren
ReDim Preserve aRow(ThisWorkbook.Worksheets.Count) As Long
ReDim Preserve aColumn(ThisWorkbook.Worksheets.Count) As Integer
ReDim Preserve aRange(ThisWorkbook.Worksheets.Count) As String
lngCalcMode = Application.Calculation
With Application
.OnKey "%{F11}", ""
.OnKey "%{F8}", ""
.OnKey "^{BREAK}", ""
.EnableCancelKey = xlDisabled
.CutCopyMode = False
.EnableEvents = False
.DisplayAlerts = False
.Interactive = False
.DisplayCommentIndicator = xlNoIndicator
.Calculation = xlCalculationManual
.IgnoreRemoteRequests = True
.WindowState = xlMaximized
.ScreenUpdating = False
End With
Set PB = New clsProgBar
PB.Title = ThisWorkbook.Name
PB.Caption1 = "Even geduld alstublieft"
PB.Caption2 = "gegevens worden bijgewerkt"
PB.Show
StapNummer = 0
Select Case ActiveSheet.Name
Case strMacroSecurity
Sheets(strRitten).Activate
Case strTransport
Sheets(strRitten).Activate
Case strDatabaseRoutes
Sheets(strRitten).Activate
End Select
Set HuidigBlad = ActiveSheet
Set HuidigeCel = ActiveCell
For Teller = 1 To ThisWorkbook.Worksheets.Count
Worksheets(Teller).Activate
Call VensterPositieBewaren(Teller) ' run this before makin
changes
Next Teller
If Worksheets(HuidigBlad.Name).Visible = True Then
Application.GoT
Reference:=Sheets(HuidigBlad.Name).Range(HuidigeCel.Address)
End If
Call StatusBalkUpdaten
End Sub

The code activated by the button is:
Sub mnuAfsluiten()
ThisWorkbook.Close ' Activeworkbook.close gives the same problems
End Sub


When the code in "Workbook_Beforeclose" is in the "Auto_close" macro
everything works fine. Thanks for trying to help me!

Pau

 

[phcvergouwe]

Here's all the code. The names speak for themselves, I hope

+-------------------------------------------------------------------
|Filename: modPF_52_04_JJJJ_MM_XLS_Blad.zip
|Download: http://www.excelforum.com/attachment.php?postid=4801
+-------------------------------------------------------------------

 

[GS]

Your Workbook_BeforeClose event suggests that the Auto_Close routine still
exists. I'm not sure why you're splitting your shutdown between the two but
AFAIK, using both causes conflicts so I suggest using one or the other
instead.

HTH
Regards,
Garry

 

[phcvergouwe]

Garry, thanks for replying. I am currently developing my code, so I may
have sent an intermediate version. But the funny part is: if I put
everything in the auto_close macro AND leave a workbook_beforeclose
macro in, everything works fine. If I put everything in the
workbook_beforeclose macro and delete the auto_close macro, everything
works only if I press the "X". If I press my own commandbutton, my
problem occurs!