timestamp once all cells are filled in

Question

Here is the situation

Columns A through G are designed as drop down lists (data validation): Name, Number, ID, Phone, etc. Upon arrival to the office, each employee must fill their information into each cell of the row, in columns A to G.

What I want from a VBA code:

Only when each cell is filled in A:G, the date and time is stamped in the corresponding cell, in column H. It is permanent. It doesn't change ever. And once the date is stamped, the cells Columns A:G are locked as well.

My coding so far:

Private Sub Worksheet_Change(ByVal Target As Range)
     If Target.Column = 1 Then
          Target.Offset(0,1) = Now
     End If
End Sub

This timestamp only works when cells in column A are changed :(

Should I be using a "case select" statement?

Siddharth Rout · Accepted Answer · 2013-11-19T21:11:08.007

1

Is this what you are trying? (TRIED AND TESTED)

Option Explicit

'~~> Change this to the relevant password
Const MYPASSWORD As String = "BlahBlah"

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge > 1 Then Exit Sub

    On Error GoTo Whoa

    Application.EnableEvents = False

    Dim rng As Range
    Dim nRow As Long

    nRow = Target.Row

    Set rng = Range("A" & nRow & ":G" & nRow)

    '~~> Check if all cell from A-G are filled and
    '~~> There is no time stamp already there
    If Application.WorksheetFunction.CountA(rng) = 7 _
    And Len(Trim(Range("H" & nRow).Value)) = 0 Then
       ActiveSheet.Unprotect MYPASSWORD
       Range("H" & nRow).Value = Now
       Range("A" & nRow & ":H" & nRow).Locked = True
       ActiveSheet.Protect MYPASSWORD
    End If
Letscontinue:
    Application.EnableEvents = True
    Exit Sub
Whoa:
    MsgBox Err.Description
    Resume Letscontinue
End Sub

edited Nov 19 '13 at 21:11

answered Nov 19 '13 at 20:12

Siddharth Rout

147,039
17
206
250

You might also want to see [THIS LINK](http://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) for explanation on how to use `Worksheet_Change` – Siddharth Rout Nov 19 '13 at 20:23
Problem: excel crashes when i run the code. In addition, the time and date change when I change the value of any cell of the specific row. For example, if I fill in all cells, the date and time is stamped, great. But if I decide to change the value of one of the cells, the most recent date and time is stamped again. How would I just make the timestamp permanent on the first entry? – user2988859 Nov 19 '13 at 20:51
Which Excel version are you using? May I see your sample file? – Siddharth Rout Nov 19 '13 at 20:52
Sure, excel 2007, how do I upload a file? – user2988859 Nov 19 '13 at 20:59
you can upload it to wikisend.com and share the link here – Siddharth Rout Nov 19 '13 at 21:00
company intranet firewalls, I can't :( – user2988859 Nov 19 '13 at 21:01
any other file sharing website? – Siddharth Rout Nov 19 '13 at 21:02
but it's literally just data validatio liast in A to G – user2988859 Nov 19 '13 at 21:02
Ok let's try and address it here... You said `But if I decide to change the value of one of the cells, the most recent date and time is stamped again.` But how are you changing it? that row gets locked? – Siddharth Rout Nov 19 '13 at 21:03
The row doesn't get locked. After selecting a certain valule in the drop-down list for any of the columns A:G, I can still change that value. So to be clear, the timestamp works when all cells have data. IF I decide to change one of the fields, the timestamp reloads to the current time. – user2988859 Nov 19 '13 at 21:08
Ok I will amend the code so that the time stamp doesn't change. BTW, I just tried it and it is not crashing here. Can you explain the exact step when the crash happens? – Siddharth Rout Nov 19 '13 at 21:09
`The row doesn't get locked. After selecting a certain valule in the drop-down list for any of the columns A:G, I can still change that value.` How is that possible :) We are locking the cells from `A:H` using the code `Range("A" & nRow & ":H" & nRow).Locked = True` as per your request in your question... – Siddharth Rout Nov 19 '13 at 21:12
Woah, just realize that. sorry for the confusion. The entire sheet gets locked. That's cool but not necessary for my situation. just locking the H column would be fine. maybe thats the key to keeping it permanent? – user2988859 Nov 19 '13 at 21:15
just tried it works great! thanks for your time! btw, I just removed the password protection portion. – user2988859 Nov 19 '13 at 21:20

timestamp once all cells are filled in

1 Answers1