Difference between before_close and auto_close

[phcvergouwe]

Dear group,

I have a weird problem concerning the difference between Workbook_BeforeClose and auto_close. I used to use an auto_close macro (in a separate module) which does a lot of things (calling subroutines contained in separate modules!) and tried recently to copy everything to the workbook_beforeclose-macro (in the ThisWorkbook-module). 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]...
>
> 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?
>
>
> --
> phcvergouwe
> ------------------------------------------------------------------------
> phcvergouwe's Profile:
http://www.excelforum.com/member.php...o&userid=34659
> View this thread: http://www.excelforum.com/showthread...hreadid=544250
>

[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
[email protected]
"phcvergouwe" <[email protected]>
wrote in message
news:[email protected]...
>
> 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?
>
>
> --
> phcvergouwe
> ------------------------------------------------------------------------
> phcvergouwe's Profile:
> http://www.excelforum.com/member.php...o&userid=34659
> View this thread: http://www.excelforum.com/showthread...hreadid=544250
>

[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]...
>
> 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?
>
>
> --
> phcvergouwe
> ------------------------------------------------------------------------
> phcvergouwe's Profile:
http://www.excelforum.com/member.php...o&userid=34659
> View this thread: http://www.excelforum.com/showthread...hreadid=544250
>

[phcvergouwe]

Don, Bob, thanks for replying. Here is (part of) my code (apologies for some names, but I use some Dutch in names, to avoid confusing with 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 settings 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 making changes
Next Teller
If Worksheets(HuidigBlad.Name).Visible = True Then
Application.GoTo 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!

Paul

[phcvergouwe]

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

[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!