Excel lock cell after data entry

MS Excel 2003. I have come up with a date/time stamp but I need to lock the cells after data entry so that they are tamperproof without a password. Please feel free to refer me to an answer given earlier!
PitfourAsked:
Who is Participating?
 
Chris BottomleySoftware Quality Lead EngineerCommented:
PITFOUR

The specifics for your requirement will hopefully be answered by something like this for any new entries detecting the change and setting the protection post change:

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim cel As Range
   
    If Not Intersect(Target, Me.[a2:ba200]) Is Nothing Then
        Application.EnableEvents = False
        On Error Resume Next
        Me.Unprotect "pw"
        On Error GoTo 0
        For Each cel In Intersect(Target, Me.[a2:ba200]).Cells
            cel.Locked = True
        Next
        ActiveSheet.EnableSelection = xlUnlockedCells
        Me.Protect "pw"
        Application.EnableEvents = True
    End If
   
End Sub

Assuming you want to initialise the settings automatically so that the auto update above can take over then you could try something like this in a module:

Sub ini_lock()
    Dim cel As Range
   
        Application.EnableEvents = False
        On Error Resume Next
        ActiveSheet.Unprotect "pw"
        On Error GoTo 0
        Cells.Locked = False
        For Each cel In Intersect([a2:ba200], [a2:ba200])
            If cel.Value <> "" Or cel.HasFormula = True Then cel.Locked = True
        Next
        ActiveSheet.EnableSelection = xlUnlockedCells
        ActiveSheet.Protect "pw"
        Application.EnableEvents = True
   
End Sub

Regards
Chris
0
 
kacorretiredCommented:
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
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
kacorretiredCommented:
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?
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
PitfourAuthor Commented:
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.
0
 
kacorretiredCommented:
without password you can't solve this job
0
 
kacorretiredCommented:
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
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
kacorretiredCommented:
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
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
kacorretiredCommented:
Chris
You have right. BUT. I mean this macro is on an extra page on PITFOUR's PC and nobody else can see it.
0
 
kacorretiredCommented:
This macro is no part of the used workbook
0
 
Patrick MatthewsCommented:
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
0
 
PitfourAuthor Commented:
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!
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
 
PitfourAuthor Commented:
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....
0
 
PitfourAuthor Commented:
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.
0
 
Chris BottomleySoftware Quality Lead EngineerCommented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.