OnClose procedure

[Flyingmeatball]

I have a sub for my current workbook set up to call a macro to "tidy things up" before they close the sheet. The problem is the "save excel" dialog is then prompted after these things have run. If the user then presses cancel, they don't actually exit the workbook and are stuck in an ugly limbo of a security sheet I created. Does anyone know what the syntax is to say, "Only cleanup if they are ACTUALLY leaving the workbook, not just if they press the close button". I don't want them to reach the security page by pressing cancel on the saving dialog box. I've included the code below so you can see what I have. I know the sub declaration includes a Cancel as a boolean, but I don't know how to reference them clicking the cancel button triggering a cancel.

Thanks!

Private Sub Workbook_BeforeClose(Cancel As Boolean)

'removes assumptions established on run
Range("D:F").Select
Selection = cb.ClearDataND
Selection.clear

'reset the security sheet
Sheets("Security").Visible = True

'code below hides the "important" sheet
Sheets("mySheet").Visible = xlVeryHidden
'don't worry about this line
cb.SetCBAutoLoad (False)

End Sub

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.EnableEvents = False
'removes assumptions established on run
Range("D:F").Select
Selection = cb.ClearDataND
Selection.Clear

'reset the security sheet
Sheets("Security").Visible = True

'code below hides the "important" sheet
Sheets("mySheet").Visible = xlVeryHidden
'don't worry about this line
cb.SetCBAutoLoad (False)
Application.EnableEvents = True
End Sub

 

[Flyingmeatball]

This is causing my macros that are supposed to run on open and on close to fail. Before on close it would switch to a security page. Upon open it would move away from the security page if they enabled macros. Now its having trouble switching between the two.

Thoughts?

Thanks!