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

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
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??
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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:
 
Upvote 0

Forum statistics

Threads
1,213,527
Messages
6,114,142
Members
448,551
Latest member
Sienna de Souza

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
Back
Top