VBA Code for date entry to column when range updated

Question

I have a spreadsheet that I need column B to automatically populate with the date and time when columns C-K are updated. There are columns further than K which I don't want to trigger the update code.

Firstly I used the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim i As Integer
i = Target.Row
Dim t As String
t = VBA.Now
Cells(i, 2) = t
End Sub

This didn't work for my situation as this updates for any changes in the row. The advantage of this was that users could not type over these cells preventing the VBA from entering the date updated.

To enable trigger for just the selected rows, I went to the following code:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim KeyCells As Range
Set KeyCells = Range("C6:K43")      
If Not Application.Intersect(KeyCells, Range(Target.Address)) _
    Is Nothing Then
Dim i As Integer
    i = Target.Row

    Dim t As String
    t = VBA.Now

    Cells(i, 2) = t
End If
End Sub

This is, however, now able to be typed over by users and no longer works.

I have tried locking the cells but then the VBA can't work. I also tried using an offset formula but this doesn't work due to the range of cells being more than 1 column.

Any suggestions before I tear my hair out???

When updating the worksheet from within a Change event handler, you should disable events while performing the update, or that will re-trigger your code. — Tim Williams, May 09 '17 at 06:11
http://stackoverflow.com/questions/3037400/how-to-lock-the-data-in-a-cell-in-excel-using-vba I think this might help you. — Sivaprasath Vadivel, May 09 '17 at 06:20

Shai Rado · Answer 1 · 2017-05-09T11:06:59.370

0

Since Target is already a Range, there's no need to use Range(Target.Address), just Target will do.

Also, following on @Tim Williams feedback, you need to turn Application.EnableEvents to False, not te re-enter the code once you add the current time to cell in column "B".

Try the code below:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Set KeyCells = Range("C6:K43")

Application.EnableEvents = False
If Not Application.Intersect(KeyCells, Target) Is Nothing Then
    Range("B" & Target.Row) = Now
End If
Application.EnableEvents = True

End Sub

edited May 09 '17 at 11:06

answered May 09 '17 at 06:36

Shai Rado

33,032
6
29
51

1

Good answer - just checking if you really mean `DisplayAlerts`? Shouldn't that be `EnableEvents`? – Ambie May 09 '17 at 10:59
Thank you for the above code. This does work for the formula that I required, however, with this code, I can still type in column B and this removes the VBA date entry in this column. Is there any way around this? – Choppet May 09 '17 at 22:34

VBA Code for date entry to column when range updated

1 Answers1