Auto Lock after first Data Entry and Time Stamp

[macpcboy]

Hi all,

I just registered but have been a long time reader of the forum. I would like some help regarding a spreadsheet that I am currently trying to program.

It goes something like this:

Row	Column C	Column D	Column E
2	Tom	Dave	Bob
3	Approved	Not approved	Amended
4	Time & Date	Time & Date	Time & Date

<tbody>
</tbody>

· C2, D2 and E2 are names of Employees. Tom can only change C3 and C4. He cannot change anything in D3, D4, E3 and E4. Similarly, Dave and Bob can only change their own respective column values.
· C3, D3 and E3 - each have Data Validation Lists with drop down options Appoved, Not Approved and Amended.
· C4, D4 and E4 have the following formulas:

=IF($C$3<>"",IF(C4="",NOW(),C4),"")
=IF($D$3<>"",IF(D4="",NOW(),D4),"")
=IF($E$3<>"",IF(E4="",NOW(),E4),"")

As soon as value was entered in Cell C3, C4 shows the time and date. Same goes for E3, E4 and D3, D4.

My question is, now that any value is selected in cell C3 (e.g. Approved), I would like it to be automatically locked and no one else can change it (even Tom, who originally approved it).

Any suggestions would be welcome,

Thanks,

 

[Snakehips]

macpcboy,

Welcome to MrExcel.

To achieve the above you will need to use vba similar to below.

Assumes C4:E4 are protected C3:E3 not.

You will not need the current circular formula that you have for the timestamp.

Right click sheet tab >> View Code and paste in the below.

Private Sub Worksheet_Change(ByVal Target As Range)If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C3:E3")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Application.EnableEvents = False
Target.Offset(1, 0).Value = Now
Target.Locked = True
ActiveSheet.Protect
Application.EnableEvents = True


End Sub

Hope than helps.

 

[macpcboy]

Hi Snakehips,

Thank you for your reply. I tried to put in the VBA code but unfortunately I am still able to change the values in C3:E3. I would like to see that the value once entered cannot be edited at all.

Is that possible?

Thanks,

 

[Snakehips]

I notice that the code in my response has not rendered correctly but if you copy that to your sheet's code module and did not correct the missing line feed then it should have shown first line in red and not even compiled.
Code should read.......

Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("C3:E3")) Is Nothing Then Exit Sub
ActiveSheet.Unprotect
Application.EnableEvents = False
Target.Offset(1, 0).Value = Now
Target.Locked = True
ActiveSheet.Protect
Application.EnableEvents = True


End Sub

The code must be in the Sheet's code module.
C4:E4 must initially be set as locked
C3:E3 must initially be unlocked
The sheet must then be protected (I have assumed no password to be used)

Then the above should do as you are asking..
Data entered in an individual cell in range e.g. C3:will generate time stamp in row C4
C3:C4 will then both be locked and protected but not with password so user could currently unprotect if wanting to edit

 

[macpcboy]

Hi Snakehips,

Thanks a lot for your response again. I am pretty sure that the one that you sent should work now. May I present to you another way to do it. I would like to put it here in case someone has a problem. This works like a charm:

<code style="margin: 0px; padding: 0px; font-style: inherit; font-weight: inherit;">Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column < 3 Or Target.Column > 5 Then Exit Sub

'The following Line Freezes the Date And Time
If Target.Row = 3 Then Target.Offset(1, 0).Value = Target.Offset(1, 0).Value
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.Column < 3 Or Target.Column > 5 Then Exit Sub
If Target.Value <> "" Then Target.Offset(0, 1).Select
End Sub</code>

 

[Snakehips]

Just two comments..

Row 4 needs to be locked to ensure that you can't overweight the formula?
Or use the Selection Change event to always throw the user out of row 4?

Have you tried what happens if user selects more than one cell in C3:E3?