stop macro onclose

[mikalo]

The following code aims to allow data input into the spreadsheet, and
then close it as soon as possible, rather than keeping it open, so that
others can also input data.
When opening the workbook, you get a warning message that you should
aim to close it within 10 minutes.
Then, 10 minutes later, you get a message box suggesting to exit ans
save the workbook.
All this works fine.

PROBLEM
If you close the workbook in less than 10 minutes, the workbook reopens
itself to display the warning "Please exit database if not in use."
This is really silly because the operator did the right thing to close
the book promptly, and now the book is open again...

QUESTION
Can I make the macro conditional (to run only if the workbook is still
open)?
Can I have an "onclose" or "before close" macro that stops the first macro?

Vous pouvez répondre en FRANçAIS.

Thanks,
Puba

-----------------------------------------
Sub autpen()
MsgBox "You will get a prompt for saving and exiting the file after 10
minutes."
Application.OnTime Now + TimeValue("00:10:00"), "autoclose"
End Sub
-------------------------------------
Sub autoclose()
MsgBox "Please exit database if not in use." & Chr(13) & "..." &
Chr(13) & "Click Cancel in following screen to remain in the helpdesk
database, you will not get another prompt."
' closes the active workbook and lets the user decide if
' changes are to be saved or not
ActiveWorkbook.Close
End Sub
---------------------------------

 

[Kobaya]

Re : stop macro onclose

hi mikalo,

you should deactivate call of autoclose procedure
(Application.OnTime Now + TimeValue("00:10:00"), "autoclose")
by adding this code in Sub autoclose(), just before ActiveWorkbook.Close statement.

On Error Resume Next
Application.OnTime Now + TimeValue("00:10:00"), "autoclose", , False

 

[PascalXLD]

Re : stop macro onclose

Bonjour

Il est préférable d'utiliser les macros evenementielles plutot que les anciennes macros auto_open

Voici modifié

dans le Thisworkbook

Option Explicit
Dim monheure As Date

Private Sub Workbook_Open()

MsgBox "You will get a prompt for saving and exiting the file after 10 minutes."

monheure = Now + TimeValue("00:10:00")
Application.OnTime TimeValue(monheure), "totoclose"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
On Error Resume Next
Application.OnTime EarliestTime:=TimeValue(monheure), _
    Procedure:="totoclose", Schedule:=False

End Sub

Dans un module

 Sub totoclose()
MsgBox "Please exit database if not in use." & Chr(13) & "..." & Chr(13) & "Click Cancel in following screen to remain in the helpdesk database, you will not get another prompt."
' closes the active workbook and lets the user decide if
' changes are to be saved or not

ActiveWorkbook.Close
End Sub

Bonc ourage

 

[PascalXLD]

Re : stop macro onclose

oups excuse Kobaya pour la collision

par contre es-tu sûr que ton code fonctionnera sans passer par une variable pour récupérer l'heure

car ton
Now + TimeValue("00:10:00") sera différent dans l'auto_open et dans le beforeclose.

je n'ais pas essayé c'est juste pour savoir ?

 

[mikalo]

Re : stop macro onclose

En fait, il semble il faut ajuster le code de Pascal.
Merci pour votre aide.

This workbook
-------------

Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Closing timer: " & nextime
On Error GoTo xxx
Application.OnTime nextime, "chkexit", , False
MsgBox "timer cancelled"
Exit Sub
xxx:
MsgBox "error while cancelling the timer"
End Sub

Private Sub Workbook_Open()
delay = TimeValue("00:00:50")
nextime = Now + delay
MsgBox "You will get a prompt for saving and exiting after " & delay & " which is at " & nextime
Application.OnTime nextime, "chkexit", , True

End Sub



module1
--------
'dans la partie declarations les deux lignes suivantes
Public nextime As Date
Public delay As Date

Sub chkexit()
If MsgBox("The timer " & nextime & " has elapsed.Do you want to continue working", vbYesNo) = vbYes Then
nextime = Now + delay
MsgBox "You will get another prompt in " & delay & "which is at " & nextime
Application.OnTime nextime, "chkexit", , True
Else
ActiveWorkbook.Close
End If
End Sub