VB CODE

[Raghavendravijayrao]

I have an attendance register in excel. I want to lock the cell immediately after the time is registered for that date. i.e i want to lock the cell immediately after the attendance is registered in that cell. after registering the time if i click on any other cell it takes blank as a character and locks that cell too. how can i avoid this.

 

[Ramesh]

We can take the help of locked property of the cell in a worksheet..you have to password protect the worksheet and lock only those cell which you do not want the user to modify..

try this code and see if this helps....

    If Target.Value <> "" Then
        ActiveSheet.Unprotect "password"
        Target.Locked = True
        ActiveSheet.Protect "password"
    End If

 

[Raghavendravijayrao]

Thanks Ramesh,
I have already written the following code
Private Sub Worksheet_Change(ByVal Target As Range)
Dim MyRange As Range

Set MyRange = Intersect(Range("A1:AF2000"), Target)
If Not MyRange Is Nothing Then
Sheets("Attendance").Unprotect Password:="123"
MyRange.Locked = True
Sheets("Attendance").Protect Password:="123"
End If
I have locked those cells which i dont want user to modify.
I have unlocked those cells where he has to register his attendence.
but if i click on any cell after i register my attendence that cell also gets locked.


▬▬▬▬▬▬▬▬▬ Mod edit : thread moved to appropriate forum !

​

 

[Ramesh]

I don't see the if condition checking whether the current cell is blank..i.e. we will lock the cell only if the user entered a value or entered a valid value such as P or A or any other input.. try introducing if condition with If Target.value<>"" then lock the cells..