Excel, macro: save and close the file in case of inactivity for 10 (tps to see)

Out of category
1

Hello friends,

I know that there are forums dedicated to Excel but I try my luck on Lynkoa because I see that there are connoisseurs and moreover I don't know much about macro.

The purpose of this macro is that it starts when the file is opened and that after a certain amount of time of inactivity of the file it saves and closes. There are several of us who use this file and on different sites and moreover we regularly leave our workstations so if one of us forgets to leave it it annoys the others.

THANK YOU IN ADVANCE FOR YOUR ANSWERS ^_^

2

Hello

For launching a macro when opening Excel, there are some leads on http://www.commentcamarche.net/forum/affich-1903604-vba-executer-une-macro-a-l-ouverture-de-excel

and to timer it on https://www.developpez.net/forums/d24813/logiciels/microsoft-office/excel/macros-vba-excel/timer-feuille-excel/

Kind regards

1 Like
3

Thank you d.roger, I'll see if I can do it and if I understand something. 

4

Here is a file that should close after 30 seconds without touching it (at least it works for me).

I used in ThisWorkbook:

Sub Workbook_open()
    Counter = 0
    Sheet1.Tempo
End Sub

And in Feuil1:

Dim Counter As Integer

Sub Tempo()
    Application.OnTime Now + TimeValue("00:00:01"), "Sheet1.myMacro"
End Sub

Sub myMacro()
    Counter = Counter + 1
    If Counter = 30 Then
        Workbooks("Test.xlsm workbook"). Save
        ActiveWorkbook.Close
        Exit Sub
    End If
    
    Sheet1.Tempo
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count = 0 Then
        Counter = 0
        Sheet1.Tempo
    End If
End Sub

Of course it's just a test so to check, improve, etc

Kind regards

 


classeur_test.xlsm
5

Some changes following various tests:

I used in ThisWorkbook:

Sub Workbook_open()
    Counter = 0
    Sheet1.Tempo
End Sub

And in Feuil1:

Dim Counter As Integer

Sub Tempo()
    Application.OnTime Now + TimeValue("00:01:00"), "Sheet1.myMacro"
End Sub

Sub myMacro()
    Counter = Counter + 1
    If Counter = 10 Then
        Workbooks("Test.xlsm workbook"). Save
        ActiveWorkbook.Close
        Exit Sub
    End If
    Sheet1.Tempo
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 0 Then
        Counter = 0
    End If
End Sub

The main changes are in the Worksheet_Change function with If Target.Count = 0 becoming If Target.Count <> 0 as well as the removal of the Sheet1.Tempo line.

Kind regards


classeur_test.xlsm
6

I don't understand, I can't do it... When I open your excel file it bugs...


enregistrement_et_fermeture_du_fichier_en_cas_dinactivite_pendant_10.jpg
7

Then he told me The clue does not belong to the selection


lindice_nappartient_pas_a_le_selection.png
8

Message that I never got during my tests, I am on Excel 2010. I don't know if it can have an impact.

9

Final Code:

I used in ThisWorkbook:

Sub Workbook_open()
    Counter = 0
    Sheet1.Tempo
End Sub

And in Feuil1:

Dim Counter As Integer

Sub Tempo()
    Application.OnTime Now + TimeValue("00:01:00"), "Sheet1.myMacro"
End Sub

Sub myMacro()
    Counter = Counter + 1
    If Counter = 10 Then
        Workbooks(ThisWorkbook.Name). Save
        ActiveWorkbook.Close
        Exit Sub
    End If
    
    Sheet1.Tempo
End Sub

Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count <> 0 Then
        Counter = 0
    End If
End Sub


classeur_test.xlsm
2 Likes