activeworkbook.close not functioning

Myke

New Member
Joined
Jan 23, 2003
Messages
39
I'm trying to put an "ActiveWorkbook.Close" function into a sub called "shutdown" in module1, but when I then run that macro, it doesnt close as expected. I suspect its because in the "This Workbook" section I have a "workbook_beforeclose" sub. How can I get around this???
 

Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339
Are both the ShutDown procedure and the Before_Close procedure in the same workbook?
 

Myke

New Member
Joined
Jan 23, 2003
Messages
39
The workbook_beforeclose sub has some script that re-locks the sheet and then perfoms a save & close (a previous sub unlocks it depending on the user)

What I'm trying to do is put another macro that "times out" the user so that if they dont close it within two minutes, it closes anyway. This is why I want my module to close the book.

Does this make any sense or am I rambling??
 

Mudface

MrExcel MVP
Joined
Feb 18, 2002
Messages
3,339

ADVERTISEMENT

You could do this by setting a flag within your ShutDown procedure and then executing the code or not in the BeforeClose procedure depending on the flag's value. For example, place this in module 1, note that the declaration of blFlag must be outside of any procedures: -

Code:
Public blFlag As Boolean

Public Sub ShutDown()

blFlag = True
ActiveWorkbook.Close

End Sub

So, when you run your Shutdown procedure, the flag is set to True. Now, amend your BeforeClose procedure similar to the following: -

Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

If Not blFlag Then
    ' your code
End If

End Sub

Now your BeforeClose code will only run if the flag is False and will be ignored if your ShutDown procedure is run first.
 

Myke

New Member
Joined
Jan 23, 2003
Messages
39
Maybe this will be a better explanation:

Private Sub Workbook_BeforeClose(Cancel As Boolean)

Sheets("Access Requests").Unprotect
Sheets("Access Requests").Select
Range("D4:AQ33").Select
Selection.Locked = True
Selection.FormulaHidden = False
Range("D4").Select
ActiveSheet.Protect


ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

and the Macro I'm trying to make run is.....


Sub ShutDown()
Windows("Data Prot1.xls").Activate

ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub
 

Myke

New Member
Joined
Jan 23, 2003
Messages
39
Fab, worked perfectly. I'm curious though. How do you guys learn this stuff. I mean are you professional programmers or what because I've tried reading the "dummies guide to VBA" books but I still dont understand it properly (although having the IQ of a fieldmouse probably doesnt help me) :wink:
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,563
Messages
5,765,119
Members
425,262
Latest member
sabry

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top