Insert Date & Time in a column upon data change for first time only

[HECGroups]

Hello Again,

I am looking for a macros VBA where a user insert or update a data the date and time should be insert in column I and save the workbook.

Note: If the column I already have the date and time inserted before then it should give message record already have date and time.

I am using office 2010.

Thank you.

 

[Joe4]

Which cell exactly should trigger this update?
Which cell exactly should the date/time stamp be placed in?

 

[Joe4]

Here is some code I came up with that operates under the following assumptions:
- Anytime a value in column I is added/changed, look at cell J1. If there is not a date/time stamp already in there, add in the current date and time and save the file. If J1 is already populated with a date/time stamp, return a message box.

This is Event Procedure VBA code, code that is automatically triggered by some event happening (in this case, the update of a cell in a particular column). For more information on Event Procedures, you can refer to this link: Events In Excel VBA

To use this code, simply right click on the sheet tab name at the bottom of the screen (the sheet you want to apply the code to), select "View Code", and paste this code in the resulting VBA window:

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if a new entry/change is made in column I
    If Target.Column = 9 Then
'   Check to see if there is already a timestamp in column J1
        If Range("J1").Value > 0 Then
'   ...if there is, return message box
            MsgBox "Date/Time stamp already existis in cell J1"
        Else
'   ...if there is not, populate it with current date and time and save
            Range("J1") = Now()
            ActiveWorkbook.Save
        End If
    End If

End Sub

 

[HECGroups]

I am really sorry. I haven't provide you my complete requirement. Below is the details.

I have to maintained log which is having columns from Column A till Column I. Now the requirement is:-

If any user enter information within this column then it should apply date and time stamp in column J.
If the date&time is already return in column J then that row data should not me edited. i.e. row number 100 column J having date & time any user should not edit any data for that row.

Rows will keep on adding but the column will remain the same.

I hope i have provide you the details which help you to help me.

Thanks in advance for your valuable time.

 

[Joe4]

That's a bit different. You are now talking about locking/unlocking cells based upon data entry. Take a look here: http://www.mrexcel.com/forum/excel-...er-entry-using-visual-basic-applications.html

 

[HECGroups]

Thanks for your help but what about the date & time stamp. I have used your code but it is working if i enter any value in column I not on other columns.

 

[Joe4]

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if a new entry/change is made in columns A-I
    If Target.Count = 1 And Target.Column <= 9 Then
'   Check to see if there is already a timestamp in column J
        If Range("J" & Target.Row).Value > 0 Then
'   ...if there is, return message box
            MsgBox "Date/Time stamp already exists in column J"
        Else
'   ...if there is not, populate it with current date and time and save
            Range("J" & Target.Row) = Now()
            ActiveWorkbook.Save
        End If
    End If

End Sub

 

[HECGroups]

Thanks a lot for your valuable time spending for me to help. I will feedback tomorrow.

 

[HECGroups]

Hai Joe4. Now the scenario is totally changed can i continue on the same thread or open a new one. Retirement is the same but added some new requirements.

Waiting for your response.

 

[jmwbowen]

This code won't solve what seems to be HECGroups' dilemma. With this code, the user will still be able to edit data in a row where a timestamp already exists. The MSGBOX will show up, but that alone does not prevent a user from editing data that has already once been edited. The locking and unlocking of cells based on the timestamp column entry could work, but what about using Application.Undo in combination with the code Joe4 submitted.

Private Sub Worksheet_Change(ByVal Target As Range)

'   Check to see if a new entry/change is made in columns A-I
    If Target.Count = 1 And Target.Column <= 9 Then
'   Check to see if there is already a timestamp in column J
        If Range("J" & Target.Row).Value > 0 Then
'   ...if there is, return message box
            MsgBox "Date/Time stamp already exists in column J"
        Else
'   ...if there is not, populate it with current date and time and save
            Range("J" & Target.Row) = Now()
            ActiveWorkbook.Save
        End If
    End If

End Sub