Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 10771
  • Last Modified:

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!
0
Pitfour
Asked:
Pitfour
  • 7
  • 7
  • 4
  • +1
1 Solution
 
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 BottomleyCommented:
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
 
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Chris BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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 BottomleyCommented:
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
 
Chris BottomleyCommented:
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
 
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 BottomleyCommented:
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 7
  • 4
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now