Excel lock cell after data entry

You have to replace the function with the actual value. Select cell then
1. Edit / Copy
2. Edit / Special Insert  Insert Group / Value

wbr Janos

Assuming you only want to lock the specific cell
Select all cells then:
format | format cells | Protection
deselect locked
Select the cell with the time stamp
format | format cells | Protection
select locked

Select tools | protection | protect sheet.

Disable edit option and set any preferences to your general wishes and do not put anything in the password.

Hopefully this gives you what you want i.e. as I understand it prevent casual edits to the timestamp cell but that's all.

regards
Chris

My offered procedure above prevent the data change when a function is used. In this case after open the Worksheet all functions are recalculated. That's why I offered this solution.  Locking cells is possible see chris bottomley's comment. Would you like to allow for users insert other data too?

I notice my response may be in error, I read your request as cannot change but do not want password protection ... I think I misread ... but in which case simply assign a password at the end i.e. instead of:

Disable edit option and set any preferences to your general wishes and do not put anything in the password

use:

Disable edit option and set any preferences to your general wishes and set the password to your chosen value.

Chris

Sorry I have not been clear here - my fault entirely.

The date stamp comes from a drop down list based on cells from another sheet as the date/time stamp is combined with the initials of the reviewing person using the now() function. So I hope to go to the cell, select an entry from the drop down validation and then protect the cell concerned.
It is hoped that all cells with previously entered data will stay protected as a result of the previous transactions.

without password you can't solve this job

after entering the date stamp and initials you have to mark the active cell for locking. Of course first open the sheet then after entering lock

Kacor

Not sure what you're trying to say here, if the originator wants to add a password then they can certainly do so as stated.

I would agree that if the sheet is already protected that unless they know the password then they are stuck, (though it is said that excel security is breakable).  In this case however my read of the requirement is that PITFOUR wants to append their own password and therefore they CAN solve the job.

Chris

Chris,

this sheet is protected at PITFOUR therefore others can insert data on the not locked area without password. If Pitfour don't want to use password I simply can't imagine the solution

If I understand the latest from PITFOUR .. and my apologies but my system seems to be slow to update .. .or perhaps i'm not refreshing as often as I think!

When appending a new datum to be protected, (as stated earlier you would have unlocked all but the cells of interest) you need to set the lock for that cell therefore need to unprotect, set cell lock then reprotect the sheet.

Whilst it could be automated by macro .. .that would make the password visible in the macro so possibly not ideal!  Leaving you to unprotect, lock and reprotect

Chris

Chris
You have right. BUT. I mean this macro is on an extra page on PITFOUR's PC and nobody else can see it.

This macro is no part of the used workbook

Hello Pitfour,

This code shows how to mark the corresponding cells in B with a date/time stamp any time
cells Column A change due to entry/edit/paste/delete.  It assumes that the cells in Col B
are locked, and that you have a password set on the worksheet, "pw".

The code goes in the code module for the worksheet being "watched".

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cel As Range
    Dim ts As Date
   
    If Not Intersect(Target, Me.[a:a]) Is Nothing Then
        Application.EnableEvents = False
        Me.Unprotect "pw"
        ts = Now
        For Each cel In Intersect(Target, Me.[a:a]).Cells
          cel.Offset(0, 1) = ts
        Next
        Me.Protect "pw"
        Application.EnableEvents = True
    End If
   
End Sub

Of course, with the password showing in plain English on the code module, you will need to
lock the VBA Project from viewing by password.  Please note that both the worksheet and
VBA Project passwords can be shredded in seconds using widely available and
inexpensive hacking tools.

Regards,

Patrick

Patrick's solution is 95% there but it would be great if I could do without the ghost column i.e. date/time/initial stamp using the validation then lock this cell and all other completed cells with a password (only needed to prevent casual interference). There are deliberately no macros in the sheet and users have difficulty spelling concatenation, let alone looking at the code!

Which cells change, prompting the timestamp and where do you want the timestamp?  i.e. Patrick assumes that you put the timestamp in column b when the matching cell in column A is updated.  Your request to delete the ghost column means this is not quite the functionality you require but it is essentially correct hence the 95% correct comment.

As suggested earlier the principle is straightforward it simply needs tweaking to your circumstances.  The fundamental questions being what data range are you monitoring for and how is the timestamp to be recorded noting currently you presumably have some unprotected cells which when edited you want a timestamp to be recorded somewhere and the new datum and/or the timestamp to be locked.

Chris

200 names in column A. 52 columns from B onwards representing weeks of financial year. The intersections in columns B and onward contain the validation - a concatenation of the now() function and the initials from another sheet. I want to leave the empty cells unlocked and ready for data entry but the ones containing the datestamp to be locked with a password:

http://www.mindspring.com/~tflynn/excelvba2.html#Protect

This sub looks at every cell on the worksheet and
' if the cell DOES NOT have a formula, a date or text
' and the cell IS numeric, it unlocks the cell and
' makes the font blue.  For everything else, it locks
' the cell and makes the font black.  It then protects
' the worksheet.
' This has the effect of allowing someone to edit the
' numbers but they cannot change the text, dates or
' formulas.

Sub Set_Protection()
On Error GoTo errorHandler
Dim myDoc As Worksheet
Dim cel As Range
Set myDoc = ActiveSheet
myDoc.UnProtect
For Each cel In myDoc.UsedRange
    If Not cel.HasFormula And _
    Not TypeName(cel.Value) = "Date" And _
    Application.IsNumber(cel) Then
        cel.Locked = False
        cel.Font.ColorIndex = 5
    Else
        cel.Locked = True
        cel.Font.ColorIndex = xlColorIndexAutomatic
    End If
Next
myDoc.Protect
Exit Sub
errorHandler:
MsgBox Error
End Sub

This is nearly what I want but not quite....

I have tried this on a copy and it works just fine.I am not sure that I explained the situation very well in the first instance. Thanks Chris.

Glad you got there, yes I was struggling in the first instance as I couldnt get a grip on what went before.  Not to worry though at least you got your answer.

Chris