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

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

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"phcvergouwe"
wrote in message
...

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

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

Perhaps you should post your coding efforts for comments.

--
Don Guillett
SalesAid Software

"phcvergouwe"
wrote in message
...

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

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(HuidigeCe l.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

--
phcvergouw
-----------------------------------------------------------------------
phcvergouwe's Profile: http://www.excelforum.com/member.php...fo&userid=3465
View this thread: http://www.excelforum.com/showthread.php?threadid=54425

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
+-------------------------------------------------------------------

--
phcvergouw
-----------------------------------------------------------------------
phcvergouwe's Profile: http://www.excelforum.com/member.php...fo&userid=3465
View this thread: http://www.excelforum.com/showthread.php?threadid=54425

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

Maybe post all the code.

--
HTH

Bob Phillips

(remove xxx from email address if mailing direct)

"phcvergouwe"
wrote in message
...

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