Re-protect sheet automatically after a certain time

[anihak]

Although I am used to write and manipulate vba macros, I have never used timer properties or functions.

What I would like to do is have a simple macro, activated by a button, that will unprotect a sheet of my workbook, and the reprotect it automatically after, say, 30 minutes.

Is that possible?

Thanks a bunch!

 

[Trevor G]

Look at the timer events, keep the main macro separate and then use timer events to call the macro every 30 minutes. Here is an example of timer events, in this case it is counting down based on a value in a cell. If you have some knowledge of VBA then you should be able to adapt it.

Dim CountDown As Date
Sub Timer()
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub Reset()
Dim count As Range
Set count = [A1] ' A1 contains the number of seconds for the countdown.
count.Value = count.Value - 1
If count <= 0 Then
MsgBox "Countdown complete."
Exit Sub
End If
Call Timer
End Sub
Sub DisableTimer()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub

 

[anihak]

So here is what I got. It works. My problem is now that the 30 I entered doesn't translate to 30 minutes in real time. How can we do that?

Sub Timer()
ActiveSheet.Unprotect
Dim CountDown As Date
CountDown = Now + TimeValue("00:00:01")
Application.OnTime CountDown, "Reset"
End Sub
Sub DisableTimer()
On Error Resume Next
Application.OnTime EarliestTime:=CountDown, Procedure:="Reset", Schedule:=False
End Sub
Sub Reset()
Dim count As Long
count = 30
count = count - 1
If count <= 0 Then
Worksheets("rt").Protect DrawingObjects:=False, Contents:=True, Scenarios:= _
False
MsgBox "Time is up."
Exit Sub
End If
Call Timer
End Sub

Many thanks!