VBA To Re-lock a specific cell after 30 seconds

[Artik]

It keeps telling me that it can't run the Macro LockRange, because it might be unavailable etc...etc...

You had to screw up something.

Unfortunately, I got lost in the code logic.

Well then again.

Private Sub Worksheet_Change(ByVal Target As Range)    
    Dim rng         As Range
    Dim rngM        As Range
    Dim rngScope    As Range
    Dim boolUnlock  As Boolean

1    Set rngScope = Intersect(Target, Me.Range("J2:J" & Rows.Count))

    If Not rngScope Is Nothing Then
        Me.Unprotect Password:=g_strPASS

2        For Each rngM In rngScope
3            If Not IsError(rngM.Value) Then
                If rngM.Value > 0 Then

4                    Set rng = rngM.Offset(0, 3)

5                    rng.Locked = False
6                    boolUnlock = True
                End If
             End If
         Next rngM

        Me.Protect Password:=g_strPASS

7        If boolUnlock Then
8            Application.OnTime Now + TimeSerial(0, 0, 10), "'LockRange """ & rngScope.Offset(0, 3).Address(External:=True) & """'"
         End If

    End If
End Sub

Because the user can enter multiple values at the same time in the J column (e.g. Ctrl + V or Ctrl + Enter when several cells are selected), therefore use the For Each ... Next loop.
1. Determine the common part of column J and the range in which the value was entered. If something is entered in another column, the procedure will end.
2. Loop over the range rngScope cells.
3. First, check that the cell being tested does not contain a sheet error. People can type a different things. A spreadsheet error does not have to be the result of a formula. If you did not apply this condition, and immediately check if the value is greater than 0, then there would be a runtime error with the sheet error value.
4. Create a cell reference in column M.
5. Unlock the cell in column M.
6. Set the boolUnlock flag to True when at least one cell in the rngScope range is greater than 0.
7. When the flag is True ...
8. Call OnTime event for NN seconds. The first parameter is the time when the macro is to be run, the second parameter is the name of the macro to be run. MS in the help says nothing that we can pass arguments to the macro called. This is where I show how to do it. The text string must begin and end with an apostrophe. Arguments must be separated by commas.Text arguments must be enclosed by additional quotation marks.
Examples of the second OnTime event parameter:

"'MyMacro1 1.234, 3'"
"'MyMacro2 ""Wow :-)""'"
"'MyMacro3 1.234, ""Wow :-)"", 3'"

To the LockRange procedure we pass the address of the rngScope range shifted by 3 columns (i.e. the address of the cells in the M column). Additionally, in the Address property we add the parameter External: = True. This ensures that the LockRange procedure will "know" which sheet to unlock, lock the cells and lock sheet again.


I attach a workbook that works for me.


Artik