OnClose procedure

Flyingmeatball

Board Regular
Joined
Aug 15, 2007
Messages
65
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
 

Some videos you may like

Excel Facts

Excel Wisdom
Using a mouse in Excel is the work equivalent of wearing a lanyard when you first get to college

VoG

Legend
Joined
Jun 19, 2002
Messages
63,650
Try

Rich (BB code):
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

Board Regular
Joined
Aug 15, 2007
Messages
65
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,109,538
Messages
5,529,431
Members
409,876
Latest member
Akash Yadav
Top