View Single Post
 
Old 05-05-2017, 06:59 AM
Logit Logit is offline Windows 10 Office 2007
Expert
 
Join Date: Jan 2017
Posts: 591
Logit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the roughLogit is a jewel in the rough
Default

Believe this is the solution ... Changed the macro slightly :

New Code :

Code:
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
Dim ws As Worksheet
Dim inputRange As Range
Set ws = Worksheets("Sheet1")
ws.Unprotect
Application.EnableEvents = False


'tell this sub to unprotect only these cells
Set inputRange = Range("B1:C100")


' If the selected cell is not in the range keep the sheet locked
If Intersect(Target, inputRange) Is Nothing Then
'else unprotect the sheet by providing password
'(same as the one that was used to protect this sheet)
Else

    
    Target.Locked = False
    With Target
    If .Cells.Count = 1 Then
        If .Column = 2 And .Row > 1 And .Value = "In" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
        If .Column = 3 And .Row > 1 And .Value = "Out" Then
            .Offset(, 2).Value = Format(Now(), "dd/mm hh:mm:ss")
        End If
    End If
    
    End With
    
End If
Application.EnableEvents = True
ws.Protect

End Sub
Before using the above code, highlight Columns B & C. Right click either of the highlighted columns / select FORMAT CELLS ...

Choose the PROTECTION TAB. Make certain LOCKED is UNCHECKED. (Because you highlighted the two Cols B&C, this 'unlocked setting' will only apply to these two columns).

Click OK, etc.

Now, go to the MENU BAR up top / REVIEW / PROTECT SHEET. Protect the sheet without providing a password - leave that blank.

The macro should now work as desired.

If you choose to utilize a password the macro will need to be edited to include that password in the code. The code as presently written applies to rows 1 to 100.
Reply With Quote