Run MAcro if Excel is left unattended

[wmtsub]

So I would like to automaticaaly protect a worksheet if the user is no longer inputg data after 10 seconds.
Can this be done?

 

[Yongle]

Try this
- selecting any cell or amending a cell value triggers WaitAgain
- amend password to the correct one
- amend constant WaitSecs to decrease\increase wait time

After adding the code, save the workbook, close it and re-open it so that Workbook_Open updates variable TimeCheck

In ThisWorkbook module

Private Sub Workbook_Open()
    TimeCheck = Now
End Sub

In sheet module

Private Sub Worksheet_Change(ByVal Target As Range)
    Trigger
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Trigger
End Sub

Private Sub Trigger()
    TimeCheck = Now
    CheckTime
End Sub

In standard module

'[COLOR=#008080][I]these variables must be placed above all procedures[/I][/COLOR]
Public TimeCheck
Const WaitSecs = [COLOR=#ff0000]10[/COLOR]
Const SecondsPerDay = 86400

Sub WaitAgain()
    Dim AlertTime, TimeStr As String
    TimeStr = "00:00:" & Format(WaitSecs, "00")
    AlertTime = Now + TimeValue(TimeStr)
    Application.OnTime AlertTime, "CheckTime"
End Sub

Sub CheckTime()
    Select Case (Now - TimeCheck) * SecondsPerDay
        Case Is > WaitSecs: ActiveSheet.Protect Password:="[COLOR=#ff0000]password[/COLOR]"
        Case Else:  WaitAgain
    End Select
End Sub

 

[wmtsub]

Sorry fo my lack of understanding. Do I use one of the three or all three? How do i activate it.

 

[Yongle]

All 3 are required
Macro is triggered whenever a cell in the worksheet is selected or amended

 

[wmtsub]

Awesome. TKS. But ran into a little issue where after starting the macro [b4 it locked the sheet] I changed focus to another sheet and it locked that one instead. How can I force the macro to run on the origianl sheet?

 

[Yongle]

Sorry if I misunderstood your requirements.
Can you explain exactly how you want the macro to run and I will tailor it to match. Thanks

1. Do you want the macro to only apply to one sheet in the workbook?
2. When should it kick in? - when the sheet has been inactive or when the workbook has been inactive for 10 seconds
3. If the time limit is exceeded should the user have access to everything else in the workbook apart from that one sheet?

thank you

 

[wmtsub]

Thank you for your dedication. Currently all worksheets in the book are protected. I unprotect a sheet as I need to access it. This macro works 99% perfect in the manor it is written. The only issue was as I said I once focused on a sheet in a different workbook and protected that by mistake. I would like the ability to run this on any of the worksheets but insure it locks the worksheet it started on regardless of the current focus.

 

[Yongle]

as I said I once focused on a sheet in a different workbook

Read your previous posts
You did not mention a different workbook previously only a different sheet
I need to test a couple of ideas, but Workbook_Activate and Workbook_Deactivate macros should be abole to handle this
So If user activates a different workbook do you want the (correct) worksheet to be protected immediately?
- if not, what should happen?

thanks

 

[wmtsub]

You are right, but I tested the issue and it was apparent on workbooks as well so I thought protecting that would also protect the worksheet. 2 birds w/1 stone. Again thanks for the help.

 

[wmtsub]

Bump,