activeworkbook.close not functioning

[Myke]

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

 

[Smitty]

What's in the Workbook_BeforeClose sub?

Smitty

 

[Mudface]

Are both the ShutDown procedure and the Before_Close procedure in the same workbook?

 

[Myke]

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]

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

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

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]

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]

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: