CODEWITHSUNDEEP
exceltimestampvba
user2988859
user2988859

Reputation: 13

timestamp once all cells are filled in

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?

Upvotes: 1

Views: 501

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149297

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

Upvotes: 1

Related Questions